Sum Check Row Inserter: Verify Every Column's Total Against Your Expected Numbers

TL;DR: This macro inserts a SUM row beneath the active sheet’s data and lets you enter expected column totals — one InputBox, comma-separated. Every column that matches gets a green cell. Every column that doesn’t gets a red cell and a dollar difference in the report. You’ll know in 3 seconds whether the client’s TB actually balances to what they told you it should.

The Problem

A client emails you their trial balance. “Total assets should be $4,847,320,” they write. “Liabilities are $2,100,000, retained earnings covers the rest.”

You open the file. It’s 800 rows. You scroll to the bottom — no totals row. You write =SUM(D2:D801) in a cell below the data, spot-check a number, then write =SUM(E2:E801) for the next column, and =SUM(F2:F801) after that. You compare each computed total to the email. Three columns match. The Asset column is off by $11,500. Where? You don’t know yet, but you know something’s wrong.

That’s five minutes of manual SUM formulas and squinting at numbers. This macro does all of it — every column, every comparison, every flag — in one InputBox and one click.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A sheet with tabular data — column headers in row 1, data starting in row 2
At least one numeric column
Your expected column totals (from a cover letter, email, or prior-year file)

Limitations:

Works on the active sheet only — switch to the sheet you want to check first
Column headers must be in row 1 (change HEADER_ROW in the code if yours differ)
Expected totals are matched to columns by order, not by header name — enter them in the same left-to-right order as the columns appear in your sheet
Skips columns with no numeric data (text columns, blank columns)
The SUM row is inserted two rows below the last data row — it will overwrite anything sitting there, so make sure the area below your data is clear
Tolerance is ±$0.01 by default (change TOLERANCE to adjust)

#The Macro

Option Explicit

Sub SumCheckRow()
    ' ── Sum Check Row Inserter ────────────────────────
    ' Inserts a SUM row below the active sheet's data,
    ' then checks every numeric column against your
    ' expected totals from a single InputBox.
    '
    ' Change TOLERANCE and HEADER_ROW below to customize.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const TOLERANCE As Double = 0.01       ' Max $ diff for a match
    Const HEADER_ROW As Long = 1           ' Row containing column headers
    Const MATCH_COLOR As Long = &H00C6EFCE ' Light green
    Const MISMATCH_COLOR As Long = &H00D6D6FF ' Light red/pink

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

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

    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long, sumRow As Long
    Dim col As Long, i As Long
    Dim expectedInput As String
    Dim expectedParts() As String
    Dim expectedVals() As Double
    Dim colSum As Double, expected As Double
    Dim matchCount As Long, mismatchCount As Long
    Dim skipCount As Long
    Dim report As String, line As String

    Set ws = ActiveSheet

    ' ── Find the data range ────────────────────────────
    On Error Resume Next
    lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious).Column
    On Error GoTo CleanUp

    If lastRow <= HEADER_ROW Then
        MsgBox "No data found below row " & HEADER_ROW & ".", _
               vbExclamation, "Sum Check"
        GoTo CleanUp
    End If

    sumRow = lastRow + 2   ' Leave a blank row before totals

    ' ── Ask for expected totals ────────────────────────
    expectedInput = InputBox( _
        "Enter expected column totals, comma-separated:" & vbCrLf & _
        "Columns with no expectation = enter 0 or leave blank." & vbCrLf & _
        vbCrLf & "Example: 4847320, 2100000, , 2747320" & vbCrLf & _
        "(checks columns A,B,D; skips C)", _
        "Expected Totals", "")

    If expectedInput = "" Then GoTo CleanUp

    ' Parse into array of Doubles
    expectedParts = Split(expectedInput, ",")
    ReDim expectedVals(0 To UBound(expectedParts))
    For i = 0 To UBound(expectedParts)
        If IsNumeric(Trim(expectedParts(i))) And _
           Trim(expectedParts(i)) <> "" Then
            expectedVals(i) = CDbl(Trim(expectedParts(i)))
        Else
            expectedVals(i) = 0   ' 0 = skip this column
        End If
    Next i

    ' ── Write the SUM CHECK label ──────────────────────
    ws.Cells(sumRow, 1).Value = "SUM CHECK →"
    ws.Cells(sumRow, 1).Font.Bold = True

    ' ── Insert SUMs and check each column ──────────────
    matchCount = 0: mismatchCount = 0: skipCount = 0
    report = ""

    For col = 1 To lastCol
        ' Skip columns with no numeric data
        If Application.WorksheetFunction.Count( _
            ws.Range(ws.Cells(HEADER_ROW + 1, col), _
                     ws.Cells(lastRow, col))) = 0 Then
            If col <= UBound(expectedParts) + 1 Then
                If expectedVals(col - 1) <> 0 Then skipCount = skipCount + 1
            End If
            GoTo NextCol
        End If

        ' Insert SUM formula
        ws.Cells(sumRow, col).Formula = "=SUM(" & _
            ws.Range(ws.Cells(HEADER_ROW + 1, col), _
                     ws.Cells(lastRow, col)).Address(False, False) & ")"
        colSum = Round(Application.WorksheetFunction.Sum( _
            ws.Range(ws.Cells(HEADER_ROW + 1, col), _
                     ws.Cells(lastRow, col))), 2)

        ' Check against expectation
        If col - 1 <= UBound(expectedParts) Then
            expected = expectedVals(col - 1)
            If expected <> 0 Then
                If Abs(colSum - expected) <= TOLERANCE Then
                    ws.Cells(sumRow, col).Interior.Color = MATCH_COLOR
                    matchCount = matchCount + 1
                    line = "Col " & ColLetter(col) & ": $" & _
                           Format(colSum, "#,##0.00") & " ✓"
                Else
                    ws.Cells(sumRow, col).Interior.Color = MISMATCH_COLOR
                    mismatchCount = mismatchCount + 1
                    line = "Col " & ColLetter(col) & ": $" & _
                           Format(colSum, "#,##0.00") & _
                           " (expected $" & Format(expected, "#,##0.00") & ")"
                    If colSum > expected Then
                        line = line & " — OVER by $" & _
                               Format(colSum - expected, "#,##0.00")
                    Else
                        line = line & " — UNDER by $" & _
                               Format(expected - colSum, "#,##0.00")
                    End If
                End If
                report = report & line & vbCrLf
            Else
                skipCount = skipCount + 1
            End If
        End If

