AutoFit All Columns on All Sheets

What it does

After a data refresh, columns get truncated. Long values display as ### or get clipped. The fix is Format > AutoFit Column Width, but Excel applies it only to the active sheet. On a 25-tab workbook, that's 25 trips through the menu. The macro skips the menu loop. Run it once and every visible sheet has its used range autofitted column-wise. Hidden sheets are skipped on purpose.

Real-world example

Picture a financial analyst at a multi-product SaaS company who maintains the company's monthly KPI workbook. The workbook has 18 tabs: one per product line for current-period KPIs, a few summary tabs, and an "Inputs" tab where the data refresh from the data warehouse lands. After every refresh the column widths on a handful of tabs go out of whack because product names that grew (a new SKU was added) push past the previous column width. The CEO opens the workbook on her phone the morning of the executive meeting; if the columns are clipped, she pings the analyst.

His current routine: after refresh, click each tab, Ctrl+A, double-click any column boundary in the column header to autofit. About 30 seconds per tab. With 18 tabs that's 9 minutes of clicking that he sometimes shortcuts by only autofitting the tabs he thinks the CEO will look at, which is usually wrong.

With this macro he runs the tool right after refresh, and all 18 tabs get column-autofitted in about 2 seconds. The popup confirms the count. The CEO opens the workbook, sees clean columns, doesn't ping. Total elapsed: 5 seconds.

On an 18-tab monthly workbook, expect about 9 minutes saved per refresh, plus one fewer "the columns are clipped" email per month. Across a year that's roughly 2 hours back and a small but ongoing win in workbook polish.

How to use

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

1. Open the workbook. The macro operates on the active workbook's visible sheets.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick AutofitAllColumnsAllSheets, click Run.
3. The macro autofits every column of every visible sheet and reports the count.

FAQ

Does it touch hidden sheets?

No. The macro skips any sheet where Visible <> xlSheetVisible. If you want hidden sheets autofitted too, unhide them, run the macro, then re-hide. The reasoning: most hidden sheets are calculation engines or staging areas that don't need to be readable, so autofitting them just changes column widths nobody will see.

Will it autofit columns that are deliberately set to a fixed width?

Yes. AutoFit ignores any explicit width setting and resizes to fit content. If you have a column intentionally set to (say) 12 characters wide for visual consistency, this macro will resize it. There's no way for the macro to tell the difference between "this column is wide because someone made it wide" and "this column is wide by accident". If you have a sheet with intentional fixed widths, omit it from the visible-sheets path (move it to a separate workbook).

What about row heights?

The macro only autofits column widths. Row heights are left as is. If you want row autofit too, edit the .bas source: change ws.UsedRange.Columns.AutoFit to ws.UsedRange.Columns.AutoFit: ws.UsedRange.Rows.AutoFit. We may add a "rows and columns" mode in a future version.

What if a sheet has merged cells?

Merged cells can confuse AutoFit (Excel's built-in behavior, not a macro limitation). The result is that columns containing merged cells may not autofit cleanly. Run Unmerge Cells and Fill Down first to flatten merges, then run AutoFit. The macro itself runs without error on merged cells; the cells just don't resize as expected.

Can I undo it?

Ctrl/Cmd + Z typically does NOT undo AutoFit reliably for multiple sheets at once. To restore previous column widths, you'd have to set them manually. Save a copy before running if you have specific column widths you don't want to lose. The macro runs entirely offline. Free, hosted on GitHub, source visible.