How to AutoFit All Columns in Excel (3 Ways)

To autofit all columns in Excel, follow these steps:

Select all cells by pressing Ctrl+A on your keyboard.
Place your mouse cursor at the edge of any column headers until it turns into a two-sided arrow.
Then double-click on it.

All columns will autofit after that.

The term “autofitting columns” refers to the automatic resizing of column width to properly display the contents of the cells within the column.

In this Excel tutorial, you are going to learn how to autofit all columns in Excel.

In the following image, we have autofitted all columns to match the size of the text within them.

Why AutoFit Columns in Excel?

Some reasons to AutoFit all columns:

Manually changing too many columns can be tedious.
The cell contents become fully visible.
Improves the readability of the user.
Improves the chance of ignoring misinformations as they are not hidden.
Ensures a clean and well-organized look while printing.

3 Ways to AutoFit All Columns in Excel

You can fit a column by double-clicking at the end of the column header. However, it will only autofit that specific column.

To autofit all columns in Excel, you can use any of these three methods:

Double-Clicking Column Headers

The double-clicking method we mentioned above can work for all columns if we select them beforehand. It is a convenient way to fit the column sizes automatically to their texts.

To autofit all columns using double clicks, follow these steps:

Select the triangle icon where column and row headers intersect to select all cells.
Then hover your mouse in between any of the column headers.
Double-click after the mouse icon changes to a two-sided arrow.

All the columns in the sheet will now fit to the size of contents in their cells.

Using “AutoFit Column Width” Feature

AutoFit Column Width is a feature available in Excel to automatically adjust the width of a column. To use this feature to autofit all columns in Excel, follow the steps below:

Select the triangle icon to select all cells in the sheet.
Then go to the Home tab. Select Cells > Format > AutoFit Column Width.

It will adjust the column width accordingly.

Keyboard Shortcut: You can press Ctrl+A to select all cells in the sheet. Then press Alt, H, O, and I in order (not all together) to access the same feature. It accomplishes the same task.

Using VBA

When it comes to autofitting columns in Excel, VBA takes the lead due to its exceptional automation capabilities and flexibility. You can create customized scripts and apply them across multiple sheets or workbooks using VBA. This makes the process efficient for batch processing and handling large datasets.

However, you need the Developer tab enabled for this feature.

You can use the following steps to autofit all columns in Excel using VBA:

Go to the Developer tab and select Visual Basic from the Code group.
Then select Insert > Module.
Insert the following code in the module:
```
Sub AutoFitAllColumns()
Cells.EntireColumn.AutoFit
End Sub
```
Now press F5 to run the code.

All of the columns in the current sheet will fit to their cell content size.

How to Fit Column Width Manually in Excel

Fitting columns manually is suitable when you need to resize one or two columns in the worksheet. The methods described above will shorten other columns in those cases. This may not be desirable for optimal view.

To fit column width manually in Excel, follow these steps:

Hover your mouse cursor at the end of the column header you want to resize.
Click and drag the cursor when the mouse icon changes to a two-sided arrow.

What to Do If AutoFit Column Width Not Working in Excel?

The AutoFit Column Width feature may not work in Excel for various reasons. If you encounter this problem, you can try these troubleshooting tips:

If the column width is set to a specific value, autofit will not adjust the width of the column. You can check the column width by selecting the column and looking at the Width value in the Cells group on the Home tab of the Excel ribbon.
If the column contains hidden cells, autofit may not work as expected. You can unhide the cells by selecting the column and going to Home > Cells > Format > Hide & Unhide > Unhide Rows or Unhide Columns.
If there are line breaks in the text, you must manually adjust the column width.
If the Excel worksheet is protected, you may be unable to autofit columns. You can check for worksheet protection by going to Review > Changes > Protect Sheet.

Conclusion

In this article, we have shown how to autofit all columns in Excel. We have used the ribbon feature, mouse, keyboard shortcut, and VBA to do so. Double-clicking the mouse is the most efficient method. However, you can use any of the methods you find suitable. You can share your thoughts in the comments below.

Frequently asked questions

1. How Does Autofit Handle Wrapped Text?

If a cell has text that wraps to multiple lines, the column width will accommodate the maximum line length.

2. Does Autofitting Include Hidden Columns?

No, autofitting doesn’t affect the hidden columns. If you have any hidden columns (or rows) and need to autofit them, you need to unhide them first.

3. Do Auto-fitting Columns Affect The Cell Contents Or Formatting?

No, it just changes the column width. The content and formats remain the same as it was before autofitting.

Download Practice Workbook

AutoFit All Columns.xlsm

<< Go Back to Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!