Excel’s grid layout can sometimes appear static without the ability to create custom interfaces to suit the needs of the data stored in the worksheet.
While this is true to some extent, Microsoft has built in Excel the ability to instantly adjust column widths and row heights to match the size of the data in the cells.
Learn how to use the AutoFit feature in Excel to automatically resize columns and rows to fit the size of the data in your worksheet.
Before changing column width and row heights in Excel
There are limits on the size of columns and rows in Excel. Column width and row height with zero value are hidden in Excel worksheet. This is equivalent to using the hide column or hide row function.
Columns can have a maximum width of 255. This number represents the maximum number of characters that a column can contain in a standard font size.
Changing the font, font size, and adding other characteristics to the font, such as italic and bold, greatly reduces the maximum number of characters a column can hold. The default Excel column size is 8.43, which is 64 pixels.
Rows can have a maximum height of 409. This number represents how many 1/72 of an inch a row can fit. By default, Excel row size is 15, which is 20 pixels, or roughly 1/5 inch.
Use the AutoFit feature in Excel
Suppose in an Excel worksheet you have text in cell A1 that is outside the default column width of 8.43 (64 pixels). Note that although the width of a column cannot contain the length of the text, Excel allows the text to flow to adjacent columns.
Select column A, click the Home tab on the ribbon, and locate the portion of the ribbon almost all the way to the right labeled Cells.
Click the Format button and find the Cell Size section of the menu. Note that there are three options for changing the column width.
Column Width – This option allows you to manually change the column width by entering a number. This option is useless because you can easily do the same by simply dragging and dropping the column to the size you want.
The AutoFit column width is the option we want. This option will resize the column to a new size based on the length of the cell content in the column that takes up the most space.
Default Width – This setting does not actually change the width of any columns; it just changes the default column widths for a specific worksheet.
From the Format button menu, choose AutoFit Column Width and notice that the width of column A has changed to match the length of the text in cell A1.
Note that you can also automatically fit a column by simply selecting the column or columns and then double-clicking the right border of any selected column.
If you want to revert the width of all columns to the default width, just select Format – Default Width and enter 8.43. You can also use keyboard shortcut to auto fill columns in Excel.
Just press ALT + H and then press O and then I. You can release ALT and H after pressing them. So the full keyboard shortcut is ALT + H + O + I.
Unlike column width, Excel automatically adjusts the row height to match the height of the text that takes up the most vertical space in each row.
Therefore, the AutoFit Row Height feature on the Format button is not as useful as the AutoFit Column Width feature.
When pasting data into an Excel sheet, there are times when the row heights are not automatically adjusted. When this happens, the text will be clipped rather than flowing to another line above or below.
To fix this, select the line with clipped text and click Format> AutoFit Line Height. This will force Excel to resize the row height to accommodate unusually tall text.
For rows, you can use the very similar keyboard shortcut ALT + H + O + A to automatically adjust the row height.
Using the AutoFit feature in Excel, you can automatically resize the column widths and row heights on a sheet to accommodate different sized text without having to manually change the width and height values.
The time-saving AutoFit feature can also make it easier to access and read the data in your worksheet. Enjoy!
–