Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
In case anyone wants to reproduce the example in my question, the width of column A is 17.15 (125 pixels). –  devuxer Feb 14 '12 at 21:47
    
Can reproduce this –  Raystafarian Feb 14 '12 at 23:54
    
Bug seems dependent on font and font size –  Raystafarian Feb 15 '12 at 0:08
1  
I tried autofitting the columns, which widened one column, and then autofitting the rows. That got it down from 73 to 64 pages but still lots of blank lines at the bottom of most of the rows. I have the rows aligned top. Manually changing the size of the rows makes it look OK when I'm working on the doc, but not in the preview or in print. –  user150426 Aug 5 '12 at 7:15
1  
Yes, so annoying! Also seen this at least in Excel 2007. Don't recall ever seeing it in Excel 2003. –  abstrask May 23 '13 at 20:57

14 Answers 14

up vote 30 down vote accepted

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!

share|improve this answer
    
This really does seem to work. The one gray area is where you say "a little wider". One of the examples I tried didn't work unless I stretched the column a fair amount, but I did eventually get it to work. Thanks! –  devuxer Feb 14 '13 at 2:18
2  
I selected the whole sheet then double clicked the row separator between two random rows and the entire document re-adjusted the row heights. –  Keith Sirmons Aug 16 '13 at 19:02
    
Note that AutoFit does not work for any merged cells: support.microsoft.com/kb/212010 –  freb Jul 21 at 18:59
    
Awesome! Does anybody know of this is possible in a VBA script?? –  Andreas Furster Dec 4 at 19:50

The following should really be added as a comment to the original question, but because it has been locked I need 50 rep in order to do that and can't.

I simply wanted to add that, as well as the "adding extra space" symptom, the row auto-height problem also manifests itself in a manner which causes the last line of wrapped text to be HIDDEN from view. This occurs especially at zoom levels under 100% when the wrapped text in question contains no spaces. I have so far been unable to find a solution to this.

share|improve this answer

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.

share|improve this answer

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!"

share|improve this answer

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.

share|improve this answer
    
I cannot duplicate this behavior. As soon as I do "Auto Fit Row Height", any manual height setting for that row is immediately replaced with the auto-fit height. The problem is simply that the auto-fit height is often wrong. –  devuxer Jun 20 '13 at 17:31
    
I CAN duplicate this behavior. This is exactly as it works for me. –  Peter Nore Nov 3 at 15:38

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

share|improve this answer
1  
In the example I used for my question, I was at zoom = 100% the whole time. The problem occurred regardless. –  devuxer Jun 20 '13 at 17:33

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
2  
I can see how that would help, but it doesn't make any difference for the test case shown in my question. –  devuxer Dec 20 '12 at 0:52

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.

share|improve this answer
1  
I have no doubt you are correct, but then that's not "auto" anymore, it's manual. –  devuxer Dec 20 '12 at 0:44

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.

share|improve this answer

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.

Changed font to Courier

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

print preview of same file

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

share|improve this answer
    
So... basically they broke it. You're explanation is plausible though, but it really bugs me, that it is like this :-/ –  abstrask Jun 28 '13 at 23:31

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.

share|improve this answer
1  
Your steps are no different than what I already tried (double-clicking between rows is just a shortcut for "Auto Fit Row Height"). As for your theory that there is an extra character, you may be right, but I certainly didn't type one, and I wouldn't know how to get rid of it. I invite you to try typing the exact text as I have in my illustration into Excel and see if you get the same results. –  devuxer Feb 14 '12 at 21:39
    
There's no character there on mine. –  Raystafarian Feb 15 '12 at 0:08

protected by Community Aug 16 '13 at 23:43

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.