Changing Default Row Height

Written by Allen Wyatt (last updated September 23, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


2

Loretta wants to set a default row height with word wrap, but she also wants automatic row height to play into it. She doesn't want some "skinny" rows to appear. The default row height in her worksheet seems to be 12.75 but she'd like that to be, perhaps, 25 and then auto-adjust for any rows that need to have more height. She wonders how to make the default work this way.

There are two ways you can specify row height in a worksheet: explicitly or implicitly. You specify an explicit row height by using the tools on the ribbon to specify the exact height you want used for a particular row or rows. When you do so, you are "locking" the row height so that it is always what you specified.

Implicit height is calculated by Excel based on the size of the largest font used in the row. The default font for Excel is 10-point Calibri, so the row height is calculated to be 12.75. There seems to be no rhyme or reason on how this calculation occurs. For instance, if you change from 10-point Calibri to 20-point Calibri, you would expect the row height to double—but it doesn't. Instead, the row height becomes 26.25. If you change the font from Calibri to Arial, but leave it at 20 points, the row height automatically changes to 25.5.

Loretta wants the row height to be 25 and adjust larger, if necessary, and she wants it to be done automatically. There are no settings to do this. If you change the row height for a workbook to 25, explicitly, it won't adjust larger automatically. That means you need to leave the row height set to implicitly adjust (done by choosing AutoFit as the row height) and, somehow, get it to go no lower than 25.

The only way we've been able to determine to do this as Loretta envisions is to change the font and/or font size for the worksheet. As already noted, you could specify a default 20-point Arial font, and your row height would be 25.5—pretty close to Loretta's desire. This is a rather large font, but you could change the size of the font in individual columns (or cells) within a row and not have it affect the row height. This is true as long as at least one cell in the row is formatted to use a 20-point Arial font.

To change the default font used by Excel, follow these steps:

  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or a later version display the File tab of the ribbon and then click Options.)
  2. General should be clicked, by default, at the left of the dialog box—unless you are using Excel 2007, in which case Popular should be clicked. (If it isn't for some reason, click it.) (See Figure 1.)
  3. Figure 1. The General options of the Excel Options dialog box.

  4. Use the Use This as the Default Font drop-down list to choose which font you want used.
  5. Use the Font Size drop-down list to choose the size of that font you want used.
  6. Click OK.

Another approach is to adjust a column's font after you create a new workbook. For instance, let's say you open a new workbook, and you know that the data you enter will occupy rows in columns A through M. You could select column N (one column past where you will be working) and change the font for that single column to 20-point Arial. Excel automatically adjusts the row height in all the rows to 20.5 based on the new implicit row-height calculation for column N. There doesn't have to be any data in column N; you just need to adjust the font and/or font size for the column.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5663) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

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

Transferring Fonts

Do you want to transfer fonts from one computer system to another? It is relatively easy to do, but there is one ...

Discover More

Setting Stable Column Widths in a PivotTable

When you update a PivotTable, Excel can take liberties with any formatting you previously applied to the PivotTable. ...

Discover More

Selecting a Suggestion with the Keyboard

Excel tries to anticipate what you want to type into a cell, particularly when it comes to entering formulas. Here are ...

Discover More

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!

More ExcelTips (ribbon)

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

Automatic Row Height for Wrapped Text

When you format a cell so that the information within it can wrap to multiple lines, you may be surprised if Excel ...

Discover More

Adjusting Row Height for a Number of Worksheets

Adjusting the height of a row or range of rows is relatively easy in Excel. How do you adjust the height of those same ...

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}] (all 7 characters, in the sequence shown) 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 five more than 3?

2023-09-23 16:26:28

J. Woolley

This macro will set all used (non-null) cells of the active worksheet to wrap text with AutoFit and minimum 25 point row height:

Sub FixRowHeight()
    Const MinH = 25 'adjust as required
    Dim row As Range
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    For Each row In ActiveSheet.UsedRange.Rows
        With row
            .WrapText = True
            .AutoFit
            If .RowHeight < MinH Then .RowHeight = MinH
        End With
    Next row
End Sub

Using the row header, if you click the border of a row with 25 point height the number displayed is rounded to a whole number of pixels. Windows assumes 96/72 pixels per point, so 25 points corresponds to 33.33 pixels. When you click the row header border, the number displayed is 24.75 (33 pixels).


2023-09-23 10:45:48

J. Woolley

There are a couple of typos in this Tip:
1. In step 3: "Use the Use This as..." should be "Use this as..."
2. In the last paragraph: "Excel automatically adjusts the row height in all the rows to 20.5..." should have 25.5 instead of 20.5.


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.