Excel Autofit

Microsoft Excel is not only a platform limited to storing and manipulating your data but also provides a handful of different ways to present your data in a more synchronised pattern. Sometimes, other cells contain different sizes of data. Therefore, we need to adjust the default column width and row height to make it look standard.

But excel didn't stop here. It introduced another feature that could automatically determine how much to broaden or contract the column and expand or collapse the row to fit the given data. This exclusive feature is known as Excel AutoFit, and down the lane, we will cover the different ways to use this magical feature.

1. What is Excel Autofit?
2. 3 Ways to AutoFit in Excel
   • AutoFit column width and row height using the Excel ribbon
   • AutoFit column width and row height using the Excel ribbon
   • AutoFit column/row using a keyboard shortcut
3. AutoFit feature not working
4. Alternatives to AutoFit

What is Excel Autofit?

"As the name suggests, AutoFit is an important feature designed to fit different-sized data values automatically by the active cells in the worksheet. It prevents the manual labour of adjusting the column width and row height to accommodate the data."

Excel Autofit is further split into two parts:

1. AutoFit Column Width: This feature changes the selected column width to accommodate the corresponding cell's longest data value. This option increases the column horizontally to hold extra-large text.
2. AutoFit Row Height: This feature adjusts the row height (by enlarging the row height vertically) to hold the largest value in the row.

Most commonly, we export data from different sources to Excel. In such situations, the data does not fit in one particular cell, only half of the value is shown, and the other half gets hidden or sometimes the values exceed current cell (refer to the below image).

In the above example, Microsoft Excel automatically adjusts the row height based on the text height. Therefore, the auto-fit row concept is not often used as the autofit columns option. Though sometimes, while importing text, the row heights may not change automatically, and in these circumstances, the AutoFit Row Height feature becomes very helpful.

Though resizing (automatically or manually) seems easy and seamless, one should consider the following limits to how large or small the column's width, and row height can be made:

• The standard size of an Excel column is 8.43. Columns can hold a maximum width of 255. This number represents the total number of characters in the standard font size a column can bear. It may lessen the maximum column width if you try to use a bigger font size or apply different font features such as italics or bold.
• The standard height of a row ranges from 15 points on a 100% dpi to 14.3 points on a 200% dpi. Rows can hold a maximum height of 409, where 1 point represents 1/72 inch (approx.) or 0.035 cm (approx).

NOTE: If you set the column width or row's height to 0, the respective column or row is hidden from the Excel worksheet.

3 Ways to AutoFit in Excel

One of the best parts of Microsoft Excel is it offers different ways to do most things, and you can choose any of the methods depending on your preferred work style.

AutoFit columns and rows using double-click

The most commonly preferred method to auto-fit in Excel is simply double-clicking the border of a column or row:

1. Autofit single column: Put your mouse cursor over the right boundary of the column heading. You will notice a double-headed arrow will appear. Once it happens, double-click on the border. The entire column will automatically fit as per the data size.
2. Autofit single row: Position the mouse cursor over the lower border of the row heading. You will notice a double-headed arrow will appear. Once it happens, double-click on the border.
3. Autofit multiple columns or rows: Select the total rows or columns you wish to adjust and simply double-click the border between any adjacent column/row headings in the selection.
4. Autofit the entire sheet: Select the entire worksheet by pressing Ctrl + A or you can click the Select All option. Depending on your preference, double-click a boundary of any row or column heading, or one by one you can adjust both.

AutoFit column width and row height using the Excel ribbon

Excel ribbon is an interesting tool that contains many features, including the Auto fit:

1. AutoFit column width:
   • To select single, multiple or all columns of your worksheet, click on the Home tab -> go to Cells group -> click on Format.
   • The following window will appear. Select the AutoFit Column Width option.
   
2. AutoFit row height:
   • To select single, multiple or all columns of your worksheet, click on the Home tab -> go to Cells group -> click on Format.
   • The following window will appear. Select the AutoFit Row Width option.
   

AutoFit using a keyboard shortcut

Excel expert usually prefers to work with keyboard shortcuts rather than hovering with the mouse. Following are steps to autofit text in Excel using a keyboard shortcut:

1. Select a cell in any preferred row or column where you want to apply the autofit:
   • To autofit multiple adjacent or non-adjacent columns or rows, select any preferred column/row that you wish to autofit and hold the Ctrl button while selecting the other columns or rows.
   • Press the shortcut key 'CTRL + A' to autofit the entire sheet or you can also click the Select All button.
2. To Autofit the columns or rows press one of the below given keyboard shortcuts:
   • AutoFit column: Press Alt + H-> O and, -> I
   • AutoFit row: Press Alt + H-> O and, -> A
   

The above keys are also known as hotkeys. Please note that the concept of hotkeys is different from shortcut keys. You should not hit all the keys together instead, each should key should be pressed and released in steps:

• Pressing 'Alt + H' together will select the Home tab.
• After that, press key 'O' to open the Format menu.
• Press 'I' key to select the AutoFit Column Width .
• Press 'A' key to selects the AutoFit Row Height option.

It is not possible to remember the whole sequence of the hotkeys. But don't worry, hotkeys don't work like that because as soon as you hit 'Alt + H', Excel will quickly display all the keys on the top of the ribbon bar. Once you select the Format menu, Excel will further show you the keys to choose its items:

AutoFit feature is not working

Though the Excel AutoFit feature works perfectly almost in every scenario. But at times, it doesn't work up to your expectations and fails to auto-fit columns or rows, primarily when it is used in combination with other Excel features, unlike the Wrap Text feature.

Consider the following example:

You entered a text in a cell and set the desired column width to accommodate the data correctly. After that, you turned on the Text Wrap feature, selected different cells and double-clicked the boundary to adjust the row height. In most cases, it works fine, and the rows are autofitted accurately.

But sometimes (it mainly occurs in any version of Excel 2007 to Excel 2016), some extra space arises towards the last line of text. Moreover, it may seem fine on the screen visually, but when you print it, it gets cut off.

The above problem can be corrected. By trial and error, the following solution has been found:

1. Select the entire worksheet using the shortcut key Ctrl + A.
2. Select any column and drag the right boundary of the column and make it a bit wider (since you have selected the entire website, the same re-sizing will be applied to the whole sheet).
3. To autofit the row height, double-click on the row border.
4. To autofit the row height, double-click on the column width.

This will resolve your problem and you can autofit any text without any hassle!

Alternatives to AutoFit in Excel

Although the Excel AutoFit feature is very handy and effortless when it comes to accommodating the data of your Excel in the cell of your columns or rows, it may be quick and saves a lot of time. Still, it's not an option for large text strings that contain tens or hundreds of characters since so many characters on one long line would look odd!

For such cases, the best solution would be wrapping text so it will accommodate the text on multiple lines rather than autofitting it on one line.

Another possible method to adjust long text is to merge multiple cells into one single cell. To carry this method, select any two or more adjacent cells, and from the Excel Home tab, click on the Merge & Centre option in the Alignment group.