NextCol:
    Next col

    ' ── Format the sum row ─────────────────────────────
    With ws.Range(ws.Cells(sumRow, 1), ws.Cells(sumRow, lastCol))
        .Font.Bold = True
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .Weight = xlThick
        End With
    End With

    ws.Cells(sumRow, 1).EntireRow.AutoFit

    ' ── Report results ─────────────────────────────────
    Dim msg As String
    msg = matchCount & " match(es), " & _
          mismatchCount & " mismatch(es)"
    If skipCount > 0 Then
        msg = msg & ", " & skipCount & " skipped"
    End If
    msg = msg & "." & vbCrLf & vbCrLf & report

    If Len(msg) > 1000 Then
        msg = Left(msg, 997) & "..."
    End If

    MsgBox msg, IIf(mismatchCount > 0, vbExclamation, _
                    vbInformation), "Sum Check"

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: column number to letter (e.g., 1 → A, 27 → AA) ──
Private Function ColLetter(colNum As Long) As String
    If colNum <= 26 Then
        ColLetter = Chr(64 + colNum)
    Else
        ColLetter = Chr(64 + Int((colNum - 1) / 26)) & _
                    Chr(65 + ((colNum - 1) Mod 26))
    End If
End Function

#How It Works

#One InputBox, left-to-right expectations

The InputBox asks for a comma-separated list of numbers — one per column, in left-to-right sheet order. If column D is the one you care about but columns A through C are just account codes and descriptions, leave them as blanks or zeros:

Example: , , , 4847320, 2100000

This tells the macro: skip columns A–C (account code, description, prior year), check column D against $4,847,320, and check column E against $2,100,000. Non-numeric entries and blanks are treated as 0, which means “skip this column.”

Why not key=value matching against column headers? Because header text varies wildly between firms — one preparer’s “CY Balance” is another’s “Current Year Ending Balance 12/31/2026.” Left-to-right position is unambiguous. The column letter is right there in your sheet. You can count them faster than you can type a header name.

expectedParts = Split(expectedInput, ",")
For i = 0 To UBound(expectedParts)
    If IsNumeric(Trim(expectedParts(i))) And _
       Trim(expectedParts(i)) <> "" Then
        expectedVals(i) = CDbl(Trim(expectedParts(i)))
    Else
        expectedVals(i) = 0   ' 0 = skip this column
    End If
Next i

#Green means match, red means investigation

The color coding is the whole point:

Green (MATCH_COLOR): The computed SUM equals your expected total within the tolerance (±$0.01 by default). You can move on.
Red/pink (MISMATCH_COLOR): The computed SUM differs from your expected total by more than the tolerance. Something’s off — a missing JE, a duplicated line, a client-provided number that isn’t what they said.
No color (or gray): No expectation was provided for that column, or the column contains text. The SUM is still there, but no check was run.

The tolerance is $0.01 by default. This catches the difference between $4,847,320.00 and $4,847,320.45 — which matters. But it doesn’t flag pennies from floating-point rounding. If you’re verifying whole-dollar schedules (common in tax), set TOLERANCE to 0.99 to allow small rounding differences.

#The SUM formula stays live

