Get rid of extra space in cell when using Text Wrap

Solution 1:

I was able to reproduce the behavior you are seeing (also in Excel 2007). I can't find a way to get rid of the extra space once it's there, but I found that if you reverse your steps and apply the Text Wrap in a cell prior to entering your text, you will not get the extra blank line.

Solution 2:

I had the same problem just before I've typed this. And here is what I've found.

When you press wrap text the cells height is automatically adjusted. Also its height is not calculated as we expected. If there is very little space left on the last row of the text, Excel decides that the cell needs one more row... even if there is not a single letter on it.

If you get the cell's width a little wider, the automatic cell height adjustment removes the extra line.

Solution 3:

I found that if you change the width of the column that contains the cell with the extra blank line, then auto sizing the height of the row will usually get rid of the extra blank line. Apparently word wrap messes up with particular cell values and particular widths. Try different widths until auto-sizing the height gets rid of the blank line.

You can widen the column by clicking and dragging the divider between the two column headers at the top of the worksheet. Once the column is wider, auto size the row by double-clicking the divider between the row headers on the left side of the worksheet.

Of course, if your worksheet has hundreds of rows, then each cell in that column has a certain chance of hitting the magic spot, and adding the extra line. Changing the width will clean up some cells, and add unnecessary blanks to others. In this case, the only solution that I have found is to manually wrap the text to my chosen width by inserting extra carriage returns in the middle of the text at the position that I'd like the text to wrap using alt+enter. I then make sure the column is a little wider than my chosen text width, and I won't get any extra blank lines. A terrible and labor intensive solution, but it does work.

Solution 4:

Moving to new sheet worked for me on Excel 2016:

1. Ctrl+A, to select all cells
2. Ctrl+C, to copy
3. Shift+F11, to create a new sheet
4. Ctrl+V, to paste all copied cells to the new sheet
5. Resize columns by your needs
6. Select all rows that you want to AutoFit.
7. Alt+H,O,A, to AutoFit Row Height