Formula Reporter: Audit Every Formula Without Touching a Single Cell

TL;DR: Before you modify a complex workpaper, run this macro. It scans every formula — VLOOKUPs, SUMIFs, cross-tab references — and dumps them all into a new “Formula-Report” sheet with clickable cell addresses. Search, sort, print, review. Your original data is never modified.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook containing formulas — the macro works on any workbook with formulas
• No designated tabs required — the macro asks whether to scan all sheets or just the active one

What the macro does NOT do:

• It does not modify any cells on your original sheets. All output goes to a new “Formula-Report” tab.
• It does not edit, fix, or correct formulas. It reports them as-is for your review.
• It does not scan hidden or protected sheets that you can’t access.

Limitations:

• Formula text is stored as a string (with a leading apostrophe to prevent Excel from executing it). Paste a formula back into a cell and remove the apostrophe to restore it.
• Blank cells and text cells are skipped — only formulas are reported.
• Extremely long formulas (> 1,000 characters) will appear truncated in the report column — widen column C manually.

#The Macro

Option Explicit

Sub FormulaReporter()
    ' ── Formula Reporter ──────────────────────────────
    ' Scans every formula on the active sheet (or all
    ' sheets) and compiles them into a searchable report
    ' on a new "Formula-Report" sheet. Cell addresses
    ' are hyperlinked — one click jumps back to the
    ' source. Does NOT modify your original data.
    '
    ' Output: Sheet name, cell address (clickable), and
    '         formula text for every formula found.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Formula-Report"

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, wsOut As Worksheet
    Dim cell As Range, formulaRng As Range
    Dim outRow As Long, totalFormulas As Long
    Dim scanAll As VbMsgBoxResult
    Dim sheetCount As Long

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

    ' ── Ask scope: all sheets or active sheet only ─────
    scanAll = MsgBox( _
        "Scan all sheets or active sheet only?" & vbCrLf & _
        vbCrLf & _
        "  Yes = All sheets" & vbCrLf & _
        "  No  = Active sheet only", _
        vbYesNoCancel + vbQuestion, "Formula Reporter")

    If scanAll = vbCancel Then GoTo CleanUp

    ' ── Create/refresh report sheet ────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set wsOut = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    wsOut.Name = OUT_SHEET

    ' ── Write headers ──────────────────────────────────
    wsOut.Range("A1:C1").Value = Array("Sheet", "Cell", "Formula")
    wsOut.Range("A1:C1").Font.Bold = True
    wsOut.Range("A1:C1").Interior.Color = RGB(50, 50, 50)
    wsOut.Range("A1:C1").Font.Color = vbWhite

    ' ── Scan sheets for formulas ───────────────────────
    outRow = 2
    totalFormulas = 0
    sheetCount = 0

    For Each ws In ThisWorkbook.Worksheets
        ' Single-sheet mode: skip all sheets except the active one
        If scanAll = vbNo And ws.Name <> ActiveSheet.Name Then GoTo NextWS
        If ws.Name = OUT_SHEET Then GoTo NextWS

        ' ── Get all formula cells on this sheet ────────
        On Error Resume Next
        Set formulaRng = ws.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo CleanUp

        ' No formulas on this sheet — skip it
        If formulaRng Is Nothing Then GoTo NextWS

        sheetCount = sheetCount + 1

        For Each cell In formulaRng
            ' Sheet name
            wsOut.Cells(outRow, 1).Value = ws.Name

            ' Cell address (clickable hyperlink back to the source)
            wsOut.Hyperlinks.Add _
                Anchor:=wsOut.Cells(outRow, 2), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!" & _
                    cell.Address(False, False), _
                TextToDisplay:=cell.Address(False, False)

            ' Formula text — leading apostrophe prevents re-execution
            wsOut.Cells(outRow, 3).Value = "'" & cell.Formula

            totalFormulas = totalFormulas + 1
            outRow = outRow + 1
        Next cell

