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

microsoft excelmicrosoft-excel-2010

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 not in A2?

(To reproduce: set width of column A to 17.14 (125 pixels), text wrap on, and cell alignment top.)

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

Auto Fit fails for the first row, succeeds for the second

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

Best Answer

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!