Zero to Blank: Hide Every Zero on Every Sheet in One Click

TL;DR: Your trial balance has 300 rows but 140 of them are zero. The depreciation schedule has 12 columns but 8 are zero-activity. Running this macro makes every zero disappear — all sheets, one click. Run it again and they all come back. Cleaner workpapers, faster review, no permanent changes.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with zeros you want to hide — trial balances, depreciation schedules, apportionment workpapers, anything where $0.00 is noise, not information

What the macro does:

• Applies a custom number format (0;-0;;@) to all numeric cells on every visible sheet, making zeros display as blank cells
• Stores its state in the workbook so running it again restores zeros instead
• Leaves text cells, dates, and hidden sheets completely untouched
• Does not delete or modify any data — zeros are still there, just not displayed

Limitations:

• The format change replaces existing number formatting with a simple numeric format (0;-0;0 on restore). If your cells use currency symbols, comma separators, or decimal places, those are lost during the cycle. See Adapt It for how to customize the format string
• Only touches visible sheets — hidden sheets are skipped. If you un-hide a sheet later, it won’t have the zero-blanking format applied
• Zeros in text-formatted cells (cells with number format @) are left alone — if the cell is formatted as text, Excel treats 0 as the character “0”, not the number zero
• The state tracker (ZeroToBlank_Active custom document property) is stored in the workbook. If you close without saving, the state resets

#The Macro

Option Explicit

Sub ZeroToBlank()
    ' ── Zero to Blank ─────────────────────────────────
    ' Toggles zero-value display across all visible
    ' sheets. First run hides zeros (displays as blank).
    ' Second run restores them. State tracked via a
    ' custom document property so the macro knows which
    ' direction to go.
    '
    ' Non-destructive — no data is changed, only the
    ' number format applied to the cells.
    ' ────────────────────────────────────────────────────

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

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim numCells As Range
    Dim hiding As Boolean
    Dim response As VbMsgBoxResult
    Dim sheetCount As Long
    Dim cellCount As Long
    Const PROP_NAME As String = "ZeroToBlank_Active"

    ' ── Determine current state ────────────────────────
    hiding = False
    On Error Resume Next
    hiding = (ThisWorkbook.CustomDocumentProperties(PROP_NAME) = True)
    On Error GoTo CleanUp

    ' ── Ask the user ───────────────────────────────────
    If hiding Then
        response = MsgBox( _
            "Zeros are currently hidden across all sheets." & vbCrLf & _
            vbCrLf & _
            "Yes = Restore zeros (show them again)" & vbCrLf & _
            "No  = Leave hidden" & vbCrLf & _
            "Cancel = Quit with no changes", _
            vbYesNoCancel + vbQuestion, "Zero to Blank")

        If response = vbNo Or response = vbCancel Then
            GoTo CleanUp
        End If
    Else
        response = MsgBox( _
            "Hide all zero values on every visible sheet?" & vbCrLf & _
            vbCrLf & _
            "Zeros will display as blank cells. Positive and negative " & _
            "numbers are not affected. Run the macro again to restore.", _
            vbYesNo + vbQuestion, "Zero to Blank")

        If response = vbNo Then GoTo CleanUp
    End If

    ' ── Apply or restore format ────────────────────────
    sheetCount = 0
    cellCount = 0

    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible <> xlSheetVisible Then GoTo NextSheet

        Set numCells = Nothing
        On Error Resume Next
        Set numCells = ws.UsedRange.SpecialCells( _
            xlCellTypeConstants, xlNumbers)
        On Error GoTo CleanUp

        If Not numCells Is Nothing Then
            If hiding Then
                numCells.NumberFormat = "0;-0;0"
            Else
                numCells.NumberFormat = "0;-0;;@"
            End If
            cellCount = cellCount + numCells.Count
            sheetCount = sheetCount + 1
        End If

