· Validation & Checksums · 13 min read

Inconsistent Formula Detector: Find the One Cell That Doesn't Match Its Neighbors

Scans every column for formula cells whose R1C1 pattern doesn't match the column's dominant formula. Flags the one cell that was manually altered, copy-pasted wrong, or broken during a restructure — and writes a clickable report. Does not modify the original data.

Share:
Table of Contents

TL;DR: A depreciation schedule has 500 rows in column F, all using =E{row}*0.2. Except row 347 — someone changed it to =E347*0.15. The numbers are close enough that the cross-foot still works, but the methodology is inconsistent. This macro finds that one cell by comparing every formula in a column against the dominant R1C1 pattern, flags the mismatches, and writes a clickable report sheet. Your original workpaper stays untouched.

The Problem

You’re reviewing the Henderson fixed asset schedule before filing. Column F should be 20% of column E — MACRS 5-year, straight-line convention. Every row uses =E{row}*0.2. The cross-foot ties to the trial balance. The partner signs off.

Six weeks later, during the Q1 estimate calculation, you notice it: row 347 — a 2019 addition — uses =E347*0.15. Not 20%. Fifteen percent. The preparer manually adjusted one asset’s depreciation rate because the GL showed a different number, and instead of investigating, they hard-coded the override into the formula. The schedule footed because the dollar amount was small enough. But it was wrong — and you signed the return based on it.

Inconsistent formulas in a column of identical calculations are the most subtle Excel errors in tax workpapers. They don’t produce #REF! or #N/A. They produce numbers — plausible, believable numbers that are just slightly wrong. This macro finds them by comparing every formula in a column against the dominant pattern. One InputBox, one click, and you know exactly which cells don’t match.

#Prerequisites & Setup

What you’ll need:

  • Excel 2016+ (desktop)
  • A workpaper where at least one column contains multiple formulas
  • The macro works on the active sheet or all sheets — your choice at runtime

What the macro does NOT do:

  • It does not modify any cells on your original sheets. All output goes to a new “Inconsistent-Formulas” report tab.
  • It does not tell you which formula is correct. It tells you which cells don’t match the majority — you decide whether the majority is right or the outlier is intentional.
  • It does not flag hard-coded numbers. This macro compares formulas to formulas. For hard-coded numbers hiding in formula columns, use the Hard-Coded Number Detector.

Limitations:

  • Only scans the UsedRange of each sheet — data outside that range is ignored
  • A column needs at least 3 formula cells to establish a “dominant pattern.” Columns with 1–2 formulas are skipped.
  • The dominant pattern must appear in at least 3 cells to be considered a pattern. If a column has 10 different formulas and no single formula appears 3+ times, the column is skipped.
  • R1C1 pattern matching is structural, not semantic. =A1+B1 and =RC[-2]+RC[-1] match, but =A1+B1 and =$A$1+B1 do not (different references). Most inconsistent formulas in tax workpapers come from manual overrides, copy-paste errors, or restructuring — this catches all three.
  • Hidden sheets are skipped by default. See Adapt It to include them.

#The Macro

Option Explicit

