Leading the way in Microsoft Office Development

Excel

Step 1 - Understanding Column Widths

Excel supports both proportionally spaced fonts, (like Arial) as well as fixed pitch fonts (like Courier).

Different characters take up different amounts of space.

Be careful as different people creating different workbooks in different fonts will be slightly different when it comes to row heights and column widths.

The standard column width corresponds to the average number of digits which can be contained in a cell according to the font selected.

Assuming that the font is the default (Arial 10) the default column width is 8.43 (64 pixels).

This represents the number of zeros that can be displayed in the column.

This is an approximate width in points the same measurement technique used for font size.

The default font for new workbooks can be changed from the (Tools > Options)(General tab).

The font for an existing workbook can be changed from the (Format > Style > Normal > Modify) dialog box.

Step 2 - Adjusting Column Widths

You can adjust the widths of your columns in various ways:

1) Using the (Format > Column) sub menu.

2) Using the Column shortcut menu.

3) Using the Mouse.

Step 3 - Using the (Format > Column) sub menu

Width - Displays a dialog box letting you define the exact width of the selected columns.

AutoFit Selection - Adjusts the width of all the columns in the selection to accommodate the widest text.

Hide - Adjusts the width of all the columns in the selection to zero.

Unhide - Adjusts the width of all the columns in the selection to ??

Standard Width - Displays a dialog box allowing you to define a different default column width. Just pressing Enter will restore the currently selected columns to there default width.

This width may or may not be 2 decimal places. It depends on what font is being used for the Normal style.

Arial, 10 will display 2 decimal places. Times New Roman, 8 will display 1 decimal place.

Step 4 - Using the Column Shortcut Menu

An alternative to using the (Format > Column) sub menu is to use the Column shortcut menu.

This menu will be displayed if you press the right mouse button when the cursor is over a column heading.

Column Width - Displays a dialog box letting you define the exact width of the selected columns.

Hide - same as above.

Unhide - same as above.

Step 5 - Using the Mouse

The easiest way to change the width of a column is to position the mouse pointer on the line between the columns. The pointer changes to a two way arrow, click and move the mouse to the desired width.

You can drag the mouse to the right to increase the width of your columns or to the left to decrease the width.

As you drag the side of the column the new width and the number of pixels is displayed in a screen tip.

Step 6 - AutoFit the Columns

For more information please refer to the AutoFit Selection page.

Step 7 - Defining Default Column Width

To define the width for a particular column you only need to select one cell in that column.

Defining a default column width has the effect of adjusting all the columns to the same width, except for those previously modified.

If no width appears then more than one column of different widths is selected. You can enter a number between 0 and 255.

(Format > Column > Standard Width)

If no columns are highlighted then all the columns are changed.

To change the standard width for all the default width columns. Enter a value in the standard column width box.

All columns adjust to the new setting except those that have been changed manually.

Step 8 - Locking the Column Widths

Adjust the columns to the width you want to lock them at.

Select (Tools > Protection > Protect Sheet).

Adjusting the width of a column falls under the item "Format Columns".

If this checkbox is not ticked then the column widths cannot be adjusted once the sheet is protected.

Step 9 - Things to Remember

This also works with non-contiguous rows or columns by holding down the Ctrl key.

Depending on which font you are using, text may appear to fit within a column on your screen but not when it is printed. You should always check your Print Preview to check.

You can change the widths of all the columns on a worksheet by selecting any row and selecting (Format > Column > Width).