Hard-Coded Number Detector: Find the Silent Killer in Inherited Workpapers

TL;DR: You inherit a workpaper from a departed senior. The numbers look right, but three cells in the Gross Profit column are typed values, not formulas — someone hardcoded them to make the schedule balance before deadline. This macro scans your sheet, suppresses the noise (headers, zeroes, constant columns), and flags only the cells that look suspicious. Output goes to a new sheet with confidence ratings. Your original workpaper stays untouched.

The Problem

The senior left in April and you inherited the Henderson state apportionment workpaper. Every number ties to the return. The partner signs off. Six months later, during next year’s carryforward, you notice that cell D14 — the California sales factor numerator — is just the number $1,247,000. Not =B14*C14. Not a formula at all. Someone typed it. And you based six months of state estimates on it.

Hard-coded numbers in a column of formulas are the most dangerous Excel errors because they’re invisible. Ctrl+` shows formulas, but you have to scan every cell one by one. A single override in a 500-row schedule is a needle in a haystack. This macro finds the needle — and tells you how confident it is that the needle is actually a problem.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workpaper where at least some columns contain formulas
The macro works on any sheet you’re viewing — no designated tabs required

What the macro does NOT do:

It does not modify any cells on your original sheet. All output goes to a new “Hardcoded-Report” tab.
It does not tell you what the formula should be. It tells you that a cell looks out of place — you decide what to do.
It does not flag every hard-coded number on the sheet. It only flags cells that are hard-coded in columns where most other cells are formulas.

Limitations:

Only scans the UsedRange — data outside that range is ignored
Columns where fewer than 3 rows are formulas won’t trigger any flags (the macro needs a pattern to detect a break in the pattern)
Cells with formula errors (#REF!, #DIV/0!) are treated as formulas, not hard-coded numbers

#The Macro

Option Explicit

Sub DetectHardcodedNumbers()
    ' ── Hard-Coded Number Detector ─────────────────────
    ' Scans the active sheet for hard-coded numbers in
    ' columns where most other cells are formulas.
    ' Writes a report to a new "Hardcoded-Report" sheet
    ' with confidence ratings. Does NOT modify the
    ' original sheet — all output is on the report.
    '
    ' Confidence levels:
    '   HIGH   — Only hardcoded cell in a formula column
    '   MEDIUM — Column is mixed; this one is suspicious
    '   LOW    — Column mostly constant; listed for info
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Hardcoded-Report"
    Const SKIP_ROW_1 As Boolean = True
    Const SKIP_ZERO_VALUES As Boolean = True
    Const MIN_FORMULA_ROWS As Long = 3  ' Min formulas in column to trigger scan

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, wsOut As Worksheet
    Dim rng As Range, cell As Range
    Dim lastRow As Long, lastCol As Long
    Dim outRow As Long, r As Long, c As Long
    Dim totalFormulas As Long, totalConstants As Long
    Dim colFormulas As Long, colConstants As Long
    Dim colHasFormula() As Boolean, colHasConstant() As Boolean
    Dim flagged As Long

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

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    If lastRow <= 1 Then
        MsgBox "No data found on this sheet.", vbExclamation
        GoTo CleanUp
    End If

    ' ── Step 1: Profile each column ────────────────────
    ' Count formulas vs constants per column
    ReDim colHasFormula(1 To lastCol)
    ReDim colHasConstant(1 To lastCol)

    Dim startRow As Long
    startRow = IIf(SKIP_ROW_1, 2, 1)

    For c = 1 To lastCol
        colFormulas = 0
        colConstants = 0

        For r = startRow To lastRow
            Set cell = ws.Cells(r, c)

            If Not IsEmpty(cell) Then
                If cell.HasFormula Then
                    colFormulas = colFormulas + 1
                ElseIf IsNumeric(cell.Value) And _
                       Not IsDate(cell.Value) And _
                       cell.Value <> 0 Then
                    colConstants = colConstants + 1
                End If
            End If
        Next r

        colHasFormula(c) = (colFormulas >= MIN_FORMULA_ROWS)
        colHasConstant(c) = (colConstants > 0)
    Next c

    ' ── Step 2: Create 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

    ' ── Step 3: Write headers ──────────────────────────
    wsOut.Range("A1:F1").Value = Array( _
        "Cell", "Value", "Sheet", "Column Context", _
        "Confidence", "Recommendation")
    wsOut.Range("A1:F1").Font.Bold = True
    wsOut.Range("A1:F1").Interior.Color = RGB(50, 50, 50)
    wsOut.Range("A1:F1").Font.Color = vbWhite

    ' ── Step 4: Scan for suspicious cells ──────────────
    outRow = 2
    flagged = 0

    For c = 1 To lastCol
        ' Skip columns with fewer than MIN_FORMULA_ROWS formulas
        If Not colHasFormula(c) Then GoTo NextCol

        ' Count how many rows in this column are formula vs constant
        colFormulas = 0
        colConstants = 0
        For r = startRow To lastRow
            Set cell = ws.Cells(r, c)
            If Not IsEmpty(cell) Then
                If cell.HasFormula Then
                    colFormulas = colFormulas + 1
                ElseIf IsNumeric(cell.Value) And cell.Value <> 0 Then
                    colConstants = colConstants + 1
                End If
            End If
        Next r

        ' Skip columns that are entirely formula or entirely constant
        If colConstants = 0 Then GoTo NextCol

        Dim formulaRatio As Double
        formulaRatio = colFormulas / (colFormulas + colConstants)

        ' Now flag individual hard-coded cells in this column
        For r = startRow To lastRow
            Set cell = ws.Cells(r, c)

            ' Skip non-numeric, formulas, dates, empty, zeros
            If cell.HasFormula Then GoTo NextRow
            If Not IsNumeric(cell.Value) Then GoTo NextRow
            If IsDate(cell.Value) Then GoTo NextRow
            If SKIP_ZERO_VALUES And cell.Value = 0 Then GoTo NextRow

            ' ── Determine confidence ────────────────────
            Dim confidence As String, recommendation As String

            If formulaRatio >= 0.9 Then
                ' 90%+ of column is formulas — this is highly suspicious
                confidence = "HIGH"
                recommendation = "Investigate — this appears to be a " & _
                    "manual override in a formula column."
            ElseIf formulaRatio >= 0.5 Then
                confidence = "MEDIUM"
                recommendation = "Check — this column has a mix of " & _
                    "formulas and constants. This cell may be intentional."
            Else
                confidence = "LOW"
                recommendation = "Probably fine — this column is " & _
                    "mostly constants. Listed for completeness."
            End If

            ' ── Build column context string ─────────────
            Dim context As String
            context = colFormulas & " formulas, " & colConstants & _
                      " constants in column " & ColLetter(c)

            ' ── Write to report ─────────────────────────
            ' Cell address as a clickable hyperlink
            wsOut.Hyperlinks.Add _
                Anchor:=wsOut.Cells(outRow, 1), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!" & ColLetter(c) & r, _
                TextToDisplay:=ColLetter(c) & r
            wsOut.Cells(outRow, 2).Value = cell.Value
            wsOut.Cells(outRow, 3).Value = ws.Name
            wsOut.Cells(outRow, 4).Value = context
            wsOut.Cells(outRow, 5).Value = confidence
            wsOut.Cells(outRow, 6).Value = recommendation

            ' Color-code the confidence cell
            Select Case confidence
                Case "HIGH"
                    wsOut.Cells(outRow, 5).Interior.Color = RGB(254, 202, 202)
                    wsOut.Cells(outRow, 5).Font.Bold = True
                Case "MEDIUM"
                    wsOut.Cells(outRow, 5).Interior.Color = RGB(254, 240, 138)
                    wsOut.Cells(outRow, 5).Font.Bold = True
                Case "LOW"
                    wsOut.Cells(outRow, 5).Interior.Color = RGB(229, 231, 235)
            End Select

            flagged = flagged + 1
            outRow = outRow + 1

NextRow:
        Next r

NextCol:
    Next c

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

        ' Sort by confidence (HIGH first, then MEDIUM, then LOW)
        wsOut.Sort.SortFields.Clear
        wsOut.Sort.SortFields.Add _
            Key:=wsOut.Range("E2:E" & outRow - 1), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:="HIGH,MEDIUM,LOW"
        With wsOut.Sort
            .SetRange wsOut.Range("A1:F" & outRow - 1)
            .Header = xlYes
            .Apply
        End With
    End If

    ' ── Step 6: Summary ────────────────────────────────
    Dim highCount As Long, medCount As Long, lowCount As Long
    highCount = Application.CountIf( _
        wsOut.Range("E2:E" & outRow), "HIGH")
    medCount = Application.CountIf( _
        wsOut.Range("E2:E" & outRow), "MEDIUM")
    lowCount = Application.CountIf( _
        wsOut.Range("E2:E" & outRow), "LOW")

    Dim msg As String
    If flagged = 0 Then
        msg = "No suspicious hard-coded numbers found " & _
              "on '" & ws.Name & "'." & vbCrLf & vbCrLf & _
              "All columns are either entirely formulas " & _
              "or have too few formulas to establish a pattern."
    Else
        msg = "Scan complete — " & flagged & " hard-coded " & _
              "number(s) found on '" & ws.Name & "'." & vbCrLf & vbCrLf & _
              "  HIGH:    " & highCount & vbCrLf & _
              "  MEDIUM:  " & medCount & vbCrLf & _
              "  LOW:     " & lowCount & vbCrLf & vbCrLf & _
              "See '" & OUT_SHEET & "' sheet for details. " & _
              "Start with HIGH confidence items."
    End If

    MsgBox msg, vbInformation, "Scan Complete"

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: Convert column number to letter ────────────
Private Function ColLetter(colNum As Long) As String
    ColLetter = Split(Cells(1, colNum).Address(True, False), "$")(0)
End Function

#How It Works

#It profiles before it flags

The macro runs two passes. The first pass counts formulas vs constants in every column and builds a profile. The second pass only scans columns that have at least MIN_FORMULA_ROWS formulas (default: 3). A column with 2 formulas and 98 constants is not a “formula column” — it’s a data column, and every hard-coded number in it is probably fine.

This profiling step is what separates this macro from a naive conditional formatting rule. Without it, you get 500 flags and learn nothing.

#Three things it intentionally skips

Row 1. Headers, titles, and section labels live in row 1. Skipping it eliminates ~30% of false positives immediately. The SKIP_ROW_1 constant lets you turn this off if your workpapers put headers in a different row.

Zero values. A blank cell or a zero balance is not a “hard-coded number hiding in a formula column” — it’s just zero. Skipping them reduces noise without losing signal.

Dates. A cell formatted as a date is almost certainly intentional. Period-end dates, filing deadlines, placed-in-service dates — none of these should be flagged. The IsDate() check catches them.

#Confidence rating, not formula suggestion

If formulaRatio >= 0.9 Then
    confidence = "HIGH"
    recommendation = "Investigate — this appears to be a " & _
        "manual override in a formula column."

The column ratio is the single best signal for whether a hard-coded number is suspicious. If 90%+ of cells in a column are formulas and one cell is typed, that cell is almost certainly a bug or a deliberate override that the reviewer needs to know about.

At 50-90%, the column is genuinely mixed — it could be intentional. At below 50%, the column is mostly data and the flag is informational only.

This is deliberately not a formula suggestion. The macro doesn’t try to guess what =B14*C14 should be. It tells you “this cell is out of place” — you, the preparer who knows the workpaper, decide whether to fix it.

#Custom sort order for the output

wsOut.Sort.SortFields.Add _
    Key:=wsOut.Range("E2:E" & outRow - 1), _
    CustomOrder:="HIGH,MEDIUM,LOW"

The output sheet is sorted with HIGH confidence items at the top. The preparer opens the report, sees 3 red rows, and investigates those immediately. If there are 40 LOW confidence items below them, they can be skimmed or ignored based on time.

#No undo needed because nothing was changed

The original sheet is never modified. The macro reads from it and writes to a new sheet. If the preparer disagrees with the results, they delete the Hardcoded-Report tab and move on. This is intentional — VBA has no undo button, so the safest macro is one that never changes the source data.

#Clickable cell references — one click back to the source

Each cell address in the report (column A) is a hyperlink. Click D14 and Excel jumps directly to cell D14 on the original workpaper. No scrolling, no “which sheet was this on?” — one click and you’re looking at the suspicious cell in context. The hyperlink uses SubAddress:="'" & ws.Name & "'!" & cellRef, which handles sheet names with spaces and special characters automatically.

#Why the report sheet goes at the END

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

The TB comparator puts its output at the beginning (as the first sheet, before all the data). This one puts it at the end. The reason: the hard-coded report is a review tool, not a permanent fixture. Putting it at the end keeps it out of the way while the preparer flips through the tabs they’re actually auditing.

#Adapt It

Scan a specific range instead of the entire sheet. Replace the lastRow and lastCol detection with a user-selected range:

Dim scanRng As Range
On Error Resume Next
Set scanRng = Application.InputBox( _
    "Select the range to scan for hard-coded numbers", Type:=8)
If scanRng Is Nothing Then GoTo CleanUp
On Error GoTo CleanUp

' Then loop over scanRng.Rows and scanRng.Columns
' instead of 1 To lastRow and 1 To lastCol

Adjust sensitivity. Change MIN_FORMULA_ROWS to widen or narrow the scan:

Const MIN_FORMULA_ROWS As Long = 1   ' Flag every hard-coded cell in any column with at least 1 formula
Const MIN_FORMULA_ROWS As Long = 10  ' Only flag columns with 10+ formulas (fewer false positives)

Show zeros. If your workpapers use zero as a meaningful override (not just empty/blank), set SKIP_ZERO_VALUES = False.

Scan row 1 too. If your workpapers start data in row 1 (no header row), set SKIP_ROW_1 = False.

Flag hard-coded numbers in constant columns too. If you want to see every hard-coded number regardless of context (more of a full audit than a targeted scan), change MIN_FORMULA_ROWS to 1 and lower the confidence thresholds:

If formulaRatio >= 0.5 Then confidence = "HIGH"
If formulaRatio >= 0.2 Then confidence = "MEDIUM"
' Everything else gets "LOW"

Run on every sheet in the workbook. Wrap the core logic in a For Each ws loop to scan the entire workbook in one pass. Add a ws.Name column to the output so you know which sheet each flagged cell came from.

See also: Error Cell Navigator — finds formula errors like #REF! and #VALUE!. Pair it with this macro for a complete workpaper audit. For comparative validation, see TB Year-Over-Year Comparator.

Download the hard-coded detector sample workbook (state apportionment workpaper with 3 hidden hardcoded numbers and a reference sheet)

xlsx 8 KB