NextSheet:
    Next ws

    ' ── Update state tracker ───────────────────────────
    If hiding Then
        On Error Resume Next
        ThisWorkbook.CustomDocumentProperties(PROP_NAME).Delete
        On Error GoTo CleanUp
    Else
        On Error Resume Next
        ThisWorkbook.CustomDocumentProperties.Add _
            Name:=PROP_NAME, LinkToContent:=False, _
            Type:=msoPropertyTypeBoolean, Value:=True
        On Error GoTo CleanUp
    End If

    ' ── Report results ─────────────────────────────────
    If hiding Then
        MsgBox "Zeros restored on " & sheetCount & _
               " sheet(s) (" & cellCount & " cells).", _
               vbInformation, "Zero to Blank"
    Else
        MsgBox "Zeros hidden on " & sheetCount & _
               " sheet(s)." & vbCrLf & _
               "Run this macro again to restore them.", _
               vbInformation, "Zero to Blank"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

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

#How It Works

#The four-section number format

Excel’s custom number formats use up to four sections, separated by semicolons:

positive;negative;zero;text

This macro applies 0;-0;;@, which reads as:

• 0 — positive numbers: show as integer (no decimals)
• -0 — negative numbers: show with minus sign, no decimals
• (empty) — zeros: show nothing (blank cell)
• @ — text: show as-is

The third section is the zero section. By leaving it empty, Excel displays nothing when the cell evaluates to zero — exactly what we want. The fourth section (@) ensures text cells that happen to be in the selected range aren’t affected.

On restore, the macro applies 0;-0;0, which is identical except the zero section contains 0 — so zeros display as 0 again.

#SpecialCells — don’t loop through every cell

Set numCells = ws.UsedRange.SpecialCells( _
    xlCellTypeConstants, xlNumbers)

A naive approach would loop through every cell in UsedRange with a For Each cell loop and an If IsNumeric(cell.Value) Then. That works on a 50-row sheet. On a 300-row trial balance across 15 sheets, it’s 4,500 cell checks times two type-checks per cell — slow enough that the user notices.

SpecialCells(xlCellTypeConstants, xlNumbers) returns only the cells that contain hard-coded numbers. No looping, no IsNumeric checks — Excel’s internal index does the filtering in one operation. numCells.NumberFormat = "..." then applies the format to every cell in the result range at once.

The macro uses On Error Resume Next before the SpecialCells call because SpecialCells raises an error if no cells match the type filter. A sheet with only text (like a Notes sheet) would crash the macro without the error guard.

#Why the state tracker matters

Without a state tracker, the macro doesn’t know whether zeros are currently hidden or shown. The user has to remember. Did they run it last week? Did someone else run it? The confirm dialog would need to ask “Hide or restore?” every time, which is friction.

A custom document property (ZeroToBlank_Active) solves this:

On Error Resume Next
ThisWorkbook.CustomDocumentProperties.Add _
    Name:=PROP_NAME, LinkToContent:=False, _
    Type:=msoPropertyTypeBoolean, Value:=True
On Error GoTo CleanUp

Custom document properties are key-value pairs stored inside the workbook file. They survive save/close/open cycles. When the macro hides zeros, it creates the property with value True. When it restores, it deletes the property. Next time the macro runs, it checks if the property exists — if yes, zeros are hidden, so offer “restore.” If no, zeros are visible, so offer “hide.”

The On Error Resume Next wrapper on the Add call handles the case where the property already exists (shouldn’t happen, but a crashed prior run might leave it behind). The Delete call uses the same guard — if the property doesn’t exist, the delete is silently skipped.

#ScreenUpdating and Calculation — both are necessary here

This macro writes to UsedRange on every visible sheet. On a 15-sheet workbook, that’s up to 15 sheet redraws and 15 recalculation passes. ScreenUpdating = False prevents the flicker; Calculation = xlCalculationManual prevents Excel from recalculating every formula in the workbook after each sheet’s format change.

Both are restored in the CleanUp: label, which runs even if the macro crashes mid-way thanks to On Error GoTo CleanUp.

#Why hidden sheets are skipped

A hidden sheet is hidden for a reason — it might contain lookup tables, prior year data, or macro configuration the user doesn’t need to see. Applying 0;-0;;@ to a hidden validation-lists sheet won’t cause errors, but it also won’t help anyone. Skipping hidden sheets keeps the macro fast and focused on what the user is actually looking at.

If you later un-hide a sheet, its zeros will still display as zeros — it wasn’t touched. Run the macro again to pick it up.

#Adapt It