Sub DetectInconsistentFormulas()
    ' ── Inconsistent Formula Detector ──────────────────
    ' Scans each column on the active sheet (or all
    ' sheets) for formula cells whose R1C1 pattern does
    ' NOT match the column's dominant formula.
    ' Writes a report to a new "Inconsistent-Formulas"
    ' sheet with hyperlinks back to each mismatch.
    ' Does NOT modify the original data.
    '
    ' R1C1 notation normalizes row-relative references
    ' so =E5*0.2 and =E6*0.2 both become RC[-1]*0.2,
    ' making pattern comparison row-independent.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Inconsistent-Formulas"
    Const MIN_FORMULA_CELLS As Long = 3
    Const MIN_DOMINANT_COUNT As Long = 3

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, wsOut As Worksheet
    Dim cell As Range, rng As Range
    Dim lastRow As Long, lastCol As Long
    Dim outRow As Long, r As Long, c As Long
    Dim scope As String
    Dim flagged As Long, sheetsScanned As Long
    Dim startWS As Worksheet

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp
    Set startWS = ActiveSheet

    ' ── Scope selection ────────────────────────────────
    scope = InputBox("Scan formulas on:" & vbCrLf & _
        "  Y = Active sheet only" & vbCrLf & _
        "  N = All sheets", _
        "Scan Scope", "Y")
    If scope = "" Then GoTo CleanUp

    ' ── Remove old report if it exists ─────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

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

    ' ── Write headers ──────────────────────────────────
    wsOut.Range("A1:F1").Value = Array( _
        "Sheet", "Cell", "Actual Formula", _
        "Expected Pattern", "Dominant Count", "Suggestions")
    wsOut.Range("A1:F1").Font.Bold = True
    wsOut.Range("A1:F1").Interior.Color = RGB(50, 50, 50)
    wsOut.Range("A1:F1").Font.Color = vbWhite

    ' ── Scan sheets ────────────────────────────────────
    outRow = 2
    flagged = 0
    sheetsScanned = 0

    Dim wsArr() As Worksheet
    Dim i As Long, wsCount As Long

    If UCase(Left(scope, 1)) = "Y" Then
        wsCount = 1
        ReDim wsArr(1 To 1)
        Set wsArr(1) = ActiveSheet
    Else
        wsCount = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> OUT_SHEET Then
                If ws.Visible = xlSheetVisible Then
                    wsCount = wsCount + 1
                End If
            End If
        Next ws
        ReDim wsArr(1 To wsCount)
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> OUT_SHEET Then
                If ws.Visible = xlSheetVisible Then
                    i = i + 1
                    Set wsArr(i) = ws
                End If
            End If
        Next ws
    End If

    For i = 1 To wsCount
        Set ws = wsArr(i)

        ' Determine used range
        Set rng = Nothing
        On Error Resume Next
        Set rng = ws.UsedRange
        On Error GoTo CleanUp
        If rng Is Nothing Then GoTo NextWS

        lastRow = rng.Rows.Count + rng.Row - 1
        lastCol = rng.Columns.Count + rng.Column - 1

        sheetsScanned = sheetsScanned + 1

        ' ── Analyze each column ─────────────────────────
        For c = rng.Column To lastCol
            ' ── Collect all formula R1C1 patterns ───────
            Dim patterns As Object
            Set patterns = CreateObject("Scripting.Dictionary")

            For r = rng.Row To lastRow
                Set cell = ws.Cells(r, c)
                If cell.HasFormula Then
                    Dim r1c1 As String
                    r1c1 = Application.ConvertFormula( _
                        cell.Formula, xlA1, xlR1C1, , cell)
                    If Not patterns.Exists(r1c1) Then
                        patterns.Add r1c1, 1
                    Else
                        patterns(r1c1) = patterns(r1c1) + 1
                    End If
                End If
            Next r

            ' Skip if too few formula cells
            Dim totalFormulas As Long
            totalFormulas = 0
            Dim key As Variant
            For Each key In patterns.Keys
                totalFormulas = totalFormulas + patterns(key)
            Next key
            If totalFormulas < MIN_FORMULA_CELLS Then GoTo NextCol

            ' ── Find dominant pattern ───────────────────
            Dim dominantPattern As String
            Dim dominantCount As Long
            dominantPattern = ""
            dominantCount = 0

            For Each key In patterns.Keys
                If patterns(key) > dominantCount Then
                    dominantPattern = key
                    dominantCount = patterns(key)
                End If
            Next key

            ' Skip if dominant is too weak
            If dominantCount < MIN_DOMINANT_COUNT Then GoTo NextCol

            ' ── Flag mismatches ─────────────────────────
            For r = rng.Row To lastRow
                Set cell = ws.Cells(r, c)
                If cell.HasFormula Then
                    r1c1 = Application.ConvertFormula( _
                        cell.Formula, xlA1, xlR1C1, , cell)

                    If r1c1 <> dominantPattern Then
                        ' ── Sheet name ──────────────────
                        wsOut.Cells(outRow, 1).Value = ws.Name

                        ' ── Hyperlinked cell address ────
                        Dim cellAddr As String
                        cellAddr = cell.Address(False, False)
                        wsOut.Hyperlinks.Add _
                            Anchor:=wsOut.Cells(outRow, 2), _
                            Address:="", _
                            SubAddress:="'" & ws.Name & _
                                "'!" & cellAddr, _
                            TextToDisplay:=cellAddr

                        ' ── Actual formula in A1 ────────
                        wsOut.Cells(outRow, 3).Value = _
                            "'" & cell.Formula

                        ' ── Expected pattern in A1 ──────
                        Dim expectedA1 As String
                        expectedA1 = Application.ConvertFormula( _
                            dominantPattern, xlR1C1, xlA1, , cell)
                        wsOut.Cells(outRow, 4).Value = _
                            "'" & expectedA1

                        ' ── Dominant count ──────────────
                        wsOut.Cells(outRow, 5).Value = _
                            dominantCount & " of " & totalFormulas

                        ' ── Suggestion ──────────────────
                        Dim suggestion As String
                        suggestion = "This cell's formula differs " & _
                            "from the dominant pattern used by " & _
                            dominantCount & " other formula cells " & _
                            "in column " & ColLetter(c) & "."
                        wsOut.Cells(outRow, 6).Value = suggestion

                        ' Highlight mismatch row
                        wsOut.Rows(outRow).Interior.Color = _
                            RGB(254, 240, 138)

                        flagged = flagged + 1
                        outRow = outRow + 1
                    End If
                End If
            Next r

