16

When I type a long sentence (or more than one sentence) into more than one cell in a row and apply the Text Wrap option to these cells, the cells have an extra blank line on the bottom of them. There are no extra spaces after my text. I have put the cursor at the end of the text and used the Delete key.

My text is formatted to be aligned to the Top and Left.

  • The Auto Format Height option does not get rid of the extra blank line.
  • Adjusting the width of the cell doesn't help.
  • I tried copying/pasting the text into Notepad and then back into Excel to try to get rid of any formatting. That did not work.
  • I am not using Bold, Italic or any other font setting. I am using the default font when I am entering the text.
  • I have searched the Format Cells window. Nothing there gets rid of this extra blank line.

Is there some other setting I can use to get rid of this extra blank line? I realize that Excel isn't really made for word processing. But, I need to enter in comments on this sheet and use the Text Wrap feature.

I am using Office/Excel 2007 for Windows.

  • Clarification: does this happen when your zoom is at 100%? Or only when you're zoomed in/out? – Dane Mar 10 '14 at 19:59
  • The zoom does not have any affect on this. I am at 100% zoom. – Junco Mar 11 '14 at 15:13
  • 2
    Excel has had this bug since the nineties, possibly since the eighties! They must have convinced themselves that they need to retain it for compatibility. Very infuriating. – Paul Kienitz Aug 11 '16 at 18:08

15 Answers 15

5

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.

  • 1
    I've encountered the same thing in 2010. No sure fire solution yet, but zoom levels have sometimes made it look worse. – Dane Mar 16 '14 at 0:47
9

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.

  • 9
    This is because of printer fonts. Excel actually checks how it fits when printed, and if it doesn't fit in a line on the printer, it will make an extra line (which seems superfluous on the screen). You can verify that by changing your default printer. – Aganju Jul 20 '16 at 13:20
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.

  • Column width adjustment + auto-height worked perfectly in Excel 2013 Windows. – TaeKwonJoe Mar 18 '16 at 23:04
1

Excel lays out text differently in wrapped cells to the way it lays it out in text boxes.

(Try it: create a text box the same width as your wrapped cell, with zero margins, check Resize shape to fit text and Wrap text in shape, and paste in a copy of the text from your wrapped cell).

Knowing this, it seems that the problem occurs because the Autofit Row Height command adjusts the height of the row as if every cell of the row is laid out using the algorithm used for text boxes rather than the algorithm for wrapped cells. Autofit Row Height also ignores merged cells, so it really isn't very usable in many situations.

Solution: put your text into text boxes rather than wrapped cells. Of course, this introduces its own world of pain, but if you generate and populate the text boxes using VBA it isn't too difficult.

1

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
0

I tried 6 of the 7 answers that were here (excepted the text boxing) and with a bit of fiddling the ideas all worked but, on some rows only. I'm working on an Excel 97-2003 (.xls) workbook using compatibility mode from Excel 2016 but I see 2007 has the same commands I used below...
Another thing that worked on some was to type a blank into the empty cells between the other data. Some rows were corrected by copying the formatting from a good example row, onto the problem row. Using the formatting dialog box to change all the formatting on all the data at once and reapply it also worked on a few rows. What I found that worked on all the many misbehaving rows I had left at once was to select all the data area (CTRL+HOME then CTRL+SHIFT+END will select cells that surprisingly contain invisible data too), go to the HOME tab, EDITING section, select the arrow beside CLEAR, choose CLEAR FORMATS. Then select all the data you want text wrapped and either right click or use CTRL1 to open the Formatting dialog box, alignment tab, and checkmark beside WRAP TEXT, ENTER, check the data all over for wasted lines (are there blank spaces leading or following your text?), then reapply any other formatting you were using. Voila! The end of many hours of frustration. (

0

Please do the following: 1. Highlight the column, control+h (to find and replace) put a space in find and put any special character to '-' to replace. 2. Convert "text to colomn", delimit with the special character replaced.

Enjoy...

0

I read in diagonal some answers and some are useful. Eventually after fidling with the issue I found:

  • Adding text to adjacent cells also causes same symptom.

So I messed a bit more and here's the (eventual) solution - in Excel 2013:

  1. Click on Column header of the "wrapped cells" to select whole column.
  2. Widen the column by dragging the right divider.
  3. Remove and re-add the "Wrap text" setting.

Issue should disappear. Let me know if this fixes the issue.

  • 1
    This very same solution has already been proposed twice in two different answers, plus discussed in multiple comments (and doesn't always work). We don't need another past saying the same thing. Please read all the previous answers thoroughly before posting a new one, especially on old questions with lots of answers. – robinCTS Jul 18 '18 at 11:02
0

The only solution for this problem seems to be complete reinstalation of the MS Office. I searched all over the internet forums and nobody knows how to solve it. For me, the reinstalation worked, so give it a try. :)

  • 1
    Well, there are several solutions on this page.  Since this is a “feature” of Excel, it seems unlikely that reinstalling the same software would result in different behavior. – Scott Jan 22 at 9:30
0

This bug remains in Excel 2016, and it has annoyed me for decades. Making the row fixed height always comes with the risk that some text will get hidden later if more text is added to the cell. Michael Krebs' solution will work most of the time and avoids this risk. Aganju seems to understand the problem well. I think that TaeKwonJoe was just lucky with Excel 2013. Paul Kienitz has my sympathy. The other fix that avoids the risks involved with fixed row heights is to tweak the font size up or down by a small fraction, say from 11point to 10.8point. Like adjusting row widths, it's a 'one time fix' that doesn't guarantee the extra space returning, but this way you don't mess up the formatting of the rest of the sheet. Good luck!

0

I am on a work computer and don't have administrator rights, so some of these options weren't able to be tried, all others didn't work for me.

I did find a resolution for Excel 2016. I simply used the format painter from a cell that didn't have the spacing issue and copied the formatting to the offending cell. It worked on all of my cells and was minimal extra work, not a great fix, but it can keep from having to adjust fonts or cell sizes and messing up the other formatting.

-1

This problem was bothering me for a very long time too.

My only solution to get rid of the extra space /enter was to finish the sentence with a dot / full stop / the key '.' This works 100% for me, where all other solutions failed (including copy pasting in notepad (back and forth), tinkering with layout, cell width, etc)

Yes, turns out it was that simple. Just finish the line with a hard break / dot.

Good luck!

  • 1
    When this happens to me, ending the sentence with a period makes no difference. – Blackwood Jan 1 '18 at 19:10
-1

i faced same problem & now solved it. Pls select the cell, click F2, bring mouse pointer at end of the text (press end button), click delete button couple times till u feel that some unwanted blank space is removed, then press enter button.... here u go. Thanks.

-1

I highlighted the entire row. Then clicked on "Home" at the top left. Then a "Format" drop-down menu appeared on the upper right. With the row still highlighted, select "AutoFit Row Height" from the "Format" drop-down. Magic!

-2

When you are on a cell, if you click the "normal style" it should get rid of any extra space in the cell.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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