How to make Excel's "Auto Fit Row Height" feature actually auto fit the row height?

A method I just found (extending the previous post a little):

• Select the whole sheet.
• Resize a column a little wider (because the whole sheet is selected, all the columns will get wider)
• Double-click a row separator - the row height will be auto-fitted
• Double-click a column separator - the column widths will be auto-fitted

Voila!

Excel's WYSIWYG isn't the best. In your picture, 'cat.' just barely sneaks into the 5th line. If you reduce the zoom percentage to anything less than 100% (99% for example.) then 'cat.' is now wrapped down to the 6th line. I think Excel is trying to auto-fit in a way that will ensure everything is almost always visible no matter your zoom level.

That isn't the only problem you will have with AutoFit. In addition, the way a word-wrapped cell is printed won't always match what you see on screen. Take your example and change the font to Courier while leaving size at 11.

As you can see, cell A1 appears to be given 1.5 extra lines. Now look at print preview, 'cat.' is completely hidden.

In my experience, Excel has this problem with certain fonts and font sizes more than others. Courier is worse than Courier New, size 11 is generally worse than 10 or 12. (Why they picked size 11 as the new default, I have no idea.)

In some cases, the problem of extra space in some rows after invoking "AutoFit Row Height" may be that a hidden column has wrapped text in those rows.

There is a kb article on this which "suggests" (it's unacceptably sparse, in classic Microsoft fashion) that if you ever set a row height, automatic height autofitting is permanently gone for that row of that worksheet. Rows whose height you haven't touched will autofit fine though.

While you can reset column widths with the "standard width" setting in 2003 (madman-designed 2007: change to "default width"), but they forgot (again) (and again) to put that in for rows. So the permanency seems unavoidable. That means, if you have a default height of 15, and you create a sheet and set all the heights to 12, you just forfeited automatic autofitting.