NextCol:
        Next c

NextWS:
    Next i

    ' ── Format and summarize ───────────────────────────
    If flagged > 0 Then
        With wsOut
            .Columns("A:F").AutoFit
            .Columns("C").ColumnWidth = 60
            .Columns("D").ColumnWidth = 55
            .Columns("F").ColumnWidth = 60
            .Range("A1").Select
            ActiveWindow.FreezePanes = True
            .Rows(1).AutoFilter
        End With

        MsgBox "Found " & flagged & " inconsistent formula(s) " & _
               "across " & sheetsScanned & " sheet(s)." & vbCrLf & vbCrLf & _
               "See '" & OUT_SHEET & "' sheet for details. " & _
               "Click any cell address to jump to the mismatch.", _
               vbExclamation, "Formula Inconsistencies Found"
    Else
        MsgBox "No inconsistent formulas found across " & _
               sheetsScanned & " sheet(s)." & vbCrLf & vbCrLf & _
               "All formula columns use consistent patterns.", _
               vbInformation, "All Clear"
    End If

CleanUp:
    If Not startWS Is Nothing Then
        On Error Resume Next
        startWS.Activate
        On Error GoTo 0
    End If
    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

#R1C1 notation is the secret weapon

The macro doesn’t compare A1-notation formulas directly. It converts every formula to R1C1 notation first using Application.ConvertFormula:

r1c1 = Application.ConvertFormula( _
    cell.Formula, xlA1, xlR1C1, , cell)

In R1C1 notation, references are relative to the cell containing the formula. =E5*0.2 becomes =RC[-1]*0.2 — “the cell one column to my left, times 0.2.” =E6*0.2 becomes the same thing: =RC[-1]*0.2. Even though the A1 formulas reference different rows, their R1C1 patterns are identical because both reference the cell one column to the left in their own row.

This means the macro compares structure, not literal text. A column of 500 cells where every formula says “the cell to my left times 0.2” will all match regardless of which row they’re in. The one cell that says “the cell to my left times 0.15” won’t match — and that’s exactly what you want.

