Consolidate monthly spreadsheets

Monthly reports drift. Someone renames a heading, adds a total row, or changes the sheet layout, and a process that worked in January quietly breaks in April.

Quick answerFix the target schema once, then validate every monthly submission against it rather than assuming it still matches. Map acceptable variation, flag genuine exceptions, and keep each row traceable to the submission it came from.

1. DefineSet the fields the monthly output needs, once.

2. CollectTake each month’s submissions unchanged — do not pre-edit them by hand.

3. ValidateCheck each file against the schema for renamed or missing fields.

4. ConfirmReuse known mappings; review anything new.

5. ConsolidateProduce this month’s master, with source kept.

The problem is distributed editing, not Excel

Once several people can edit their own copy of a template, the copies gradually diverge. A recurring consolidation process has to tolerate harmless variation, such as a reordered column, without silently absorbing a genuinely changed one, such as a renamed field with a different meaning.

Example: London renames “Revenue” to “Sales Value” this month. A schema-aware process flags the new heading for a one-time confirmation; a position-based copy silently keeps working, but only because it got lucky.

Make the exception, not the rule, do the work

Most months, most submissions will match the schema exactly. Design the process so those files pass through with no manual intervention, and spend your attention only on the files that actually changed.

Common questions

Should every contributor use the same template?

A shared template helps, but a robust monthly process should still detect when a real submission drifts from it, because eventually one will.

How do I trace a wrong figure back to its source after consolidation?

Keep a Source File column in the output. It is the difference between a two-minute check and re-opening every submission.

Doing this every month already?

Upload this month’s submissions and see how the columns line up before you commit to a manual process that repeats the same checking work every cycle.

Try Quick Consolidate