5 Ways to Autofit Columns in Microsoft Excel

Learn how to autofit columns in Excel with real examples, easy-to-follow methods, and original illustrations.

Often you find that a few columns of your Excel worksheet are so narrow that you can’t see the whole data in each cell. You might make a mistake in data analysis if you’re unable to see the whole data.

Also, if you’re presenting a report to an audience, narrow columns might reduce the readability of the report. Not to mention, the printing of cell contents in your worksheets also depends on the visibility of data in columns.

In all such cases, you must autofit columns in Excel to get a full picture of the data, enhance worksheet readability, and consistency.

In this Excel tutorial, I shall show you all popular methods to resize worksheet columns to fit a certain width automatically.

Autofit Columns Excel Using a Double-Click

When you need to autofit columns, it means using certain methods to increase or reduce the column width according to the content of the cells in that column. The autofit feature applies to the lengthiest text string or numerical in all the cells of the target column.

Taking cursor to the right side border
Taking the cursor to the right-side border

Navigate to the specific column on your dataset in which you can see the contents of the cells partially.

Take the mouse cursor to the right-side border of the column header text as shown in the above image.

Double-click on column text border
Double-click on the column text border

Now, simply double-click to autofit the column according to the cell that has the widest content.

Autofit Columns Excel for the Whole Worksheet

Sample dataset
Sample dataset

In the above dataset, I need to autofit four columns. I can easily do this by double-clicking four times on the right-side borders of the columns. What if your worksheet contains hundreds of columns that are narrower than they should be?

In such a challenging worksheet with a lot of narrow columns, you can use this method.

Click select all in a worksheet
Click Select All in a worksheet

Firstly, go to the target worksheet and click on the Select All button located in the top left corner of the worksheet border and to the left of the column header text A. It’s typically represented by a small gray square located at the intersection of the row and column headers.

You’ve selected all the columns and rows of the worksheet by now.

Expand column width
Expand column width

Double-click the right-side border of any column of the worksheet that needs resizing.

Autofit Columns Excel for all columns
Autofit Columns Excel for all columns

Doing so shall autofit all the columns in the Excel spreadsheet.

Autofit Columns Excel Using the Format Tool

The Format tool in Excel allows you to perform various customizations on rows and columns of a worksheet. One such task is autofitting a column according to its cell contents.

AutoFit Column Width
AutoFit Column Width

Firstly, select one or more columns in your spreadsheet that need resizing.

Navigate to the Cells commands block in the Home tab and click on the Format drop-down arrow.

In the context menu that opens, click on the AutoFit Column Width option.

Autofit column using Format tool
Autofit column using Format tool

Excel shall increase or decrease the column width as required.

Using a Keyboard Shortcut

Autofit column width using shortcut
Autofit column width using a shortcut

If you wish to apply the AutoFit Column Width to one or many columns in an Excel worksheet, you can select the target columns, and press Alt + H + O + I.

Autofit Columns Excel Using Excel VBA

If you need to autofit columns in a large worksheet containing many narrow columns that don’t show the full-length content of cells, it’s better to use this Excel VBA-based method rather than the manual ones mentioned so far.

You can use the Excel VBA method to autofit all the columns or a few columns by selecting those by typing column header texts.

Here’s the VBA script that you can use:

Sub AutoFitColumns()
    Dim ws As Worksheet
    Dim rng As Range
    Dim colStr As String
    Dim colArray As Variant
    Dim i As Integer

    ' Set the worksheet
    Set ws = ActiveSheet

    ' Ask the user for input
    colStr = InputBox("Enter column letters to autofit (e.g., A, B, C, D) or type 'all' to autofit all columns.", "Autofit Columns")

    ' Check if the user wants to autofit all columns
    If LCase(colStr) = "all" Then
        ws.Cells.EntireColumn.AutoFit
    Else
        ' Split the input into an array
        colArray = Split(colStr, ",")

        ' Loop through the array and autofit each column
        For i = LBound(colArray) To UBound(colArray)
            ws.Columns(Trim(colArray(i))).EntireColumn.AutoFit
        Next i
    End If

    MsgBox "Columns have been autofitted.", vbInformation, "Task Completed"
End Sub
Excel Macro to autofit column
Excel Macro to autofit column

