Written by Allen Wyatt (last updated August 18, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
Figure 1. The General options of the Excel Options dialog box.
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.
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!
Want Excel to automatically adjust the height of a worksheet row when it wraps text within the cell? It's easy to do, ...
Discover MoreWhen you have text wrap turned on in a cell, Excel expands the height of the row as you add more text to the cell. When ...
Discover MoreYou may have a need to increase the height of the rows in your worksheet to "spread out" the data when it is printed. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-09-27 17:24:03
Dan
Just want to post this here for anyone having a similar issue. I was about the post the below questions and details related to problems I was having when I randomly stumbled upon the answer. I feel extremely lucky to have found the answer, so I'm posting it here in the hopes that it may save others time and stress.
----------------------
I found this article looking for an answer to why my files have started to open with the maximum row height of 409.5. I am using the Office 365 Pro, 64bit version of Excel.
I tried rebooting, opening files in safe mode, and checking the default font. Nothing has worked thus far. I have also noticed that I can no longer insert rows in my files and I have tried to "delete" the blank rows below my work, but that has not worked either.
One last clue is that my pointer icons (to drag or move a cell) do not look quite right anymore. The best way I can describe it is that they look pixelated.
-----------------------
What I found when thinking through what I was typing above was that, at some point, I had unintentionally added conditional formatting to a group of cells that repeated every 6 or 8 columns to the end of the sheet. Once I removed the obviously unintentional conditional formatting, I noticed that I could insert rows again. I then rebooted and found that Excel had returned to operating normally under all situations.
Hope this helps. Cheers.
Dan
2021-09-21 09:02:52
feo
I've been waiting for years for them to finally fix this....sigh
2020-04-05 02:58:04
amnon zamir
BEST way it worked for me:
I added a column to the spreadsheet, placed any character in all its cells ( just copy and paste on all the cells in the column).
then: with the cells of the column still selected, I gave them all a size of 20 points.
the rows now adjusted to the minimum heigh of the character of 20 points.
Finally: paint all these cells in white text to make them invisible or if satisfied - just hide the column.
you can change to the type height easily at any time to adjust to any development in your work.
2020-02-17 11:24:33
Nash
Great tips, thank you!
2019-07-12 13:34:33
Deborah Brancheau
If you have macros enabled, couldn't you just use something like in the VBA editor?
Sub sbChangeRowHeight()
Rows().RowHeight = 25
End Sub
2019-03-06 08:07:17
SteveJez
@R
If you have a look at the following articles you find out how to save a new default workbook which can be formatted as you want it.
https://excelribbon.tips.net/T010561_Saving_Custom_Formats.html
https://exceljet.net/blog/how-to-set-a-default-template-in-excel
Steve
2019-02-28 16:49:11
R
This shows how to change the font which results in a different row height. Is there a method to det a default row height regardless of the font?
2018-09-24 15:37:27
James Wolz
I would suggest setting the default font on one column to an appropriate font to create the minimum 25. Then hide the column. That would lock the row height, but not require adjusting the balance of the cells to a more appropriate font. Also, select the entire worksheet and turn on wrap.
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 © 2023 Sharon Parq Associates, Inc.
Comments