Established onAny spreadsheet, any versionTolerance required: see the entry on floating pointReconciliation: against a figure produced outside the file
Compute the same figure twice by different routes and require the two to agree. It is the oldest technique in accounting and it is the only one on this site that catches arithmetic that is valid and wrong.
Why it is the only one
Every other check depends on somebody noticing something: a figure that looks too large, a formula that reads oddly, a column that is half text. The whole difficulty described in the first part of this site is that the wrong answer looks exactly like the right one.
Two independent routes to the same number do not depend on anybody noticing. Either they agree or they do not, and the sheet says which.
Cross-footing
The classic form: total a grid along its rows, total it down its columns, total each set of totals, and compare. If a cell is wrong, or a range is short, or a formula is inconsistent, the two grand totals differ and the difference is the size of the error.
It costs one row, one column and one cell, and it is the standard layout of every hand-prepared ledger for several centuries.
The check cell
A cell comparing the two figures and reporting a word rather than a number: something obvious when they disagree and something quiet when they agree.
Compare with a tolerance rather than for equality, for the reason given in the entry on floating point. A tolerance of a hundredth of a currency unit is right for money and nothing is right for everything.
The independence requirement
A check comparing two figures that came down the same path is decorative. If the row totals and the column totals both derive from the same intermediate calculation, an error in that calculation moves both and the check stays green.
This is the commonest way for a model to be full of checks and still wrong, and the test is simple: could a single mistake move both sides. If yes, it is not a check.
Reconciliation
The stronger version compares against something produced entirely outside the workbook: the source system's own total, a bank statement, a figure from a different department.
That is independent by construction and it is the check worth having on anything financially significant. It also fails usefully: a persistent small difference is a definition mismatch, which is a fact worth knowing rather than an error to eliminate.
Where to put the checks
Together, in one block, at the top of a sheet that is visible when the file opens, with a single master cell reporting whether all of them pass.
Checks scattered across a workbook are checks nobody looks at. One cell saying everything agrees, or naming what does not, is a cell people read without being asked to.
Control totals on the way in
Every import should carry a count of records and a total of the main numeric column, compared against what the source said it sent. That single pair catches truncation at a row limit, a missing source in a stack, and a column that arrived as text and summed to zero.
Three of this site's parts end at that recommendation from different directions.
What a failed check should do
Be loud. A check reporting a difference in small grey text beside a large number will be read as a rounding note. Conditional formatting turning the cell red is the right use of colour, because the value is the truth and the colour is derived from it.
The check that should exist and rarely does
A comparison against the same figure from the previous period. Not for correctness but for plausibility: a total that moved by a factor of ten is either news or an error, and the sheet can ask the question.
What we cannot verify
Nothing in this entry is a claim about software behaviour beyond the tolerance argument, which follows from the storage format documented in the entry on floating point. The practice described is standard in accounting and in financial modelling and is codified in the modelling standards referred to in the fourth part.
What a difference tells you
The size of a discrepancy is diagnostic. A difference equal to one row's value means a range is short by one. A difference equal to exactly one entry means something is counted twice or omitted. A difference of a few hundredths is rounding.
So the check should show the difference rather than only the verdict: the number names the fault more often than the word does.
Checks that survive editing
Put the check formulas where an inserted row or column cannot land between them and what they measure, and refer to whole table columns rather than to fixed ranges. A check that quietly stops covering the last row is worse than no check, because it reports agreement.
In short
- Two independent routes to one figure do not depend on anybody noticing anything.
- Cross-footing costs one row, one column and one cell.
- Compare with a tolerance rather than for equality, because of how numbers are stored.
- If a single mistake could move both sides, it is not a check.
- Reconciling against a figure produced outside the workbook is independent by construction.
- Put every check in one visible block with a single cell reporting the lot.