Bulk Column Width Setter: Standardize Every Column on Every Sheet in One Click

TL;DR: You open a 20-tab workbook where every sheet has different column widths — Sched-A columns are 8.43 (Excel default), Sched-B columns are 22.57, Sched-C varies from 3.0 to 45.0. This macro sets every column on every visible sheet to the same width, or auto-fits everything if you prefer. One InputBox, zero code editing.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook where column widths are inconsistent — any inherited file, any multi-sheet workpaper

What the macro does:

• Prompts you once for a column width (enter any number between 0 and 100, or leave blank / enter 0 for auto-fit)
• Loops every visible sheet and sets all columns to that width
• Reports how many sheets were adjusted
• Tells you whether the target width was applied or skipped because it matched

Limitations:

• Only affects visible sheets — hidden sheets are skipped. Unhide them first if you need them standardized too (see Unhide All Sheets)
• Column widths are in characters (Excel’s standard unit). Width 8.43 is the Excel default for a new sheet. Width 12 shows about 12 characters of Calibri 11pt
• Auto-fit (width 0) bases the width on the longest visible cell in each column. A single cell with a 200-character formula will produce a very wide column
• Works on ThisWorkbook — the workbook containing the macro. Store it in your Personal Macro Workbook (PERSONAL.XLSB) to use it on any file you open
• The change is undoable with Ctrl+Z — but only for the active sheet. Run on one sheet at a time if you need per-sheet undo capability

#The Macro

Option Explicit

Sub BulkSetColumnWidth()
    ' ── Bulk Column Width Setter ───────────────────────
    ' Asks for a column width (0 or blank = auto-fit)
    ' and applies it to every column on every visible
    ' sheet. Reports how many sheets were changed.
    '
    ' Zero input after the initial prompt.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim wStr As String
    Dim wVal As Double
    Dim changed As Long
    Dim skipped As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Get target width ───────────────────────────────
    wStr = InputBox("Set column width for all visible sheets:" & _
                    vbCrLf & "(number = fixed width, 0 or blank = auto-fit)", _
                    "Bulk Column Width", "0")
    If wStr = "" Then wStr = "0"
    If Not IsNumeric(wStr) Then
        MsgBox "Please enter a number (e.g., 12 or 0 for auto-fit).", _
               vbExclamation, "Invalid Input"
        GoTo CleanUp
    End If
    wVal = CDbl(wStr)

    ' ── Apply to every visible sheet ───────────────────
    changed = 0
    skipped = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            If wVal > 0 Then
                ws.Cells.ColumnWidth = wVal
            Else
                ws.Cells.Columns.AutoFit
            End If
            changed = changed + 1
        Else
            skipped = skipped + 1
        End If
    Next ws

    ' ── Report results ─────────────────────────────────
    If wVal > 0 Then
        MsgBox "Set column width to " & wVal & " on " & changed & _
               " sheet(s)." & IIf(skipped > 0, vbCrLf & skipped & _
               " hidden sheet(s) skipped.", ""), _
               vbInformation, "Done"
    Else
        MsgBox "Auto-fitted columns on " & changed & " sheet(s)." & _
               IIf(skipped > 0, vbCrLf & skipped & _
               " hidden sheet(s) skipped.", ""), _
               vbInformation, "Done"
    End If

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#One InputBox, two paths

The macro asks for one number. If you enter 12, every visible sheet gets 12 character-width columns. If you enter 0 or leave the box blank, every sheet gets AutoFit — each column expands or shrinks to fit its longest visible cell. Both paths are one click after the prompt.

If wVal > 0 Then
    ws.Cells.ColumnWidth = wVal       ' Fixed width
Else
    ws.Cells.Columns.AutoFit          ' Smart fit to content
End If

The 0 shortcut means you don’t need two separate macros. The InputBox label tells the user what 0 does:

Set column width for all visible sheets:
(number = fixed width, 0 or blank = auto-fit)

#Hidden sheets are skipped — not forgotten

A hidden sheet is hidden for a reason — maybe it contains lookup tables, pivot caches, or interim calculations the preparer doesn’t want visible. The macro checks ws.Visible = xlSheetVisible and skips anything else. The final message box tells you how many were skipped, so you know those sheets weren’t missed — they were intentionally left alone.

If ws.Visible = xlSheetVisible Then
    ' ... apply width ...
Else
    skipped = skipped + 1
End If

This also protects VeryHidden sheets (xlSheetVeryHidden), which the prior preparer intentionally concealed from the Unhide dialog. If you want those visible, run the VeryHidden Sheet Revealer first.

#Fixed width vs. auto-fit — when to use each

Fixed width (e.g., 12) is best when every sheet in the workpaper has the same structure. If your firm uses a consistent layout — column A = account number (12 characters), column B = account description (40 characters), column C = CY balance (14 characters) — setting everything to a uniform width makes each sheet feel familiar the instant you switch tabs. No visual recalibration.

Auto-fit (0) is best when sheets have different structures. A trial balance, a fixed asset schedule, and a journal entry log have different data in different columns. Auto-fit makes each sheet individually readable without forcing a one-size-fits-all width. A column of two-letter state codes doesn’t need the same width as a column of full account descriptions.

#Why no Calculation toggle

The macro changes column widths. It doesn’t write to cells, doesn’t adjust formulas, doesn’t modify any data. Changing column width does not trigger a recalculation cascade. Only Application.ScreenUpdating = False is needed — to prevent Excel from redrawing each sheet as it adjusts columns, which causes visible flickering across dozens of tabs.

#Why hidden sheets are skipped silently (but counted)

Other macros on this blog itemize which sheets were changed vs. skipped. For a bulk column-width operation, the message box just gives you the totals:

Set column width to 12 on 18 sheet(s).
2 hidden sheet(s) skipped.

That’s because hidden sheets aren’t an anomaly to investigate — they’re a deliberate choice by the workbook owner. If you expected 20 sheets and got 18, check the Unhide dialog to see the 2 hidden ones. The message isn’t a warning; it’s a reminder.

#Adapt It