Microsoft Excel - False automatic line break

Question

I have problem using the automatic line break feature and the automatic line height feature of Microsoft Excel. As you can see in the picture below I put a simple text ("Just a testtext which could break wrong", without manual line breaks, spaces or other chars before or behind the visible text and without manual line breaks in the text) in the cell A1, B2 and C3 and activated the "Wrap Text" feature for this cell. I changed the vertical aligment to top (A1), center (B2) and bottom (C3), to see if it makes a difference for the problem as @Peter K. suggested. I then resized the cells' height automatically using the automatic line height feature by selecting the whole sheet and doubleclicking on the horizontal line between the line numbers 1 and 2.

As you can see in the picture below, the text is wrapped correctly but an empty line is added behind the text (top alignment), before and behind the text (vertical centered alignment) or before the text (bottom aligment).

I would expect that the line's height is chosen, so the whole text exactly fits into the cell without empty space before or behind the text as it is shown with another text in A4. The problem really seems to be text and columnd width dependent.

How do I get rid of this unnecessary empty lines?

Adding line breaks manually with Alt+Space don't change the outcome.

Revised due to ambiguity in the question

Answer 1

The extra line comes from scaling or font definitions.
Excel calculates the needed line height for 100% scaling and the print font, but if you use a different scaling, the letters scale slightly different, and the word length can result in more or less line breaks.

Change the scale of your sheet to 100% (in the bottom right) and the problem should be gone. If you want the content larger, use a larger font, not scaling (or accept the odd extra line here and there).

If that doesn't fix it, there could be a slight difference between the print and the screen versions of the font. Try 'print preview', and check if that view does not have the issue.

Unfortunately, this is a common and known issue, and there is really not much to avoid it. You could chose another font, or simply another column width, where the minuscule difference doesn't result in an extra line break.

Answer 2

The font height measure is a bit higher than the visible portion of the glyphs, leaving space for the low end of e.g. y, g and similar.
Also add space for underlining - and special character features as ^, ~ in e.g. Æ, Å and Ö.
The space(s) for these features are then likely to be included in the Excel alignment and adjustment features (e.g. auto line height).

Edit: The extra height of the font (typeface) may well be the root of the "extra space" you have trouble with.

Changing font to something else MIGHT make a difference, depending on IT'S layout and how Excel calculates the space.

Ref: https://en.wikipedia.org/wiki/Glyph_Bitmap_Distribution_Format
Notice the FONT_DESCENT and FONT_ASCENT information.

BITMAP

Excel formula to convert the bitmap data to visible format: =SUBSTITUTE(HEX2BIN(A1,8),"0","_")

Answer 3

I cannot answer definitively for you due to not being able to make the problem appear from your pathway in my version of Excel (Version 2205 Build 15225.20288 Click-to-Run). So I cannot test for success. It is also the case because it has been a while since I confronted this issue as I do nothing currently in which it crops up.

My experience is also not as nuanced as yours as I have never encountered it applying different (though obviously related!) effects based upon the vertical alignment for the cell. My experience is just with the bothersome extra FULL line at cell bottom, akin to your Top aligned cell (A1) which surprises me since I NEVER use that alignment (mainly because decades of experience with trying now and then made cell contents DROP in the cell, relative to Center alignment, not rise, and it was always very much not my need so...) but rather use Center (>50%) and Bottom.

However, the last time I dealt with it was with a question on this site, though I cannot point you to it, and the solution (maybe "end solution" would better name that, since it doesn't seem possible to stop it during work, only to make it go away in the end) was rather odd, to say the least. Just "plain old odd"... not like "stop doing cardio and eat 12#'s of cake a day to lose weight" odd. (That does work... 'cause you die and waste away... so no props to the advertisers.)

The solution was to not use any old way to set row heights to automatic. It's odd since usually if Excel offers ten ways to invoke one thing, and they all claim to do exactly that, then they all enter that subroutine at the same place and do the same steps of code. Different when it's something called "("Legacy" now) Import Wizard" and "Text-To-Columns" in which the latter does a subset of the same subroutine, but in that case they are not called the same thing, eh?

The solution, which worked reliably for all my test samples from seeing the question, was to set the row heights for whatever row selection I made using the Ribbon's Home tab|Cells group|Format button|AutoFit Row Height mechanism. Exactly that and no other way of setting the row heights to automatic.

It was not me giving that answer, just me testing it as I found it interesting. (Maybe a year old?) But it DID work when other methods, like the between the rows line doubleclicking did not.

So I'd be saying, do the work until finished, or too aggravated to not fix what's there and move on until too aggravated to... again, and then set the row heights to automatic using that exact command in that exact place in the Ribbon (which is not a menu with submenu after submenu... no matter how many hierarchical clicks it takes to get to what you need...).

I believe it also turned out that if one uses VBA to set it, that works too. Just need the setting of it in VBA not the selection of what to operate on. So one could use Ctrl-A and run the one-line macro. Or if you work where macros are not permitted, have the macro's one line in a Named Range, go to the Name Manager and copy the Refers to to the Clipboard, exit, use Ctrl-A, and Alt-F11 then paste it in the Immediate Window and press Enter to run it.

Won't swear VBA was good for it, but I believe it was. Again, can't test here since your method of setting it up doesn't make it happen to me.

Side notes: it is completely unrelated to how fonts actually use space above and below what you normally see due to their descenders and exact ascenders. As his pic shows, and what I've personally seen, these are FULL lines of whatever heights those descenders and ascenders require. FULL lines, not extra whitespace. For whatever internal reason, this functionality of Excel's just screws up and thinks the extra line is needed. Check row heights vs. seeing that many internal rows when they actually all have characters in them. I'm saying, three lines of text actually display in the cell and you find a row height of 38.25 and two lines of text display in a similar cell, with the extra line like in his pics, and and you find the same 38.25 row height. These are FULL lines, not a font artifact. Scaling does bring its own... "special"... problems (hate it) but while it will sometimes look something like this due to its mangling of the display of fonts, it is not the same thing as this.