Excel 2002 Column Widths & Printing

[mlv]

Excel column widths are starting to confuse me!

I have a workbook containing several sheets. The sheets are not identical
and have differing numbers of columns, e.g. some sheets have 9 columns,
whilst others have 11 or 14.

The margins for each sheet are exactly the same with 0 set for header and
footer. The printer setup for each page calls for 100% normal size, A4, 1
page wide by 1 page tall. The correct print area is set for each sheet.

The various column widths for each sheet, when added together, are identical
(86 units in this case, although what the 'units' are, I do not know). I
always check using print preview to ensure that the full width of the
document will print, and reduce the column widths if the preview shows the
right-hand edge clipped.

I've just had to reduce the column widths on one sheet (9 columns wide) as
the print preview showed its right-hand edge clipped. I reduced the column
widths by the minimum necessary for the print preview to show it printing
fully. However, when I printed the sheet out, it printed 10mm narrower than
one of the other sheets (14 columns wide) printed on the same printer at the
same time.

Rather bizarrely, I sometimes find that on re-opening an Excel document
where I previously had to reduce the column widths for it to print fully, I
can increase the column widths again, and it still prints OK.

Am I missing something here? Is there something else that influences column
width, or some parameter that I have not set correctly? Does, for example,
Excel increase a column width automatically, if the text in a cell is too
wide to print?

 

[David McRitchie]

Hi Mike,
When you indicate 1 page wide by 1 page tall.
Excel will force it to fit on one page. The settings are independent of
each other, so you could get a big variation depending on how many
rows you have.

If the printing is too small you may wish to employ some additional
techniques, such as fitting all of the columns to the data, turning on
text wrapping.

Fit Print to Page, and Adjustments to Layout
http://www.mvps.org/dmcritchie/excel/fitprint.htm

Not sure but I think the fonts are adjusted to integer fontsizes.

 

[mlv]

Hi Mike,
When you indicate 1 page wide by 1 page tall.
Excel will force it to fit on one page. The settings are independent
of each other, so you could get a big variation depending on how
many rows you have.

If the printing is too small you may wish to employ some additional
techniques, such as fitting all of the columns to the data, turning on
text wrapping.

Fit Print to Page, and Adjustments to Layout
http://www.mvps.org/dmcritchie/excel/fitprint.htm

Not sure but I think the fonts are adjusted to integer fontsizes.

Hi Dave, thanks for the info.

Is there an easy way to ascertain the total width in column units of a
particular worksheet, other than interrogating each column in turn and
adding the width units together?

What is a column width unit (or a row height unit, for that matter)? Is it
something I can set, e.g. I column width unit = 1.0mm?

 

[Gord Dibben]

Row heights are measured in points or pixels. There are 72 points to an inch
and "maybe" 96 pixels to the inch.

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

For an interesting and enlightening discussion on this subject see

http://snipurl.com/dzz8

If you want to use VBA to set height and width in mm.

Ole Erlandson has code for setting row and column dimensions.

http://www.erlandsendata.no/english/index.php?d=envbawssetrowcol

To get column width use this UDF

Function colwidth(x)
Application.Volatile
colwidth = x.ColumnWidth
End Function

=colwidth(A1)


Gord Dibben Excel MVP