Changing Width and Height to Inches

by Allen Wyatt
(last updated June 21, 2018)

5

Dean wonders how to change the column width and the row height from pixels to inches. He would like to create labels in Excel and they are specified in inches.

Before getting into possible ways to address this problem, it should be pointed out that Excel uses an odd way of calculating row height and column width. In fact, row height is calculated entirely differently than is column width.

Row height is measured not in pixels, but in points. A pixel is a relative unit of measurement related primarily to display devices such as monitors and printers. If something is 50 pixels wide, that size, by itself, means nothing without knowing how many pixels the device is capable of displaying vertically.

Points, however, are well defined—there are approximately 72 points per inch, so a row height of 72 would be an inch tall. Simple, right?

Now consider column width, which is not specified either by pixels or points. Instead, it is specified in character widths of whatever typeface you are using. For instance, if your Standard style is set to Courier 10, then a column width of 12 means you can fit exactly 12 characters in a given column. Change the typeface used, and all of a sudden your column width will change, as well so that it can still display 12 characters.

Yes, that approach is really goofy. It also makes it very hard to do something like design labels in Excel. You might get something designed that works on your system (after a good deal of trial and error), but if you then forward the workbook off to a friend, chances are good that it won't work on their system. Why? Because how a typeface is rendered on their system may differ entirely than it does on yours.

The best way to deal with labels, quite honestly, is to do them in Word. You can keep your label data in an Excel worksheet, but use mail merge in Word to create the actual labels. The tools provided for labels are much easier and much more precise than what you can do in Excel.

If you absolutely must do it in Excel then you should switch to Page Layout view. (Display the View tab of the ribbon and click the Page Layout tool in the Workbook Views group.) This displays both horizontal and vertical rulers on the screen that you can use as a guide to help set your row height and column widths.

There is another interesting side-effect of working in Page Layout view: If you display the Home tab of the ribbon and use the commands for setting row height and column width (from the Format tool), you'll note that both are specified in inches. They only go back to the absurd character-count column width if you switch back to Normal view.

If you prefer to set the height and width through the use of a macro, you might find the WidthHeightInches macro instructive.

Sub WidthHeightInches()
    Dim Temp As String
    Dim RInch As Single
    Dim CInch As Single
    Dim WPChar As Double
    Dim c As Range
    Dim r As Range

    Temp = InputBox("Row height in inches?")
    RInch = Val(Temp)
    If RInch > 0 And RInch <= 2 Then
        Temp = InputBox("Column width in inches?")
        CInch = Val(Temp)
        If CInch > 0 And CInch <= 3 Then
            For Each c In ActiveWindow.RangeSelection.Columns
                WPChar = c.Width / c.ColumnWidth
                c.ColumnWidth = ((CInch * 72) / WPChar)
            Next c
            For Each r In ActiveWindow.RangeSelection.Rows
                r.RowHeight = (RInch * 72)
            Next r
        End If
    End If
End Sub

Remember that column widths are specified by the number of digits that can be fit horizontally in a cell. The heart of this macro figures out the width of a single digit, in points. It does this by dividing the width of the column in points (the .Width property) by the width of the column in characters (the .ColumnWidth property). This value is then used to calculate how many characters wide the column should be in order to be the width desired.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8768) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using Overtype Mode

When you type information into a document, what you type normally is inserted just the left of the insertion point. Word ...

Discover More

Cropping Graphics

Excel makes it easy to place a graphic in a worksheet. Once there, you may want to chop off a side (or two) of the ...

Discover More

Turning Off a Dictionary for a Style

There may be some paragraphs in a document that you don't want Word to spell- or grammar-check. You can 'turn off' the ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Formatting Subtotal Rows

Excel automatically formats subtotals for you. But what if you want to change the default to something more suitable for ...

Discover More

Hiding and Unhiding Rows

When building a worksheet, you may need to hide some of the rows or unhide other, previously hidden, rows. It's easy to ...

Discover More

Detecting Hidden Rows

Excel allows you to easily hide rows in a worksheet, so their contents are not visible. Figuring out how to detect where ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 7?

2018-08-02 12:39:49

J. Woolley

Your formula c.ColumnWidth=((CInch*72)/WPChar), which resolves to c.ColumnWidth=((CInch*72*c.ColumnWidth)/c.Width) usually requires iteration. Experience indicates that 3 loops are necessary and sufficient. For example, see http://powerspreadsheets.com/excel-vba-column-width/#6-Set-Column-Width-in-Points


2015-07-20 14:38:48

Dean Bush

Thank you for the comments. I will try each of them.


2015-07-18 12:21:34

StuCram

If you want to use inches to specify Page Setup margins via a macro, the Excel function InchesToPoints is handy there too.

example:

With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1.25)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.667)
End With


2015-07-18 11:36:25

StuCram

Excel's function <B>InchesToPoints</B> is useful too so that you need not know there are 72 points per inch. I've used it to specify inches for the size and position of a text box (or other shapes too).

example…

Sub setTextboxSpecs(TBname As String, _
tbTop As Double, _
tbLeft As Double, _
tbHeight As Double, _
tbWidth As Double)

' purpose: allows setting the top, left position and the dimensions of a named toolbox uning inches.

With ActiveSheet.Shapes(TBname)
.Top = Application.InchesToPoints(tbTop)
.Left = Application.InchesToPoints(tbLeft)
.Height = Application.InchesToPoints(tbHeight)
.Width = Application.InchesToPoints(tbWidth)
End With

End Sub

'---------------------------------------------

Sub Test_SetToolboxSpecs()
' testing the above subprogram with 2 shapes...
Call setTextboxSpecs("TextBox 1", 1, 2, 4, 4)
Call setTextboxSpecs("Decagon 2", 3, 1, 1.5, 2.5)
End Sub


2015-07-18 10:56:52

David de Jongh

I brought up the Page Layout solution, and if you only have Excel, that is the way to go. Personally, I use Access for my mailing list databases, and Word mail merge to print the labels.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.