Without R1C1 conversion, you’d have to strip out row numbers with complex string parsing. With it, ConvertFormula does the heavy lifting in one call.

#Dictionary groups identical patterns

The macro uses a Scripting.Dictionary to count how many times each unique R1C1 pattern appears in a column:

Dim patterns As Object
Set patterns = CreateObject("Scripting.Dictionary")

For r = rng.Row To lastRow
    Set cell = ws.Cells(r, c)
    If cell.HasFormula Then
        r1c1 = Application.ConvertFormula(...)
        If Not patterns.Exists(r1c1) Then
            patterns.Add r1c1, 1
        Else
            patterns(r1c1) = patterns(r1c1) + 1
        End If
    End If
Next r

After this pass, patterns contains every unique R1C1 formula used in the column, mapped to its count. =RC[-1]*0.2 → 498, =RC[-1]*0.15 → 1, =RC[-1]*0.2+100 → 1. The dominant pattern is clearly =RC[-1]*0.2 with 498 occurrences, and the other two are outliers.

#Two thresholds prevent false positives

The dominant pattern must satisfy two conditions before the column is scanned for mismatches:

  1. At least MIN_FORMULA_CELLS formula cells total (default: 3). A column with one formula and 99 constants isn’t a formula column — there’s no pattern to break.

  2. At least MIN_DOMINANT_COUNT occurrences of the dominant pattern (default: 3). If a column has 5 formulas and all 5 are different, there’s no “dominant” pattern to compare against. The column might be genuine (e.g., a notes column where formulas are ad-hoc) and flagging everything would be noise.

These thresholds mean the macro stays silent on columns where inconsistency is expected. It only speaks up when there’s a clear pattern and a clear break in that pattern.

#The report shows both actual and expected formulas

Each flagged row has two formula columns:

wsOut.Cells(outRow, 3).Value = "'" & cell.Formula         ' Actual
wsOut.Cells(outRow, 4).Value = "'" & expectedA1            ' Expected

The actual formula is what’s in the cell. The expected formula is the dominant pattern, converted back to A1 notation for readability. If the dominant pattern is =RC[-1]*0.2 and the flagged cell is E347, the expected formula shows =E347*0.2 — exactly what the preparer should have entered.

The leading apostrophe (') prevents Excel from trying to evaluate the formula string when you click on the report cell. It also keeps the formula text left-aligned so it’s easy to read at a glance.

#The suggestion column gives context

suggestion = "This cell's formula differs from the dominant pattern " & _
    "used by " & dominantCount & " other formula cells in column " & _
    ColLetter(c) & "."

Instead of just listing the mismatch and leaving the preparer to count, the suggestion column tells them exactly how many other cells use the correct pattern. “498 of 500” is a lot more compelling than just “mismatch” — it tells the preparer that this is almost certainly a bug, not an intentional override.

#Why the report has an AutoFilter, not a sort

.Rows(1).AutoFilter

The report doesn’t sort results — it applies an AutoFilter instead. The mismatches appear in scan order (sheet by sheet, column by column, row by row), which preserves the natural context of the workpaper. The preparer can filter by sheet name to focus on one workpaper at a time, or scan the unfiltered list to see the full picture. Sorting by something artificial (like formula text) would scramble this natural order and make the report harder to navigate.

#Adapt It

Download the inconsistent formula detector sample workbook (fixed asset schedule with 3 hidden inconsistent formulas across 5 sheets)
xlsx 10 KB

Get the next macro in your inbox

One copy-paste-ready macro recipe every two weeks. No spam, no VBA theory — just automation that saves you time.

One macro recipe every two weeks. Unsubscribe anytime.

E

Excel Macro Guy

Excel enthusiast · married to an accountant

I love Excel. My wife is an accountant. Every busy season, I watch her wrestle with workpapers and think "a macro could do that in half a second." So I build them. She tests them on real client data. What survives gets published here.

More about me →

Back to top