Go to the target worksheet and press Alt + F11 to launch the Excel VBA Editor tool.

There, click the Insert button on the toolbar. A context menu shall open. There, click the Module option.

A blank module shall pop up. There, copy and paste the above script.

Click the Save button on the toolbar.

Click No
Click No

A Microsoft Excel dialog shall open. There, click No to open the Save As dialog.

Save as XLSM
Save as XLSM

In Save As, click the Save as type drop-down and choose XLSM file type. Click Save to create a macro-enabled copy of the original workbook. Excel shall save the script within the newly created XLSM file.

Now, close the Excel VBA Editor.

Run macro
Run macro

Press Alt + F8 to launch the Macro dialog box. There, select the AutoFitColumns macro and hit Run to execute the script.

Choose column text
Choose column text

You shall now see an input box where you must type All to autofit all columns or type column header texts separated by commas, like A, B, C, D, etc.

Autofit Excel Columns using VBA
Autofit Excel Columns using VBA

Hit the OK button to programmatically autofit columns using Excel VBA.

⚠️ Warning: When you introduce any modifications in your worksheet using Excel VBA or Office Scripts, the Excel Undo feature won’t work. So, create a copy of the worksheet or workbook before using Excel VBA and Office Scripts.

Suppose, you’d like to create this macro to be available for all the workbooks you work on your Excel desktop app. In that case, you must create the VBA macro in a Personal Macro Workbook.

Start macro recording
Start macro recording

To create a Personal Macro Workbook, navigate to any blank worksheet and click on the Record Macro command button inside the Code block of the Developer tab.

If you don’t have the Developer tab in your Excel app or don’t know how to enable this feature, check out this Excel tutorial:

📒 Read More: Add the Developer Tab in Microsoft Excel

The Record Macro dialog shall open. There, type anything in the Macro name field that you can remember. Since this is a dummy macro recording, you might want to delete it later.

Similarly, enter a shortcut for the macro as well in the Shortcut key field. Now, click the Store macro in drop-down and choose the Personal Macro Workbook option. Click OK to start the macro recording event.

Click the Stop Recording button to close the macro recording and create a VBA script in the Personal Macro Workbook file.

Creating global macro in personal workbook
Creating global macro in personal workbook

Now, press Alt + F11 to launch the Excel VBA Editor. There, you shall find the newly-created Personal Macro Workbook in the left-side navigation panel.

Select the Personal Macro Workbook project and repeat the steps mentioned earlier to create the AutoFit Column Width VBA macro for this project.

From now on, you can access the AutoFitColumns macro in any workbook you open in your Excel desktop app.

Autofit Columns Excel Using Office Scripts

Another AutoFit Column Width function automation you can use is through Office Scripts. If you can access the Automate tab on the Excel desktop or web app, you can try this method.

Create a new Office Script
Create a new Office Script

Click on the New Script command button inside the Scripting Tools of the Automate tab in Excel.

Copy and paste the following Office Script inside the Code Editor console of the Excel app. It should be on the right-side border.

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet
    let worksheet = workbook.getActiveWorksheet();

    // Get the current selection
    let selectedRange = workbook.getSelectedRange();

    // Apply autofit to the columns in the selected range
    selectedRange.getFormat().autofitColumns();
}

Click the Save script button to save the Office Script for future use.

Select columns and run script
Select columns and run script

Select the target column or columns by clicking on the column text like A, B, etc.

Hit the Run button on the Code Editor console to execute the script.

Autofit column using Office Scripts
Autofit column using Office Scripts

Excel shall apply the AutoFit Column Width function to the selected columns.

📝 Note: Office Scripts is only available to you if you’ve bought Microsoft 365 Business Standard or a better subscription. Also, if you’re using Microsoft 365 as an employee or student of a business organization or school, the IT admin must enable the feature for your account.

Conclusions

Now you know how to autofit a column in Excel. This Excel skill allows you to adjust column width automatically to show all the contents of its cells. Alternatively, you can use it to reduce the column width and save space on the worksheet if there are unnecessarily wide columns.

Did the article help you in mastering column width customizations in Excel? If yes, comment below. If you’ve found any issue in this Excel tutorial that needs rework or you know a better method than the ones I’ve demonstrated above, write a few lines in the comment box.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