Leading the way in Microsoft Office Development

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.

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

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

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.

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

You can get a column width to automatically expand to accommodate the widest text by pressing (Format > Column > AutoFit Selection).

Autofit can be used to change column widths to fit the widest entry in a column.

To auto adjust the column width to the contents, double click on the border the right of the column heading.

You can change the width of a column by positioning the cursor on the line between a particular column and the next. The cursor will change to a thick black line with two arrows pointing in opposite directions. Once the new cursor appears you can drag holding down the left mouse button to adjust the size.

The rows and columns can be easily resized. Place the mouse near the black line to the right or below the column or row you wish to adjust. The mouse pointer will change allowing you to drag the edge.

You will also get an on-screen indicator of the height or width as you drag. You can just double click for it to accommodate the widest or tallest entry. If you select multiple rows or columns you can resize them all with a single mouse click.

Resizing one of the selected rows or columns will resize them all.

You can prevent long numbers from expanding the columns widths. If the column has already been resized or the number exceeds the current width then ##’s are displayed instead of digits. You can use the TEXT function. This displays the whole number as text.

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 - 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).