Reconcile a spreadsheet before writing the story around it

Freeze the input and write the expected grain

Save an untouched copy of the received workbook or export and record its retrieval date. Then write one sentence describing what a row represents: one invoice, one customer-month, one task, or one line item. This is the grain. Many spreadsheet mistakes are not arithmetic errors; they are accidental changes in grain, such as joining one invoice to several payment rows and then summing the duplicated invoice amount.

Record the candidate key for that grain. A customer-month table may require customer_id + month, not customer ID alone. If no stable key exists, say so and treat deduplication as a business decision rather than a cleanup trick.

Run checks in a fixed order

The UK Department for Education's quality guidance explicitly calls for row and column counts, missing and duplicated data checks, unique identifiers compared with row counts, filter checks, and internal consistency between data, graphs, tables, and commentary. Microsoft Power Query's profiling tools expose validity, errors, empties, distinct values, and distributions. Its documentation warns that profiling defaults to the first 1,000 rows unless the whole dataset is selected. A clean preview is not necessarily a clean file.

Use control totals at matching granularity

A grand total can match while categories are wrong. Reconcile in layers: overall sum, period sum, then a meaningful business dimension such as project or status. If the source total includes tax but the analysis table does not, document the basis rather than forcing equality. For each mismatch, create a bridge showing excluded, duplicated, reclassified, or adjusted records.

Hypothetically, a freelancer receives 1,240 invoice lines representing 410 invoices. After joining a client table, there are 1,247 lines. The money total rises even though no new invoices were added. Before interpreting client revenue, compare key counts and find which client IDs are duplicated in the lookup. This is an illustrative failure pattern, not a reported test.

Check the narrative last

1. Confirm the title, period, currency, units, and inclusion rules.
2. Reconcile source and transformed row counts.
3. Test key uniqueness and blanks.
4. Reconcile sums overall and by at least one meaningful dimension.
5. Trace every headline number, chart, and percentage back to a table cell or query output.

Write the interpretation only after the checks pass. Then read each sentence against the figures: “grew” needs a valid comparison period; “largest” needs complete categories; “average” needs the intended denominator. Keep quality notes with the workbook, not in someone's memory.

If the spreadsheet feeds a query, continue with SQL answer validation. If it feeds an automated recurring report, use the release controls in the automation change guide. Reconciliation is the boundary between data preparation and storytelling; crossing it early makes polished errors harder to notice.