Excel 2010 column width changes

Topic

New Reply

This seems like such a petty annoyance, but it’s driving me (and some of my students) insane…

I teach a computer applications course at a community college and use some auto-grading software for students to submit work. The work is done in the Excel application (not a simulation). One of the tasks is to set an Excel spreadsheet column width to a specific size. One such size is 15.86, another is 14.11, and so on. Students have reported that they apply these settings, but when they redisplay the column width, the settings have changed…usually by 3/100, so 15.86 becomes 15.89, 14.11 becomes 14.14, and so on. Sometimes the sizes change up, and sometimes down. I can replicate this on my own two copies of Office (one on my desktop, one on my laptop).

I know in the real world, people rarely need such precise column widths and instead probably just auto-fit to the widest entry, but a) students are getting marked wrong, and b) it’s driving me nuts that I can’t trace the source of the error. I get similar behavior whether I access the column width dialog by right-clicking the column, or by using the ribbon (Format > Column Width).

Any ideas?

Reply | Quote

Replies

I speculate that XL stores the widths in PIXELS and that there are no partial pixels, so it puts it as close as possible to the request…

Steve

Reply | Quote

That would seem reasonable, but the change in settings DOES stick on a third computer in the house. I can’t find any settings that are different between the one that works and the two that don’t.

Reply | Quote

Default fonts and size, display drivers, even default printers and printer drivers are probably at play too…

Steve

Reply | Quote

Excel sets column widths in POINTS. Then the complexity starts, since there are variations between fonts, between displayed and printed fonts, between truetype and fixed fonts and so on.

Anyway, the upshot is that you cannot create a precise, repeatable column width across a number of PCs unless a host of settings are identical – in practice never.

Excel is also inconsistent about how it rounds to get to the nearest whole point size, and of course the vertical and horizontal measurement systems are different. Hence whole treatises have been written on how to achieve square cells !

I know this isn’t much help, but its the way it is.

Reply | Quote

I’m interested in a resolution to this as well. We use an Excel template to upload journal entries into a PeopleSoft accounting system. The process that does the upload uses the column width to determine the number of characters a field can be. My computer has Excel 2010, but almost everyone else uses Excel 2007. I noticed recently that when I open the templates, the column widths have decreased by .11–what was once 16 is now 15.89, 2 is 1.89, etc. This does not seem to change anything for other users, however, when I same the file as Formatted Text (space delimited), which is required to perform the upload, the columns have these weird widths and the upload fails–I have to change the widths, which then messes everyone else up. Obviously we’ve decided to just have two templates, but it seems weird that the width would change between the two versions.

Reply | Quote

A number of thoughts.

1. Have you tried protecting the 2010 sheet with only “Format Columns” checked ?

2. Differences such as you describe are often due to different fonts being used on the different PCs, rather than different versions of Excel. You can even get subtly different versions of a font with the same name ! The Excel default font is the usual culprit. Can you check that your PC isn’t different from those with the Excel 2007 installations ?

There are some additional clues to the great column width mystery here: http://support.microsoft.com/kb/214123

3. Given the extremely quirky way that Excel (all versions) determines column widths, it seems to be asking for trouble to depend on “The process that does the upload uses the column width to determine the number of characters a field can be“. Is there maybe another way that you can determine the number of characters in a field that doesn’t depend on such shifting sands ?

Reply | Quote