3 ways to autofit columns & rows in Excel

Are your columns and rows a bit of a mess?

Too wide, too narrow, too tall, or too short for the data they hold?

Here are three ways to optimise their size:

Mouse method

Click the top-left corner of the worksheet to select everything, then double-click between any column (or row) to AutoFit all at once.

Keyboard method

Press Ctrl + A twice to select the entire worksheet. Then use:

Alt + H + O + I to optimise columns

Alt + H + O + A to optimise rows

Automatic method

If you want AutoFit to resize columns and rows as you edit, press Alt + F11 to open the VBA Editor, and then drop this in the ThisWorkbook object:

Private Sub Workbook_SheetChange( _
    ByVal Sh As Object, _
    ByVal Target As Range)
    On Error Resume Next
    Sh.UsedRange.Columns.AutoFit
    Sh.UsedRange.Rows.AutoFit
End Sub

Final thoughts

It’s a shame there isn’t a native toggle option in the ribbon that optimises column and row size automatically.

For example, a fixed column width is fine for values of predictable character length. However, when they’re unpredictable, this can result in text being cut off and numeric values displaying hashtags if they’re longer than what the cell can support.

Would you like to see this feature arrive?

Watch the video