Leading the way in Microsoft Office Development

Excel

Step 1 - What is AutoFit ?

AutoFit is the name given to adjusting the width (or height) automatically to fit the contents of a cell, row or column.

Before we discuss the AutoFit in more detail lets remind ourselves what the default behaviour is in a new workbook.

The column width will be 8.43 and the row height will be 12.75 if we assume the default setting of Arial 10 for our standard font.

When we enter text into a cell the column width will not change.

When we enter numbers into a cell the column width may or may not change.

If you enter less than 9 digits the width will not change and the most significant figures will be displayed.

If you enter 9, 10 or 11 digits (without a decimal place) the width will automatically increase to display the whole number.

If you enter 12 (or more) digits the width will automatically increase to 11.71 and the number will be formatted (and displayed) in scientific notation.

Column "C" displays the number that was typed in to cell "B3".

The column width will only increase automatically when you enter numbers over 100 million.

Whether the number contains decimal places or not is irrelevant.

Step 2 - AutoFit Columns

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

A quicker way to AutoFit a particular column is to double click on the edge of the column heading.

Move the mouse to the column edge and when the cursor changes to a double arrow double click with the right mouse button.

You can also select multiple columns and use the same method to AutoFit all these columns in a single double click.

If you manually adjust the width of a column the column will always stay that width.

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 3 - AutoFit Rows

You can get a row height to automatically adjust to accommodate the largest amount of text by pressing selecting (Format > Row > AutoFit).

A quicker way to AutoFit a particular row is to double click on the bottom of the row heading.

This only works if the cell has been formatted with Wrap Text. (Format Cells)(Alignment tab).

The row height depends on the font size applied and not on whether the row actually contains any text.

Excel makes the default row height a little taller than the text.

This command returns empty rows to the standard height and other rows to the tallest height to accommodate the entry.

If any rows are formatted with a particular font size then that font size is used to determine the height of the row.

Step 4 - AutoFit Worksheet

You can AutoFit an entire worksheet with one double click

Select the entire worksheet by selecting the Select All in the upper left corner of the worksheet.

Double click any boundary between columns to autofit all the columns

Double click any boundary between rows to autofit all the rows.

You can the select the individual rows and columns to make any manual adjustments.

Step 5 - AutoFit Exceptions

If any cells have been formatted with the scientific number format such as 123E+13 then AutoFit will not adjust the column width required to display the number in full.

You will have to change the number format first to be able to display the number in full.