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

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

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!

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

Answer 3

I just wrote a small macro for that purpose. I assigned it a shortcut key (Ctrl-Shift-J) and it works like a charm for me.

Sub ArrangeTable()
'set proper column width and row height for the current region
    With Selection.CurrentRegion
        .Columns.ColumnWidth = 200
        .Columns.EntireColumn.AutoFit
        .Rows.EntireRow.AutoFit
    End With
End Sub

To make this permanently available, you can easily make a automatically loading add-in:

• create a module in a fresh blank workbook and paste the code,
• assign it the shortcut key you want,
• then Save As, Add-in, to folder %appdata%\Microsoft\Excel\XLSTART
  This way it will be invisibly loaded every time you start Excel.

Answer 4

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.

Answer 5

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.

Answer 6

Sometimes there is the nuclear method to fix misbehaving autoproblems.

What I do is copy the text out of each cell and paste the data in Notepad, then delete the row. Then I immediately copy and insert a row that is blank but has the same formatting and good behavior as the row that was deleted. (You must copy and insert the entire row by using left row buttons.)

Once that is done, you have to go back and insert the text from notepad into each cell. (Use F2 or otherwise activate the edit mode of each cell before inserting text.) It is a pain, so it is a last resort, especially for ginormeously wide worksheets.

It is the only solution sometimes, as the problem resides in the program itself.

Answer 7

I have not thoroughly tested this but it's been working for me. Essentially it will auto adjust all sheets then go back through and set the column width to the original width, unless it's been extended by the auto adjust. I only had 10 columns and never more than 1000 rows so adjust the code as needed.

Sub ResizeCells()
    Dim sheetCounter As Integer
    Dim rowCounter As Integer
    Dim colCounter As Integer
    Dim totalWidth As Double
    Dim oldColWidths(1 To 10) As Double

    For sheetCounter = 1 To ThisWorkbook.Sheets.Count
        ActiveWorkbook.Worksheets(sheetCounter).Select
        totalWidth = 0

        ' Extend the columns out to 200
        For colCounter = 1 To 10
            oldColWidths(colCounter) = Cells(1, colCounter).ColumnWidth
            totalWidth = totalWidth + oldColWidths(colCounter)
            Cells(1, colCounter).ColumnWidth = 200
        Next

        For rowCounter = 4 To 1000
            If Cells(rowCounter, 1).Value <> "" Or Cells(rowCounter, 2).Value <> "" Or Cells(rowCounter, 3).Value <> "" Then
                Rows(rowCounter & ":" & rowCounter).EntireRow.AutoFit
            End If
        Next

        ' do autofit
        For colCounter = 1 To 10
            Cells(1, colCounter).EntireColumn.AutoFit
        Next

        ' reset to original values if current width is less than the original width.
        For colCounter = 1 To 10
            If Cells(1, colCounter).ColumnWidth < oldColWidths(colCounter) + 0.25 Then
                Cells(1, colCounter).ColumnWidth = oldColWidths(colCounter)
            End If
        Next
    Next
End Sub

Answer 8

Actually there is an extra character there. It's the hidden LF character that is at the end of every field. When the text is just the right size the extra row is there to hold the LF character. Resizing the column width as other people have suggested will resolve that cell's issue, but if your spreadsheet has lots of varying size values in this same column, it is possible that your fix for this cell will cause another cell in the column to have the problem. I've learned to live with it most of the time.

Answer 9

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 10

I didn't take the time to read the other comments. Why the line at the bottom of the first block and not in the second block?

It's because the text ends too close to the right edge of the cell. If you adjust the column to be a little bit wider and double click between the rows it will resize accordingly.

Answer 11

I too have endured this tiny but extremely distracting bug for many years. I have no work-around except fiddling around with the row width for the cell with most text in it, and eventually the program will relent and show the text properly.

Answer 12

There is an easy solution:

1) Select all rows that you want to autoformat (if it's a large block click on the first and then press Shift+End and Shift+Down)

2) Right click on the selection, Format Cells, Alignment tab, uncheck Wrap text.

Hope this helps ;)

Answer 13

I have Excel 2010. I found that this issue happened to me when my Zoom setting wasn't at 100%. As long as I am at 100%, I don't get the text cut off with the AutoFit for columns and rows

Answer 14

My hypothesis is that Excel gets knotted when it has to extend too many lines. I had a similar experience to the above. It looks like there's an extra NL/CR/LF at the end but I know there's not. If I build up the cell some characters at a time, it auto-formats OK until a particular size is reached, then it puts in the extra line. (Similar behaviour in reverse happens if you start by deleting some characters.) It happens at different times with different cells.

Bummer!

So if you can extend the cell width, you can probably avoid the problem. If you can't (like in my case) you're stuck with manual formatting. As I said, "Bummer!"

Answer 15

What worked for me (Excel 2010), was to delete a hundred or so unused columns to the right of my data. I can only guess that there was something there causing the auto row height to misbehave.

Answer 16

select all cells, and click on the line that separates the rows twice... it'll automatically do it.