The macro inserts =SUM(D2:D801), not a hard-coded value. This is deliberate. If you notice a mismatch and fix a row, the SUM updates automatically — no need to re-run the macro. The green/red fill is static (it won’t re-color itself after you edit data), but you can re-run the macro to get fresh colors.

If you prefer to freeze the values (so the SUM row doesn’t change even if data changes), add two lines at the end:

ws.Range(ws.Cells(sumRow, 1), ws.Cells(sumRow, lastCol)).Value = _
    ws.Range(ws.Cells(sumRow, 1), ws.Cells(sumRow, lastCol)).Value

This converts all the formulas to their current values — a one-time snapshot.

#The ColLetter helper

Excel’s Address property gives you $D$2:$D$801, but that’s verbose. The ColLetter helper converts column numbers to letters: 1 → A, 4 → D, 27 → AA. The report says “Col D: $4,835,820 (expected $4,847,320) — UNDER by $11,500” instead of a cell address the user has to decode. It handles multi-letter columns (AA, AB, …) correctly without any external references.

Private Function ColLetter(colNum As Long) As String
    If colNum <= 26 Then
        ColLetter = Chr(64 + colNum)
    Else
        ColLetter = Chr(64 + Int((colNum - 1) / 26)) & _
                    Chr(65 + ((colNum - 1) Mod 26))
    End If
End Function

#The MsgBox report tells you what to investigate

The final MsgBox lists every checked column, its computed total, and whether it matched. For mismatches, it shows the dollar difference and whether the total is OVER or UNDER your expectation. This lets you prioritize: if Assets are under by $11,500 and Liabilities match perfectly, the problem is in the asset detail, not the entire TB.

The message is truncated at 1,000 characters — a limitation of VBA’s MsgBox. If your sheet has 50 numeric columns, you won’t see all 50 lines in the popup. For comprehensive reports, use the Formula Reporter or Range Quick Stats macros — this one is built for the 3-to-10 column scenario typical of a TB or fixed asset schedule.

#Adapt It

Change the tolerance. If whole-dollar workpapers can have ±$1 rounding without being wrong:

Const TOLERANCE As Double = 0.99

If you’re reconciling exact cents (bank recs, JE logs):

Const TOLERANCE As Double = 0   ' exact match only

Match by header name instead of column position. Some users prefer to type header names in the InputBox — especially on wide sheets where counting columns is error-prone. Replace the expected-values loop with this header-matching approach:

' InputBox format: "Assets=4847320, Liabilities=2100000"
Dim hdr As String
hdr = Trim(CStr(ws.Cells(HEADER_ROW, col).Value & ""))
For i = 0 To UBound(expectedParts)
    If InStr(1, expectedParts(i), hdr, vbTextCompare) > 0 Then
        expected = CDbl(Split(expectedParts(i), "=")(1))
        ' ... comparison logic ...
    End If
Next i

The InputBox format changes to Assets=4847320, Liabilities=2100000 — the macro pulls the header from row 1 and looks for a matching key in your list.

Run on all sheets. Wrap the scan in a sheet loop to verify every sheet in the workbook:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ' Skip instructions or summary tabs
    If ws.Name Like "Instructions*" Or ws.Name Like "Cover*" Then GoTo NextWS
    ws.Activate
    ' ... the macro body here (use ws instead of ActiveSheet) ...
NextWS:
Next ws

This is useful for multi-entity consolidations where every entity sheet should have the same column structure and you want to verify all of them.

Change the colors. If red/green doesn’t work for your screen or your printer:

Const MATCH_COLOR As Long = &H00E2EFDA   ' darker green
Const MISMATCH_COLOR As Long = &H00C0C0FF ' darker red

Or swap to borders instead of fills if you print in black and white:

' Replace .Interior.Color with border patterns:
ws.Cells(sumRow, col).Borders(xlEdgeBottom).LineStyle = xlContinuous
ws.Cells(sumRow, col).Borders(xlEdgeBottom).Weight = xlThick
ws.Cells(sumRow, col).Borders(xlEdgeBottom).Color = _
    IIf(match, RGB(0, 128, 0), RGB(255, 0, 0))

Change which row is the header row. If your headers are on row 5 (after firm name, client name, and engagement info in rows 1–4):

Const HEADER_ROW As Long = 5

The SUM range starts at HEADER_ROW + 1, so data must begin immediately below the header row. If you have blank rows between the header and the data (e.g., a sub-header on row 6), the SUM will include those blanks — still correct, just shows $0.00 for blanks.

See also: Range Quick Stats — no-InputBox stats for a selected range. Use it before Sum Check Row to see min/max/average before you verify totals. Missing Value Auditor — finds blank cells in mandatory columns. If your sum is off, this finds where the blanks are.

Download the sample workbook (trial balance with a deliberate $11,500 asset mismatch)

xlsx 22 KB