NextWS:
    Next ws

    ' ── Handle zero formulas ───────────────────────────
    If totalFormulas = 0 Then
        MsgBox "No formulas found.", vbInformation
        Application.DisplayAlerts = False
        wsOut.Delete
        Application.DisplayAlerts = True
        GoTo CleanUp
    End If

    ' ── Format report ──────────────────────────────────
    With wsOut
        .Columns("A").ColumnWidth = 18
        .Columns("B").ColumnWidth = 10
        .Columns("C").ColumnWidth = 90
        .Range("A1").Select
        ActiveWindow.FreezePanes = True
    End With

    ' ── Summary ────────────────────────────────────────
    MsgBox "Found " & totalFormulas & " formula(s) across " & _
           sheetCount & " sheet(s)." & vbCrLf & vbCrLf & _
           "Report created on '" & OUT_SHEET & "' sheet." & _
           vbCrLf & "Click any cell to jump back to its source.", _
           vbInformation, "Formula Reporter"

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 MsgBox replaces the InputBox for clarity

Most macros on this blog use InputBox for a single value — a date, a password, a column letter. But for a binary choice like “all sheets or active sheet?,” a MsgBox with Yes/No/Cancel is clearer and faster:

scanAll = MsgBox( _
    "Scan all sheets or active sheet only?" & vbCrLf & _
    vbCrLf & _
    "  Yes = All sheets" & vbCrLf & _
    "  No  = Active sheet only", _
    vbYesNoCancel + vbQuestion, "Formula Reporter")

The user reads the question once, clicks a button, and the macro proceeds. Cancel aborts gracefully — no cleanup needed because nothing was created yet.

#SpecialCells finds formulas without looping every cell

This is the single most important design decision in the macro. Instead of iterating through every cell on every sheet (which would freeze Excel on a large workbook), the macro uses SpecialCells(xlCellTypeFormulas):

On Error Resume Next
Set formulaRng = ws.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo CleanUp

This returns a Range object containing only the cells with formulas — in a single call, regardless of how many rows or columns the sheet has. A 500-row sheet with 12 formulas processes in under a second because the macro only touches those 12 cells. The On Error Resume Next wrapper handles the case where a sheet has no formulas at all — SpecialCells raises an error instead of returning Nothing, and this catches it.

#Leading apostrophe prevents formula re-execution

Every formula is written to the report with a leading apostrophe:

wsOut.Cells(outRow, 3).Value = "'" & cell.Formula

Without the apostrophe, Excel would try to execute each formula in the report sheet — producing #REF! errors because the referenced cells don’t exist on the report tab. The apostrophe tells Excel to treat the content as text. If you need to paste a formula back into a cell for testing, just remove the apostrophe.

#Refreshing the report replaces the old one

The macro deletes the existing Formula-Report sheet before creating a new one:

Application.DisplayAlerts = False
ThisWorkbook.Worksheets(OUT_SHEET).Delete
Application.DisplayAlerts = True

This means you can run the macro multiple times — after adding formulas, changing the scope, or just to get a fresh view — and each run replaces the previous report. No “a sheet named Formula-Report already exists” error. DisplayAlerts = False suppresses the “Are you sure?” prompt.

#The report sheet is placed at the end

Set wsOut = ThisWorkbook.Worksheets.Add( _
    After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))

The hard-coded detector puts its output at the end for the same reason: this is a review tool, not a permanent resident. Putting it last keeps it out of the way while you flip through your actual workpaper tabs.

#Empty report = auto-deleted

If zero formulas are found, the macro deletes the empty report sheet and exits:

If totalFormulas = 0 Then
    MsgBox "No formulas found.", vbInformation
    Application.DisplayAlerts = False
    wsOut.Delete
    Application.DisplayAlerts = True
    GoTo CleanUp
End If

No orphaned empty sheet. The user gets a clean “No formulas found” message and the workbook returns to exactly how it was.

#Adapt It