Seven checks every month-end close spreadsheet should run

Why checks matter more than formulas

Spreadsheet errors are rarely clever. Research collected by the European Spreadsheet Risks Interest Group has documented errors in a large share of operational spreadsheets, and most come from ordinary causes: a missing row, a duplicated paste, a date read as text. Checks catch those causes regardless of who built the formulas.

Put the checks on one visible sheet, each with a status of Pass, Flagged or Fail, so the reviewer sees them before the results.

1. Required columns are present

If a system export changes its layout, every formula downstream can silently point at the wrong column. Check that each expected header exists before anything else runs. A missing column should stop the run.

2. Amounts and dates are readable

Count the rows where an amount is not a number or a date is not a date, for example with COUNTIFS on helper columns or with explicit data types in Power Query. One unreadable amount means the total is wrong.

3. Duplicates are counted once and reported

Exports and copy and paste both create duplicates. Build a key from the fields that should be unique, such as reference, date and amount, and count how often each key appears. Report duplicates rather than quietly removing them, because sometimes they are real.

4. Row counts agree to the source

Record how many rows each export contained and compare it with how many rows the workbook used. This is the simplest completeness check and catches truncated pastes and filtered ranges.

5. Control totals agree to the ledger

The opening balance, period activity and closing balance in the workbook should each agree to the general ledger or control report. Show both figures and the difference, not only a tick.

6. Every difference is explained or flagged

A reconciliation is complete when the difference is fully explained by listed items, such as timing items or unapplied cash, and the unexplained amount is zero. If an unexplained amount remains, the status should read "needs review", never "reconciled".

7. Exceptions have reasons and owners

Each unmatched item should carry the rule it failed, the source row and who needs to look at it. An exception list without reasons just moves the investigation to someone else.

What a failed check should do

Separate two kinds of failure. Missing columns or unreadable data mean no reliable result is possible, so the run should stop. A reconciliation difference is different: show it, mark the run as needing review and let a person investigate. In both cases, a failed or blocked run must never look like a clean one.

The Excel workflow demo runs all seven checks on synthetic data. You can introduce a duplicate receipt and watch the status change.