Inconsistent Column Widths in Excel

Question

I'm working in two different excel workbooks that were created by two different people.  Both excel workbooks have the Font set at Times New Roman 12, and the column width to 14.  However, when I have both columns set with an Accounting format, zero decimal places, and with a $ sign one of them only displays 7 characters before it turns to #####, while the other will display 11 characters before it turns to #####.  Does anyone know what setting is causing the two different workbooks to present the same settings in different ways?

I'm specifically looking for what I need to change in one of the files so that it will also display the 11 characters in a consistent format as the other workbook, without just increasing the column width.  I'm using the excel files to present financial information in report, so I'd like them to exhibit the exact same characteristics so I don't need to eyeball them for consistency.

Any comments or suggestions would be greatly appreciated.

Answer 1

Not with any certainty, sorry.  I could guess that it's somehow possible that the default printer setup on the two originating computers is different.  The setup on-screen and for printout is often based in part on the default printer.

As an example: I often have to try to closely emulate the look of a PDF or Word document in Excel.  The test for accuracy is to simply align a printout of the original document and a printout from Excel and hold it up to a light to see if things align closely enough.  But I have to do it on just one computer, and use the printer that the final product will be printed on: if I do all the setup on another system with a different default printer and get everything 'perfect', it will NOT be perfect when printed on the final production system with the production printer.  And this means changing row heights/column widths on screen to get the proper output for the final work.

Answer 2

Do you have any other ideas as to what might be causing the difference between the files if it isn't the shrink to fit feature?

Thanks

Answer 3

" Is there another setting that may do the same thing but for an entire workbook?"

Not that I'm aware of - the Shrink to Fit is a cell formating option, so it can be applied at the cell or worksheet level only.  You could do it for all cells on any given sheet, but you'd have to do that sheet by sheet for each sheet in the workbook.

It is to be expected that when shrink to fit is turned on and when it's utilized by the cell, that the font is going to look smaller than the font in cells that didn't have to be shrunk.  So $ 100.00 would appear at 12 point, while $ 11,222,333.44 would appear in smaller font because it was shrunk to fit.

Answer 4

As you suggested I checked both workbooks, and neither of them has the shrink to fix setting turned on.  I also tried changing one to shrink to fit as you did and I had a similar result where it fit with the column width of 14.  However, I did notice that when the shrink to fit feature is used, it makes the font appear to be smaller than the rest of the text within the sheet.  Below I've included a snip from each of the workbooks.  The first one is from the file that fits more characters, while the second is from the other file with the shrink to fit turned on.  As you can see, the shrink to fit seems to make the numbers the same size, but the size of the text associated with each item is not consistent because the numbers were shrunk.  Is there another setting that may do the same thing but for an entire workbook?

Thanks

Answer 5

Initially the differences could be related to the resolution of the originator's video display.  But that's not the problem at hand.

One possible solution is to get one or both of them, or do this at your end, to select the column and then on the [Home] tab use the pull-down in the lower left corner of the Alignment group to display the Format Cells dialog and on the [Alignment] tab in it, check the Shrink to fit option. 

The question then is whether or not the contents will still be large enough for you to actually make out the entry because this setting will try to shrink very long entries down to the available width which can make the effective font size virtually zero.

[Edit] Just as a note, I pulled up my Excel 2010 and set a column to Times New Roman, 12 pitch with currency formatting and entered 1234567.89 into a cell, result is ########.  I did auto-width and it changed it to width of 15.  Back to 14 width and "Shrink to fit" and the $ 1,234,567.89 is clearly readable.