Changing Default Row Height

by Allen Wyatt
(last updated March 4, 2016)

14

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 and Excel 2013 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, 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

Selecting Individual Cells in a Table

Many times before applying formatting or doing another operation, you need to select an individual cell in a table. Here's ...

Discover More

Allowing Only Comments In a Document

Develop a document that is to be reviewed by a group of people, and you may want to protect it in some way. One way you can ...

Discover More

Returning the MODE of a Range

The MODE function is used to determine the most frequently recurring value in a range. This tip explains how to use the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Adjusting Row Height when Wrapping Text

If you have some cells merged in a worksheet, and you wrap text within that merged cell, Excel won't automatically resize the ...

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 rows ...

Discover More

Changing Width and Height to Inches

Want to set the width and height of a row and column by specifying a number of inches? It's not quite as straightforward in ...

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. 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 9 + 4?

2016-06-10 10:49:06

Stan Straatman

Hi Allen,

No way to have a defaul Row height in Excel. Solved it to one colomn with bigger font. Excel is than adjusting according that font. Make that colomnn hidden.
Change than back to Autofit Row Height.

With this you have a changed standard row hight and can make use of autofit.

Regards,

Stan


2016-04-19 21:48:33

Madhur

Hey Bill, thanks for this superb trick!! So simple yet so effective!! Thanks once again!

Cheers!


2016-04-13 09:40:52

Bill M

My spreadsheet columns are from A to K, and the font is 9 for everything. In Column L (which is outside my work area and contains no text), I selected the entire column and changed the font to 12. This allowed all of the rows in my work area to expand height-wise.


2016-03-04 14:30:42

Morris Manning

If working with merged cells, this works for me. Place in appropriate sheet, set intersect ranges and set NewRhHt to desired factor.


Private Sub Worksheet_Change(ByVal Target As Range)
''Excel does not have a feature which autofits row heights on merged cells.
''This macro does perform an autofit of row heights on designated merged cells.
''Adapted from Jim Reck by Greg Wilson, with modifications suggested in a blog _
by Debra Dalgleish with comments by "Smallman", (Ozfrid MVP) and others.

Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

'Substitute one or more cell address below representing merged ranges where row height will be automatically _
adjusted to account for wrapped text. Reducing text to one line will automatically reduce row height accordingly. _
Since this code changes only one merged range at a time, it is much faster than using an array approach which _
causes processing all target ranges for each change in one cell.
'Cell addresses representing merged ranges must be unlocked in a protected sheet or workbook.

If Not Intersect(Target, Range("B23, B25, B27, B29, B31, B32, B33, B34, B35, B36, B37, B38, B39, B40, B41, B42")) Is Nothing Then
With Target
Sheet1.Unprotect
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
If c.Locked = True Then
c.Locked = False
End If
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt * 1.3 'This increases row height by a factor of 30%. Adjust as desired
cWdth = 0: MrgeWdth = 0
If c.Locked = True Then
c.Locked = False
End If
Application.ScreenUpdating = True
End If
End With
End If
End Sub


2016-03-04 07:39:52

Jan

I did the changes as suggested here. However, my "Options" box did not have all of the options your screen shot had. Is it due to the version or are you using an "advanced" option?


2015-12-23 10:03:18

Philip

One way to achieve this is to insert a column and in each cell down add any character; format the font for this column as say 22. This turns up the row height to 30 or so and still allows a auto row height. Then hide the column. QED


2013-07-24 10:47:39

Alice

C Jenkins quick workaround fixed it for me! Thank you! This has been bugging me for awhile. Brilliant fix!


2013-07-16 12:01:28

LCable

That worked! Thanks!


2013-07-16 11:55:26

Willy Vanhaelen

Do the following to be sure you copy the macro in the right place:
- Activate the sheet you want the macro to work in.
- Click the 'Developer' tab in the ribbon.
- Click 'View Code' in 'Controls'.
- That's the place were you can copy the macro.
- Be sure NOT to change the macro's name.

The macro will only be triggered when the cell's content is changed. Simply changing the font size for example will not trigger it unless you afterwards press F2 and Enter.

Willy


2013-07-15 14:28:01

LCable

I tried Willy's macro and couldn't get it to work properly when I thought it would adjust the line height to higher than 25. I put his macro into a new macro saved in the worksheet I was using. Wasn't sure about his direction to put it in the VBA project.


2013-07-09 17:03:45

Aldo

Don ... if your organization creates Certificates for each person required, then you can use those to maintain security and still be able to create and use custom macros.

Have your IT people look into it. The certificate writer is included in MS Office.


2013-07-08 14:11:14

Don

Willy...Thanks for the macro. I've got a slightly different use for it, resetting row height when there are merged cells.

There is one problem with macros (and I use a lot of them): Sometimes organizations place security restrictions on and lock that on people's PCs.


2013-07-08 01:30:07

C Jenkins

Another solution is select one column, change the font to give you the desired row height.

Now hide the column from view.

Setup the selected columns with text wrap, ensure the text in these columns is set vertically in the centre.


2013-07-06 11:06:52

Willy Vanhaelen

Here is a solution to do this autmatically.

Set the row height explicitly to 25 for the entire worksheet or the range you want as explained in the second paragraph above.

Place this little macro in the corresponding sheet in the VBA project (not in a module!). When entering data it will adjust the row height automatically to larger when necessary or keep it / restore it to 25 otherwise:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Rows.AutoFit
If Target.Rows.RowHeight < 25 Then Target.Rows.RowHeight = 25
End Sub

Willy Vanhaelen


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.