Autofit inconsistent

[Larry Dunn]

I have observed, for the first time, that Excel doesn't autofit a line the same each time - even on the same computer.
I put in this test line:

A doubtful test line with random words to determine the width bias of the current computer.

The first time when I autofitted it, I got a width of 71.88. The second time, 79.63.

After restarting Excel, I went through the same sequence, with identical results. (At least something is consistent.)
On the third attempt, the 79.63 recurs. That's good, but not great.
That means that every time I want to autofit a line, I must first autofit the line, then do that again, and keep the second result.

This has very bad repercussions for portability. My procedure that wraps text can't possibly guarantee its results, even approximately,
when run on a different system.

I've tried developing a ratio, by autofitting my test line, and applying that ration to the observed width works fairly well, but
I'm not happy with it. It's not precise enough, and all this, it seems to me, shouldn't be happening.

I can't say if this is new behavior, or if I have been sleepwalking all these years.

[mikerickson]

If you want text to wrap where you want it to, insert line breaks. Either CHAR(10) or CHAR(13) work.

[ZVI]

I have observed, for the first time, that Excel doesn't autofit a line the same each time - even on the same computer.
I put in this test line:
A doubtful test line with random words to determine the width bias of the current computer.
The first time when I autofitted it, I got a width of 71.88. The second time, 79.63.
Only monospaced fonts like Courier New have constant spacing between characters while proportional fonts have variable spacing between characters.
Read this article for more details - Description of how column widths are determined in Excel (https://support.microsoft.com/en-us/kb/214123)

[Larry Dunn]

I'm an idiot. Always have been.





When I put the test line into the cell, it was in the Calibri font, 11 point.


Then I changed the font to Arial 12, and tested again.


I don't really know how I did that, but it's the only way I can reproduce the result.





But there is a mystery about the AutoFit feature. When I fit a line on one


computer, it may fit within a line of column width 70.





That same line, on another computer, may yield a column width approaching 80.


Or just over 60.





I don't understand this. The screen resolution might be different, of course,


but that reduces/enlarges the text equally with everything else. Since the column


width, I think, is defined in terms of points, as are the font sizes, everything


should come out the same. But it doesn't.





I was able to equalize my word wrapping, though, with the following trick.


On opening the file containing the procedure, I put the test line into a cell,


and performed the autofit. I then read the column width, and divided it into


the column width I observed on my home machine. I saved this ratio in another


cell.





Later, when I ran the word wrap procedure, I used the ratio to inflate,


or deflate, the observed column width. The end result was that the words


wrapped around the same way on both machines. But one looked reasonable,


and the other was just a little bit too long. Very odd.





I should explain that, when I talk of word wrap, I mean that I examine a long


line, and move one or more words down to the next line, until the line is


short enough to fit. I then move down through the text until the end of the


paragraph.





In this way, a long line will be transformed into a paragraph of separate lines.

By the way, neither of the two responses above seems to have anything to do
with the behavior I described. ( I admit that I described it incorrectly.)

[ZVI]

For me the below citation from the provided link (https://support.microsoft.com/en-us/kb/214123) explains that behavior

SUMMARY
The standard column width in Microsoft Excel 2000 is 8.43 characters; however, the actual width that you see on the screen varies, depending on the width of the font defined for the Normal style of your workbook. Changing the default font also changes the column width. This behavior occurs because of the way that Excel stores the column width information for individual fonts. This article discusses how column widths are determined.

MORE INFORMATION
Excel begins with a default width of 8 characters and translates this into a given number of pixels, depending on the Normal style font. It then rounds this number up to the nearest multiple of 8 pixels, so that scrolling across columns and rows is faster. The pixel width is stored internally in Excel for positioning data on the screen. The number that you see in the Column Width dialog box is the pixel width retranslated into character units (based on the Normal font) for display.

A column width of 8.43 means that 8.43 of the default font's characters fit into a cell. The default font for the worksheet is the font that is assigned to the Normal style.

Note Changing Printer DPI can affect the font metrics and can adjust the column widths.

Please pay the attention on default font and on DPI (Dots per Inch setting of Desktop customization)

[Larry Dunn]

Thanks, ZVI.

This answers many questions, and more importantly proves that there are people who actually understand this stuff.

If I'm understanding correctly, this means that if my default font is Calibri 11, and yours is Arial 12, a column width of 70, say, will appear wider or narrower.
This makes sense to me, after your explanation, since the column width is defined on the default font.

When I look at the monitors of my two machines, I see a vastly longer line on one. I have the same file up on each screen, and they have the same
default font of Calibri 11. Yet the line width of 70 is barely enough to accommodate the text on one screen, and almost too much on the other.

Is it possible that the DPI setting could change the view that much?

All this would seem to make it impossible to get the same result on two different machines.

I've been able to get my 'word wrap' to function the same on my two machines, and I believe it will test out to work out on others.
What I'm doing is using a standard line of text, and autofitting it, then computing the ratio of the new column width to the observed
column width on my home system. I then use that ratio to control the moving of words down to the next row.

Silly as it seems, it works.