How to AutoFit All Columns in Excel (4 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

When you work with a large amount of data in Excel, sometimes you may find the column widths are not set optimally. However, you can overcome this unwanted situation if you know how to autofit all columns in Excel.

Resizing the width of every column in an Excel spreadsheet manually involves a tiring and time-consuming procedure. And using the “Wrap text” command increases the row height to make texts of a cell visible.

But. AutoFit adjusts the column width, keeping the row height constant.

Do you want to get a quick illustration of what we are going to do in this article? Check out the image below.

Overview of how to autofit all columns in Excel


About Columns in Excel

Column width in Excel refers to the horizontal measurement of a column, which determines the amount of data that can be displayed in the column.

By default, the column width is 8.43 characters wide, but you can adjust the width of any column to fit the content of the cells within it.

Default column width in Excel


Sample dataset

The image of the spreadsheet above contains some data where many of them cross the edge of several cells. When you have this type of data in your Excel sheet, you must be wondering how to autofit all the columns at a time.

We will try to give several solutions for this. This section will cover 5 different ways to autofit all columns in Excel.


1. Double Clicking Column Header to AutoFit Columns

By implementing this method, Excel will automatically adjust the size of the cells, either vertically or horizontally, to fit the largest text that a column or row contains. But if we do that with all the cells selected, we can autofit all columns of an Excel spreadsheet.

  • Select all cells by selecting the triangle icon where column headers and row headers meet on the top left of the spreadsheet.

Click to select all columns

  • This will select all the columns in your spreadsheet.
  • Put your cursor in between any column headers. Thus, your cursor icon will change like the following figure.

Double click between column header

  • Double-click the mouse.

Columns autofitted

That’s it. That is all you have to do to apply this method to autofit all columns in Excel at once.


2. Applying the “AutoFit Column Width” Feature from the Home Tab

There is a feature in the Excel ribbon that offers to adjust column widths and row sizes. We can use that to autofit all columns in Excel.

  • Select all the columns >> go to the Home tab >> click Format under Cells group >> select AutoFit Column Width from the drop-down list.

Use of autofit column width feature

  • This feature will autofit all the columns in the spreadsheet.

All columns in Excel autofitted


3. AutoFit All Columns with Keyboard Shortcut

There is a simple keyboard shortcut to autofit columns in Excel.

Just press CTRL+A >> ALT+H >> O >> I and Excel will autofit the columns.

Keyboard shortcut to autofit column

  • First, press CTRL+A. That selects all columns.
  • Then Press the ALT key, and that enables the shortcut keys to be visible for all features.
  • Next press H >> O >> I consecutively.

Shortcut keys after pressing ALT

As a result, Excel will have all the columns in the worksheet.


4. Applying VBA Code to AutoFit Multiple Columns

VBA code is also available to autofit all the columns in your Excel sheet. I just, have to use the EntireColumn and Autofit properties of VBA.

  • Press ALT+F11 to activate the Visual Basic Editor window.
  • From the toolbar options of the VBA window, click Insert >> select Module.

Insert Module window

  • Now insert your VBA code in the Module window.

VBA code to autofit column

Code:

Sub AutoFitAllColumns()
    Cells.EntireColumn.AutoFit
End Sub
  • Click Run on the toolbar option to execute the code.
  • The code will make all the columns of your worksheet autofit with the text content.

Column autofitted with code execution


What Is the Alternative to AutoFit All Columns in Excel (Auto Wrap Text)

You can use the “Format Cells” dialog box to adjust your columns. But this method incorporates the “Wrap Text” command. So, it increases the row height first to make the cell value visible. In the end, it adjusts the column width slightly.

  • Select all the columns >> Right-click on one of the selected columns >> choose “Format Cells” from the menu that appears.

Format cells option in menu context

  • In the Format Cells dialog box, click on the Alignment tab >> check the Wrap text checkbox under the Text control section >> click OK.

Format cells dialog box

  • This will make the data visible like the image below. The “Wrap text” checkbox ensures that any text that is too long to fit in a cell will wrap to the next line, allowing the column to be sized appropriately.

Wrap text enabled

  • Double-click on the boundary between the column headers to autofit the column width based on the contents of the cells.

Column width adjusted


How to Manually Change Column Width in Excel

The default width of a column in Excel is 8.43 units. But on the demand, you can change the column width. Just put the cursor in between column headers and hold & drag the mouse to increase or decrease the column width.

Changing column width

And when you have a requirement to fit the width to a particular unit,

  • Select any cell in that column >> go to the Home tab >> select Format under Cells group >> click Column width.

Column width option

  • Put the desired unit in the Column Width box to change the width according to your needs.

Column width dialog box


How to Use AutoFit Feature in Merged Cells in Excel

The autofit feature is not workable for merged cells.

Autofit column with merged cell

If want to autofit a column containing a merged cell, Excel will autofit the column based on the largest cell values of that column, but it will have no effect on the merged cell.

Column including merged cell


Why Is AutoFit Not Working in Excel?

  • 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”.

Frequently Asked Questions

1. Can I autofit a specific column in Excel?

Yes, you can. Select the specific column only and use the AutoFit feature.

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

3. How do I make sure that columns stay autofitted even after I add or delete data?

If you add or delete data to the cells within that column, the column width may no longer be optimal. In that case, you can use the “Wrap Text” feature stated in Method 4 of this article.


Takeaways from this Article

  • Double-clicking the column header is the simplest way to autofit a single column or multiple columns in Excel.
  • If you need to autofit columns frequently, using VBA code can automate the process and save you time in the long run.
  • The “Format Cells” dialog box gives you more control over column width adjustments, including the ability to set a specific width or adjust column width based on the contents of the cells.
  • Remember to save your work frequently to avoid losing any changes you make to your spreadsheet.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below.


Conclusion

Setting columns to autofit by text can be a time-saving technique for managing large amounts of data. You can make your worksheet more reader-friendly if you know how to autofit all columns in your Excel sheet based on the largest text content. Each of the methods stated in the article has specific advantages depending on the requirement of your data. Getting acquainted with multiple methods helps you to choose the best one suited for you. Don’t forget to leave a comment about which method you like the most.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo