IFERROR Mask Detector: Find the Errors Your Workpaper Is Hiding From You

TL;DR: IFERROR is the most dangerous function in Excel. It hides #REF!, #N/A, #VALUE!, and #DIV/0! behind whatever fallback you give it — and if that fallback is a blank string, you’ll never know the error exists. This macro finds every IFERROR and IFNA in your workbook, categorizes each one, and creates an audit report that flags the ones most likely masking real problems. The original data stays untouched.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with formulas — the macro scans every sheet
• No special setup required — just run it

Limitations:

• Does not detect errors inside user-defined functions (UDFs) — only standard IFERROR/IFNA formulas
• Classification is heuristic, not guaranteed — “suspicious” means “worth checking,” not “definitely broken”
• The macro reads from formulas but does not modify the original sheets — all output goes to a new “IFERROR-Audit” report sheet
• Formulas on protected sheets are still scanned (the macro reads only, no writes to original sheets)

#The Macro

Option Explicit

Sub AuditIFERROR()
    ' ── IFERROR Mask Detector ──────────────────────────
    ' Scans every sheet for IFERROR() and IFNA()
    ' formulas. Classifies each as:
    '   GENUINE  — Reasonable fallback (value, text,
    '              reference, or named range)
    '   SUSPICIOUS — Fallback is blank, empty string,
    '                or zero — may be masking errors
    '   REDUNDANT — Wraps a function that can't error
    '               (e.g., simple arithmetic)
    ' Creates an "IFERROR-Audit" report sheet with
    ' hyperlinks back to each source cell. Original
    ' data is never modified.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "IFERROR-Audit"
    Const BLANK_THRESHOLD As String = ""

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, rpt As Worksheet
    Dim cell As Range, formulaCells As Range
    Dim outRow As Long
    Dim fText As String, fUpper As String
    Dim funcName As String, fallback As String
    Dim clas As String, note As String
    Dim genuine As Long, suspicious As Long, redundantCount As Long
    Dim sheetCount As Long

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

    ' ── Step 1: Delete old report if present ───────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    ' ── Step 2: Create report sheet ────────────────────
    Set rpt = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    rpt.Name = OUT_SHEET

    ' ── Step 2a: Write headers ─────────────────────────
    rpt.Range("A1:F1").Value = Array( _
        "Cell", "Sheet", "Function", "Fallback Value", _
        "Classification", "Formula Text")
    rpt.Range("A1:F1").Font.Bold = True
    rpt.Range("A1:F1").Interior.Color = RGB(50, 50, 50)
    rpt.Range("A1:F1").Font.Color = vbWhite

    outRow = 2
    genuine = 0: suspicious = 0: redundantCount = 0
    sheetCount = 0

    ' ── Step 3: Scan every sheet ───────────────────────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = OUT_SHEET Then GoTo NextSheet

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

        If formulaCells Is Nothing Then GoTo NextSheet

        Dim foundOnSheet As Boolean
        foundOnSheet = False

        For Each cell In formulaCells
            fText = cell.Formula
            fUpper = UCase(fText)

            ' ── Check for IFERROR ───────────────────────
            If InStr(fUpper, "IFERROR(") > 0 Then
                funcName = "IFERROR"
            ElseIf InStr(fUpper, "IFNA(") > 0 Then
                funcName = "IFNA"
            Else
                GoTo NextCell
            End If

            foundOnSheet = True

            ' ── Extract fallback value ──────────────────
            fallback = ExtractFallback(fText, funcName)

            ' ── Classify ────────────────────────────────
            call ClassifyIFError fText, funcName, fallback, _
                                clas, note

            ' ── Write to report ─────────────────────────
            rpt.Hyperlinks.Add _
                Anchor:=rpt.Cells(outRow, 1), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!" & cell.Address(False, False), _
                TextToDisplay:=cell.Address(False, False)
            rpt.Cells(outRow, 2).Value = ws.Name
            rpt.Cells(outRow, 3).Value = funcName
            rpt.Cells(outRow, 4).Value = fallback
            rpt.Cells(outRow, 5).Value = clas & " — " & note
            rpt.Cells(outRow, 6).Value = "'" & fText

            ' ── Color-code classification ───────────────
            Select Case clas
                Case "SUSPICIOUS"
                    rpt.Cells(outRow, 5).Interior.Color = RGB(254, 202, 202)
                    rpt.Cells(outRow, 5).Font.Bold = True
                    suspicious = suspicious + 1
                Case "GENUINE"
                    rpt.Cells(outRow, 5).Interior.Color = RGB(217, 249, 212)
                    genuine = genuine + 1
                Case "REDUNDANT"
                    rpt.Cells(outRow, 5).Interior.Color = RGB(254, 240, 138)
                    redundantCount = redundantCount + 1
            End Select

            outRow = outRow + 1

NextCell:
        Next cell

        If foundOnSheet Then sheetCount = sheetCount + 1

NextSheet:
    Next ws

    ' ── Step 4: Format report ──────────────────────────
    If outRow > 2 Then
        With rpt
            .Columns("A:F").AutoFit
            .Columns("F").ColumnWidth = 65
            .Range("A1").Select
            ActiveWindow.FreezePanes = True
        End With

        ' Sort: SUSPICIOUS first, then REDUNDANT, then GENUINE
        rpt.Sort.SortFields.Clear
        rpt.Sort.SortFields.Add _
            Key:=rpt.Range("E2:E" & outRow - 1), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:="SUSPICIOUS,REDUNDANT,GENUINE"
        With rpt.Sort
            .SetRange rpt.Range("A1:F" & outRow - 1)
            .Header = xlYes
            .Apply
        End With
    End If

    ' ── Step 5: Summary MsgBox ─────────────────────────
    Dim total As Long, msg As String
    total = genuine + suspicious + redundantCount

    If total = 0 Then
        msg = "No IFERROR or IFNA formulas found in this workbook."
    Else
        msg = "Audit complete — " & total & " IFERROR/IFNA " & _
              "formula(s) found across " & sheetCount & " sheet(s)." & _
              vbCrLf & vbCrLf & _
              "  SUSPICIOUS (red)  : " & suspicious & _
              " — likely masking errors" & vbCrLf & _
              "  REDUNDANT (yellow): " & redundantCount & _
              " — wraps safe formula" & vbCrLf & _
              "  GENUINE (green)   : " & genuine & _
              " — reasonable fallback" & vbCrLf & vbCrLf & _
              "See '" & OUT_SHEET & "' sheet. Start with " & _
              "SUSPICIOUS (red) items first."
    End If

    MsgBox msg, vbInformation, "IFERROR Audit Report"

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

' ── Helper: Extract the second argument (fallback) ─────
Private Function ExtractFallback(formula As String, _
                                  fname As String) As String
    ' Finds IFERROR(expr, fallback) and returns fallback
    ' Uses simple comma counting — works for nested parens
    ' but not for deeply weird edge cases
    Dim pos As Long, depth As Long, i As Long
    Dim startArg As Long

    pos = InStr(UCase(formula), fname & "(")
    If pos = 0 Then
        ExtractFallback = "[parse error]"
        Exit Function
    End If

    ' Walk past the first argument
    depth = 0
    startArg = 0
    For i = pos + Len(fname) + 1 To Len(formula)
        Select Case Mid(formula, i, 1)
            Case "("
                depth = depth + 1
            Case ")"
                If depth = 0 Then
                    ' End of IFERROR — fallback is empty
                    ExtractFallback = "[empty]"
                    Exit Function
                End If
                depth = depth - 1
            Case ","
                If depth = 0 Then
                    startArg = i + 1
                    Exit For
                End If
        End Select
    Next i

    If startArg = 0 Then
        ExtractFallback = "[no fallback]"
        Exit Function
    End If

    ' Extract from startArg to closing paren (matching depth)
    depth = 0
    For i = startArg To Len(formula)
        Select Case Mid(formula, i, 1)
            Case "("
                depth = depth + 1
            Case ")"
                If depth = 0 Then
                    ExtractFallback = Trim(Mid(formula, startArg, _
                                               i - startArg))
                    Exit Function
                End If
                depth = depth - 1
        End Select
    Next i

    ExtractFallback = Trim(Mid(formula, startArg))
End Function

' ── Helper: Classify the IFERROR/IFNA ─────────────────
Private Sub ClassifyIFError(ByVal formula As String, _
                             ByVal fname As String, _
                             ByVal fallback As String, _
                             ByRef clas As String, _
                             ByRef note As String)
    Dim innerExpr As String, innerUpper As String

    ' ── Step 1: Check fallback for suspicious patterns ──
    If fallback = """""" Or fallback = "" Or _
       fallback = "[empty]" Then
        clas = "SUSPICIOUS"
        note = "Fallback is blank — errors are invisible"
        Exit Sub
    End If

    If fallback = "0" Then
        clas = "SUSPICIOUS"
        note = "Fallback is zero — may hide missing values"
        Exit Sub
    End If

    If fallback = """" & "-" & """" Or _
       fallback = """-""" Or _
       fallback = """N/A""" Or _
       fallback = """n/a""" Then
        clas = "SUSPICIOUS"
        note = "Placeholder fallback — check if errors exist"
        Exit Sub
    End If

    ' ── Step 2: Check if the wrapped expression is safe ──
    ' Extract the inner expression (first argument)
    innerExpr = ExtractInner(formula, fname)
    innerUpper = UCase(innerExpr)

    ' If the inner expression is just arithmetic, it can't
    ' produce a formula error (divide by zero is about
    ' values, not formulas — #DIV/0! is a value error)
    If IsSafeExpression(innerExpr, innerUpper) Then
        clas = "REDUNDANT"
        note = "Wraps safe expression — IFERROR unnecessary"
        Exit Sub
    End If

    ' ── Step 3: Check inner for likely error sources ────
    If InStr(innerUpper, "VLOOKUP") > 0 Or _
       InStr(innerUpper, "XLOOKUP") > 0 Or _
       InStr(innerUpper, "MATCH") > 0 Or _
       InStr(innerUpper, "INDEX") > 0 Then
        clas = "GENUINE"
        note = "Wraps lookup — likely handles #N/A"
        Exit Sub
    End If

    If InStr(innerUpper, "!") > 0 Then
        clas = "GENUINE"
        note = "Cross-sheet reference — could break if sheet deleted"
        Exit Sub
    End If

    If InStr(innerUpper, "[") > 0 Then
        clas = "SUSPICIOUS"
        note = "External workbook reference — common #REF! source"
        Exit Sub
    End If

    If InStr(innerUpper, "/") > 0 Then
        clas = "GENUINE"
        note = "Contains division — handles #DIV/0!"
        Exit Sub
    End If

    ' Default: unclear intent
    clas = "GENUINE"
    note = "Unclear risk — review manually"
End Sub

' ── Helper: Extract first argument from IFERROR/IFNA ──
Private Function ExtractInner(formula As String, _
                               fname As String) As String
    Dim pos As Long, depth As Long, i As Long
    Dim startExpr As Long

    pos = InStr(UCase(formula), fname & "(")
    If pos = 0 Then
        ExtractInner = ""
        Exit Function
    End If

    startExpr = pos + Len(fname) + 1
    depth = 0

    For i = startExpr To Len(formula)
        Select Case Mid(formula, i, 1)
            Case "("
                depth = depth + 1
            Case ")"
                If depth = 0 Then
                    ExtractInner = Trim(Mid(formula, startExpr, _
                                            i - startExpr))
                    Exit Function
                End If
                depth = depth - 1
            Case ","
                If depth = 0 Then
                    ExtractInner = Trim(Mid(formula, startExpr, _
                                            i - startExpr))
                    Exit Function
                End If
        End Select
    Next i

    ExtractInner = Trim(Mid(formula, startExpr))
End Function

' ── Helper: Check if expression is inherently safe ─────
Private Function IsSafeExpression(formula As String, _
                                   fUpper As String) As Boolean
    ' An expression is "safe" if it can't produce formula
    ' errors — only simple arithmetic and constants.
    ' Lookups, external refs, INDIRECT, OFFSET, etc. are
    ' NOT safe.

    Dim unsafePatterns As Variant
    Dim p As Variant

    unsafePatterns = Array( _
        "VLOOKUP", "XLOOKUP", "HLOOKUP", _
        "MATCH", "INDEX", "INDIRECT", "OFFSET", _
        "SUMIF", "COUNTIF", "AVERAGEIF", _
        "SUMIFS", "COUNTIFS", "AVERAGEIFS", _
        "!", "[", "INDIRECT", "ADDRESS", _
        "CELL(", "INFO(", "GETPIVOTDATA", _
        "DSUM", "DGET", "DCOUNT")

    For Each p In unsafePatterns
        If InStr(fUpper, CStr(p)) > 0 Then
            IsSafeExpression = False
            Exit Function
        End If
    Next p

    ' If we reach here, the expression looks like simple
    ' arithmetic or constants — safe from formula errors
    IsSafeExpression = True
End Function

#How It Works

#Three-tier classification: not all IFERRORs are dangerous

The macro doesn’t just find IFERRORs — it rates them. Every IFERROR/IFNA formula gets one of three classifications:

Classification	Color	What it means
SUSPICIOUS	Red	Fallback is blank, zero, or a placeholder. Errors are invisible. Investigate immediately.
REDUNDANT	Yellow	Wraps a simple arithmetic expression that can’t produce formula errors. The IFERROR is dead weight — it adds confusion without adding protection.
GENUINE	Green	Reasonable fallback for a formula that can legitimately error — lookup #N/A, division by zero, cross-sheet reference. Probably fine, but review to confirm.

The report sheet is sorted with SUSPICIOUS items at the top. You open the report, see 3 red rows, and investigate those before anything else. If there are 40 green rows below them, they’re vetted and can be skimmed.

#Suspicious: when IFERROR hides the truth

