How to AutoFit All Columns on Every Sheet at Once in Excel

The fastest way to autofit every column on every sheet

To autofit all columns on every sheet at once in Excel, you need a macro, because Excel has no command that does it. AutoFit works on one sheet at a time. You select a sheet, autofit its columns, move to the next tab, and repeat. On a 25-tab model that's 25 rounds of the same click.

A macro loops every visible sheet and runs AutoFit on each one in a single pass. Here's how the options stack up.

Method	Sheets covered	One click?
Double-click a column border	One sheet	No
Home > Format > AutoFit	One sheet	No
Free macro	Every visible sheet	Yes

TL;DR: Key takeaways

• Excel has no built-in button to autofit every sheet. AutoFit only ever touches the active sheet.
• On one sheet, press Ctrl + A, then double-click any column border to autofit all columns.
• To do a whole workbook, a free macro runs AutoFit on every visible tab in one pass and reports the count.
• The macro autofits columns only, not row heights, and it skips hidden sheets.
• AutoFit ignores merged cells, so a merged title won't stretch a column. That's Excel's rule, not a bug in the macro.

How to autofit columns on a single sheet

For one sheet, Excel's built-in AutoFit does the job in a second. The fastest route is the double-click trick.

1. Click the Select All corner (the triangle above row 1 and left of column A), or press Ctrl + A.
2. Move your mouse to any border between two column headers, so the cursor turns into a double-arrow.
3. Double-click that border.

Every column snaps to the width of its widest value. You can also use the ribbon: Home > Format > AutoFit Column Width. Both do the same thing, and Microsoft documents them in change the column width and row height.

That's fast for one sheet. The pain starts when the workbook has twenty of them.

The problem: Excel does this one sheet at a time

AutoFit is a per sheet action. There's no "apply to all sheets" checkbox, and no keyboard shortcut that reaches across tabs. Autofit the January sheet and February stays exactly as cramped as it was.

So for a multi-tab workbook, a monthly model, a report with a tab per branch, a workbook you inherited from someone who never touched column widths, you're stuck clicking through each sheet by hand. Select all, double-click, next tab, select all, double-click, next tab. It's the kind of mindless repetition that's easy to half-finish and leave three tabs looking ragged.

That's the job worth automating. Not because autofit is hard, but because doing it thirty times in a row is a waste of a good afternoon.

How to autofit all columns on every sheet at once

To autofit all columns on every sheet at once, run a macro that walks each tab and autofits it for you. Download AutoFit All Columns on All Sheets. It's a free .xlsm file with one macro. No signup, and it runs offline so your workbook stays on your machine.

First time running one of our macros? See How to run our tools for the 30-second setup.

1. Open the workbook you want to tidy up.
2. Press Alt + F8, pick AutofitAllColumnsAllSheets, and click Run.
3. The macro autofits every column on every visible sheet, then pops a message with the count.

Under the hood it's a short loop over the sheets. If you'd rather paste the code into your own workbook, here it is:

Sub AutofitAllColumnsAllSheets()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.UsedRange.Columns.AutoFit
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

It autofits the columns inside each sheet's used range, which is the block of cells that actually holds data. Turning off screen updating while it runs keeps a 40-tab workbook from flickering and makes it finish faster.

Example: a monthly model refresh

A financial analyst at a logistics firm in Chennai keeps a forecasting workbook with a tab per route, eighteen of them, plus a summary. Every month the data drops in from the ERP export, and the new values are wider than the columns. Numbers show as #####, text gets clipped, and the whole thing looks unfinished right when it's about to go to the finance head.

Fixing it by hand meant clicking into all eighteen tabs, selecting everything, and double-clicking a column border on each. About three minutes of clicking that had to happen every single month.

Now the analyst runs the macro after each refresh. Every tab gets its columns autofitted in one pass, the ##### cells resolve to real numbers, and the message confirms "Autofitted columns on 19 visible sheet(s)." Three minutes of clicking becomes about two seconds, and no tab ships looking half-baked.

What it touches: hidden sheets, rows, and merged cells

Three honest details, so there are no surprises.

It skips hidden sheets. The loop only autofits sheets that are visible. If a hidden tab needs it too, unhide the sheets first, then run the macro.

It autofits columns, not rows. The macro sets column widths to fit the content. It doesn't change row heights, so if you have wrapped text that needs taller rows, that's a separate step.

AutoFit ignores merged cells. This is a built-in Excel rule, not a quirk of the macro. A merged banner across the top of a sheet won't force its column wider, because Excel doesn't count merged cells when it sizes a column. If one column looks too narrow after running it, check for a merged cell driving the width you expected.

For more one-pass workbook jobs, freeze the top row on every sheet keeps your headers visible after the columns are tidy, and both sit inside the guide to Excel productivity macros.

FAQ

Can Excel autofit all sheets at once?

Not with a built-in command. AutoFit only ever applies to the active sheet, so you'd normally repeat it tab by tab. The one-step option is a macro that loops through every visible sheet and autofits its columns in a single run, then reports how many sheets it changed.

How do I autofit all columns on one sheet quickly?

Press Ctrl + A to select the whole sheet, then double-click any border between two column headers. Every column resizes to fit its widest value. You can also use Home > Format > AutoFit Column Width on the ribbon, which does the same thing.

Does the macro autofit rows too, or only columns?

Only columns. It sets each column to the width of its content across every visible sheet, but it leaves row heights alone. If you need taller rows for wrapped text, autofit the rows separately, since column autofit and row autofit are two different actions in Excel.

Will it autofit hidden sheets in the workbook?

No, it skips hidden sheets on purpose and only autofits visible ones. If you want a hidden tab included, unhide it first, then run the macro. When it finishes, the message tells you exactly how many visible sheets it touched, so you can confirm the count matches what you expected.

Why didn't one of my columns resize?

The usual cause is a merged cell. Excel's AutoFit does not size a column to fit merged cell contents, so a merged title or header won't stretch the column under it. Unmerge those cells, or set that column's width by hand. Every non-merged column still autofits normally.

What to do next

For a single sheet, press Ctrl + A and double-click a column border. For a whole workbook of tabs, don't click through each one by hand.

Download the free AutoFit All Columns on All Sheets macro and run it on your own workbook. It's the quickest way to autofit all columns on every sheet at once, skipping hidden tabs and reporting the count, so a multi-tab workbook looks consistent in one click instead of twenty.