Every serious spreadsheet error story — the copy-down that cost billions, the report that summed 4,970 of 5,000 rows — shares one feature: the workbook knew. The evidence was sitting in the cells; no formula was checking. Software engineers solved this problem decades ago with automated tests. The spreadsheet version is humbler and just as effective: a checks block — a handful of formulas whose only job is to audit the rest of the file, continuously, and go red before your reader goes looking.
The anatomy of a check
Every check is one sentence made testable: something that must be true about this workbook. Written as a formula returning TRUE/FALSE (or a count of violations, where zero is the pass):
=SUM(Report[Amount]) = SUM(Orders[Amount])
The report accounts for every pound in the data. If a filter quietly excluded rows, a lookup dropped the unmatched, or a range stopped a row short — this line turns FALSE the moment it happens, not the month someone reconciles.
The standard battery, most of which you’ve met as diagnostics and now install as permanent residents:
| The claim | The check |
|---|---|
| Totals reconcile end-to-end | =ROUND(SUM(a),2)=ROUND(SUM(b),2) — pennies respected |
| No text-numbers in the amounts | =COUNT(col)=COUNTA(col) — the COUNT gap |
| Every order matched a customer | =SUMPRODUCT(--ISNA(matches))=0 |
| No duplicate keys | =COUNTA(ids)=ROWS(UNIQUE(ids)) — the counting rule |
| No errors anywhere that matters | =SUMPRODUCT(--ISERROR(Report))=0 |
| Dates inside the period | =COUNTIFS(dates,"<"&start)+COUNTIFS(dates,">"&end)=0 |
| The amortisation lands on zero | =ABS(final_balance)<0.01 |
The IS family (ISNUMBER, ISTEXT, ISBLANK, ISNA) plus
-- to turn TRUE/FALSE into countable 1/0
builds nearly all of them.
Make it impossible to miss
Checks that hide are checks that fail silently — the exact disease they exist to cure. So: a Checks section at the top of the Calc sheet (or its own tab in a big model), one row per check — plain- English claim, the formula, and a status cell:
=IF(check, "OK", "FAIL")
with conditional formatting making FAIL glow. Then the master line — the one that travels:
=IF(COUNTIF(Checks[Status],"FAIL")=0, "ALL CHECKS PASS ✓",
COUNTIF(Checks[Status],"FAIL") & " CHECKS FAILING")
— displayed on the output sheet, beside the dashboard’s timestamp. A report that publicly certifies itself changes the conversation: readers stop spot-auditing your numbers, and you stop being asked to.
The habit that builds the battery
Don’t sit down to “write tests” — you won’t. Instead, every bug you fix becomes a check. The day a text-number skews a total, add the COUNT check; the day an unmatched key slips through, add the ISNA line. Each incident costs you once and then guards forever — the workbook accumulates immunity the way the cleaning routine accumulated steps. Five checks catch most disasters; a mature model carries ten and never mentions them.
This is also, quietly, the professional signature. Anyone can build a model that’s right today. The inheritable workbook is one that announces when it stops being right — under new data, new months, new hands. A green checks panel is the cheapest trust a spreadsheet can buy.
The workbook always knows. The checks row is how it learns to say so — out loud, in red, in time.