If fallback = """""" Or fallback = "" Or _
   fallback = "[empty]" Then
    clas = "SUSPICIOUS"
    note = "Fallback is blank — errors are invisible"

The blank-string fallback is the most common and most dangerous pattern. =IFERROR('2024-Henderson.xlsx'!CA_Sales, "") — the cell looks empty, the column still sums, and you have no way to know the data source disappeared. Every preparer has done this, usually because they inherited the formula and never questioned it.

Zero is the second-most-dangerous. =IFERROR(VLOOKUP(A2, RateTable, 2, 0), 0) — if the lookup fails, the cell quietly returns zero. The tax due for that jurisdiction drops by $48,000 and nobody notices because the cell isn’t blank and doesn’t trigger any formula error.

Placeholder text like "-" or "N/A" gets flagged as suspicious too — even though it’s visible, it’s masking the type of error. A cell showing "-" could be hiding a #N/A (harmless, expected) or a #REF! (data is gone). The preparer can’t tell the difference without investigating the underlying formula.

#Redundant: IFERROR wrapping safe arithmetic

If IsSafeExpression(innerExpr, innerUpper) Then
    clas = "REDUNDANT"
    note = "Wraps safe expression — IFERROR unnecessary"

The IsSafeExpression helper checks the inner expression for anything that could produce a formula error: lookups, cross-sheet references, external workbook references, INDIRECT, OFFSET, and a dozen other functions. If none of these are present, the expression is simple arithmetic — and an IFERROR around it is redundant.

=IFERROR(A2+B2-C2, 0) — this can never produce a #REF! or #N/A. The only possible error is #VALUE! if someone types text into an input cell, and you probably want that error to surface. Redundant IFERRORs are clutter — they don’t hurt the model, but they mask the preparer’s intent and make auditing harder.

#Genuine: reasonable error handling

If InStr(innerUpper, "VLOOKUP") > 0 Or _
   InStr(innerUpper, "XLOOKUP") > 0 Then
    clas = "GENUINE"
    note = "Wraps lookup — likely handles #N/A"

Lookups are the most common legitimate IFERROR use case. Account 11010 might not exist in the rate table. The schedule header might not match any row in the TB. These are expected-not-found scenarios, and IFERROR handles them cleanly.

Division gets the GENUINE tag too. =B14/C14 with IFERROR protects against a #DIV/0! when the denominator is zero — common in ratio analysis and apportionment percentage calculations.

Cross-sheet references get a GENUINE classification because they’re common and intentional — but the macro notes “could break if sheet deleted” in the report. The preparer can see the risk and decide whether to harden the reference.

#The external workbook trap

If InStr(innerUpper, "[") > 0 Then
    clas = "SUSPICIOUS"
    note = "External workbook reference — common #REF! source"

External workbook references deserve special attention. When the source file moves to an archive folder, gets renamed, or is on a network share that changes, every IFERROR that references it silently stops working. The output looks fine — zero, blank, whatever the fallback is — but the underlying data is gone. This is the exact scenario from the problem statement. The macro flags every external-reference IFERROR as SUSPICIOUS regardless of what the fallback is, because the risk of a broken link is higher than for any other formula type.

#Fallback extraction: parsing nested IFERRORs

Private Function ExtractFallback(formula As String, _
                                  fname As String) As String
    ' Walk past the first argument with paren counting

The ExtractFallback helper parses the IFERROR or IFNA formula by counting parenthesis depth. It walks past the first argument (which may contain its own commas and nested parens) and captures the second argument. This handles =IFERROR(VLOOKUP(A2,IF(B2>0,Data,Other),2,0), "N/A") correctly — the comma inside the nested IF doesn’t confuse it.

The companion ExtractInner function does the same for the first argument, which is needed by the IsSafeExpression check to determine whether the wrapped expression needs error handling at all.

#Read-only: the original workpaper is never modified

The macro uses SpecialCells(xlCellTypeFormulas) to read formula text, and writes all findings to a new IFERROR-Audit sheet. The original workpaper sheets are never modified — not even a cell value read-and-rewrite. If the preparer disagrees with the classification, or the report is too noisy, they delete the IFERROR-Audit tab and the workpaper is exactly as it was before.

This is especially important for an audit macro. If the macro accidentally modified cells while “auditing” them, nobody would trust it. Read-only output means zero risk.

#Why 3 classification tiers instead of 2

Three tiers create action priority. A 2-tier system (suspicious vs. fine) would dump everything with a fallback value into “fine” — but IFERROR wrapping a cross-sheet VLOOKUP and IFERROR wrapping =A2+B2 are not the same thing. The redundant tier catches wasteful IFERRORs that add confusion, even though they don’t mask real errors. The preparer can see them and decide to simplify the formula for the next reviewer.

#Adapt It