Written by Allen Wyatt (last updated February 27, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8768) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
You may have a need to increase the height of the rows in your worksheet to "spread out" the data when it is printed. ...
Discover MoreChanging the default row height used for a worksheet is relatively easy, as long as you don't mind the row height never ...
Discover MoreNeed to hide a large number of rows? It's easy to do if you combine a few keyboard shortcuts. Here are several techniques ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-03-01 10:11:32
J. Woolley
You might be interested in the freely available DisplayScreenMetrics macro in My Excel Toolbox. It computes the Zoom factor necessary for displaying Excel's Page Layout in actual size.
See https://sites.google.com/view/MyExcelToolbox/
2021-02-28 15:50:32
Ronmio
Sometimes I simply insert an object as a placeholder to set a particular size in inches. That allows you to go into Picture Tools > Format > Size and specify the Height and Width in (hundredths of an) inch. To get those dimensions to hold fast, make sure you set the object's Properties so that you "... don't size with cells".
If you want to create labels, you can also plug a formula into each object that points to a cell that contains dynamic content such as names and addresses. The drawback to this formula-reference approach is that all the resulting content within a given object will be restricted to single format (all characters will have the same font format, etc.). You can get around this by nesting/grouping objects that have different formatting.
Also keep in mind that Microsoft Windows' WYSIWYG (what you see is what you get) does not live up to its name. (For example, perfect circles like pie charts will usually print as slightly squished ovals.) Ultimately, the actual printed size is dependent on your printer's driver. Consequently, you may need to do a little trial-and-error tweaking of height and/or width to get things perfect. Because of that idiosyncrasy I always insert a non-printing text box that specifies the printer that the worksheet is formatted for.
2021-02-27 11:14:23
J. Woolley
The "heart" of this Tip's macro should be iterated because c.ColumnWidth depends upon itself; three iterations should be sufficient. Here is the recommended change:
...
For Each c In ActiveWindow.RangeSelection.Columns
For iter = 1 To 3
WPChar = c.Width / c.ColumnWidth
c.ColumnWidth = ((CInch * 72) / WPChar)
Next iter
Next c
...
See https://powerspreadsheets.com/excel-vba-column-width/#7-Set-Column-Width-in-Inches for a similar method.
Also, see https://sites.google.com/view/MyExcelToolbox/
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments