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.

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.

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

Autofit Columns Excel for the Whole Worksheet

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.

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.

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

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.

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.

Excel shall increase or decrease the column width as required.

Using a Keyboard 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

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.

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

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.

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

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.

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.

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.

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.

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

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.