Excel column widths

Question

I have an Excel spreadsheet with column borders (vertical borders between columns) that I want to print at exactly 11/32 inch intervals. (That's the spacing of wide-ruled lined paper, which has 32 rows per 11 inches; on my spreadsheet, that would be transposed to 32 columns per 11 inches.) I understand that Excel doesn't tally the column separator (approximately 5/7 character width) in the column widths. So I set each column width to 11/32 inches minus 5/7 character width. I did so by:

11/32 inches as character width = (11/32 inches) * (96 pixels per inch) / (7 pixel width per character for Calibri 11 default) = 4.71 characters

And then subtracting the 5/7 character width, yields exactly 4 characters width per column.

The resulting printout is close, but the distance between lines is slightly greater than 11/32 inches, and the discrepancy quickly accumulates. Why isn't the result more exact? Am I making an error in reasoning or calculation? Apparently the columns need to be slightly narrower than 4 character width.

Answer 1

Hi Richard Tillman,

The behavior may be related to how Excel calculates column widths. The measurement is based on the font and font size used by the Normal cell style rather than being a fixed physical measurement.

As a quick check, compare the Normal cell style between an affected workbook and one that displays correctly, particularly the font and font size.

If those settings differ, aligning them may help produce more consistent column sizing.

I hope this points you in the right direction.

Answer 2

A Calibri 11 column width of 4.00 already resolves to 33 pixels—the exact screen-pixel equivalent of 11/32 inch at 96 DPI—but Excel’s pixel quantization and the printer’s scaling prevent that conversion from guaranteeing an exact physical measurement on paper.

The calculation

Your target is:

[ \frac{11}{32}\text{ inch}\times96\text{ pixels/inch}=33\text{ pixels} ]

Excel’s documented column-width model uses the maximum digit width of the Normal-style font, plus five pixels comprising four pixels of margin padding and one pixel for the gridline. For Calibri 11 at 96 DPI, the maximum digit width is seven pixels.^1^

Thus:

[ 4\times7+5=33\text{ pixels} ]

So 4.00 is the correct nominal width. The five-pixel allowance is already part of Excel’s complete cell-width calculation; it should not be treated as an additional physical separator outside the column.

However, Excel converts widths to whole pixels. Microsoft explicitly notes that physical column dimensions are rounded to whole pixels and consequently may not fit an expected physical page width exactly.^2^ The 96-DPI conversion describes Excel’s layout coordinate system, not a promise that a printer will place 96 screen pixels into exactly one physical inch.

Eliminate print scaling first

1. Select File → Print.
2. Under Settings, select No Scaling.
3. Open the printer’s Properties and ensure that its paper size matches the paper size selected in Excel.
4. Disable any printer-driver option such as Fit, Shrink/Enlarge, or Borderless expansion.
5. Print a test containing 32 columns and measure between the first and last relevant border.

Excel can resize output when its selected paper size differs from the paper loaded or configured in the printer. Microsoft also documents that Fit to and Adjust to scaling change the physical output dimensions.^3^

If 100% output remains slightly too wide

Do not reduce the column width blindly to 3.99: several nearby character-width values can resolve to the same whole-pixel width. With a seven-pixel digit width, the next effective step is approximately one-seventh of a character unit. Width 3.86 is therefore likely to produce a 32-pixel column rather than a slightly smaller 33-pixel column:

[ 32/96=1/3\text{ inch} ]

That is narrower than 11/32 inch and will introduce a discrepancy in the opposite direction.

Instead, calibrate the print scale:

[ \text{scale percentage} =\frac{\text{desired span}}{\text{measured span}}\times100 ]

For example, if 32 columns print as 11.06 inches:

[ 11/11.06\times100\approx99.46% ]

Excel’s Adjust to box may only permit the precision exposed by the UI, so use the nearest available percentage:

1. Open Page Layout.
2. Select the Page Setup dialog launcher.
3. On Page, choose Adjust to.
4. Enter the calibrated percentage.
5. Print another 32-column test and refine if necessary.

Setting and checking physical width

In Windows Excel, switch to View → Workbook Views → Page Layout. In that view, Excel permits column dimensions to be specified and viewed in physical units; the ruler unit is controlled through File → Options → Advanced → Display → Ruler Units.^4^

You can use 0.34375 inch as the intended width, but Excel will still quantize the result to its supported pixel width. Therefore, Page Layout view is useful for setting and inspecting the nominal dimension, while a measured 32-column print test is the reliable way to calibrate the final paper output.

---

References

1. Column Class-Definition
2. Differences in column width between Excel 2011 for Mac and later versions | Microsoft Support
3. Scale a worksheet | Microsoft Support
4. Change the column width and row height