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

Question

For every generation of Excel I can remember (including 2010, which I'm using now), Excel's "Auto Size Row" features sometimes fails to actually auto size a row when the cell contains wrapped text. When it works properly, all the text is revealed and there is no additional space below the last line of text. When it fails, it adds extra space below the text. To make matters worse, what you see is not always what you get, i.e., text that appeared okay on screen gets cut off when it's printed. You also get different sizing depending on whether you are zoomed in/out or at actual size.

Simple test case:

Why is there a one-line gap after the text in cell A1 but in A2?

(I double-checked that I applied Auto Fit Row Height to both rows. Zoom level is 100%.)

Is there any known remedy for this without resorting to manually adjusting the row heights (which is not practical for more than a handful of rows)?

Answer 1

To paraphrase Dunc:

I've solved a similar issue by:

1. Selecting the entire spreadsheet.

2. Double clicking the line in the margin in between the two rows.

Specifically to address your issue of that extra line, I'd assume that there is a character there. It may not be a visible character, but there is definitely something there.

Answer 2

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