Reporting automation solution
Spreadsheet consolidation automation
Combine recurring spreadsheets and CSV files through controlled intake, schema validation, versioned transformations and visible exceptions. Keep useful templates without making manual copy and paste the integration layer.
What spreadsheet consolidation automation should do
Spreadsheet consolidation automation should inventory the expected files, confirm who and what period each file represents, validate structure and values, apply repeatable transformations, detect duplicates, preserve source lineage, combine accepted records and expose rejected inputs with a reason and owner.
It should not guess its way through a changed template. A structural change is a new input version that must be mapped, tested and approved.
01
Manual consolidation hides data quality decisions
- attachments and shared folders contain several versions of the same file;
- columns move, names change and formulas are overwritten;
- copy and paste removes the source record and audit trail;
- duplicates or missing submissions are discovered late;
- errors are corrected directly in the combined output;
- one person knows the undocumented transformation steps; and
- the final workbook is difficult to reproduce.
02
Treat the template as an input contract
| Contract area | Example control |
|---|---|
| File identity | Entity, period, submission type and unique submission ID |
| Schema | Required sheets, tables, columns, names and supported version |
| Values | Data types, valid codes, date ranges and required fields |
| Completeness | Expected entities, row counts and control totals |
| Uniqueness | Business key and duplicate submission rules |
| Ownership | Submitter, correction owner and deadline |
A template version should change deliberately. Keep old mappings only for the periods or contributors that still need them.
03
Create a repeatable path from submission to combined data
Microsoft Power Query can combine files from folders, SharePoint, Azure Blob Storage and other file-system views. Its documentation recommends consistent file type and structure and explains how an example-file transformation is applied to each input. Use that capability inside a controlled process rather than treating a folder as self-governing.
04
Quarantine exceptions with enough information to fix them
Missing
No expected file
Keep the submission open and notify the responsible owner.
Structural
Unsupported template
Reject the file with the failed schema and supported version.
Data
Invalid or duplicate rows
Report row-level reasons without discarding accepted records silently.
Control
Totals do not reconcile
Hold publication until the difference is explained or approved.
05
Preserve lineage from output back to source
- store the original file or an immutable reference where policy permits;
- record checksum, submitter, period, received time and template version;
- add source-file and source-row identifiers to transformed records;
- version transformation logic and test representative historical files;
- make reruns idempotent so the same file does not duplicate output;
- reconcile counts and totals before and after transformation; and
- separate correction of source data from an approved adjustment.
06
When should you keep files, centralise data or build an application?
| Approach | Best fit |
|---|---|
| Controlled file pipeline | Periodic submissions, stable templates and limited collaboration |
| Reporting database or warehouse | Repeat analytical integration, history and governed measures |
| Focused application | Concurrent workflow, permissions, validation, status and direct integration |
| Replace the process | The spreadsheet is only compensating for a missing source-system capability |
Use the guide on when to replace a spreadsheet with an application, or compare a reporting database with a data warehouse.
Sources
Primary references
Questions
Frequently asked questions
What is spreadsheet consolidation automation?
It collects controlled spreadsheet or CSV inputs, validates their identity and structure, transforms them through versioned rules, combines accepted data and routes exceptions for correction before producing an output.
Do all files need the same structure?
A shared schema makes consolidation simpler and safer. When sources differ, define an explicit mapping and validation path for each supported version instead of relying on column position or a single example file.
Can Power Query automate spreadsheet consolidation?
Yes, especially when files share a stable format and location. The wider process may still need submission tracking, validation, exception ownership, approval and monitored refresh.
Should spreadsheets be replaced with an application?
Replace them when concurrent workflow, permissions, validation, scale, audit or integration needs exceed what controlled files can support. A spreadsheet can remain a valid input when its role is bounded.
How do you stop one bad file corrupting the result?
Validate file identity, schema, types, period, duplicates and control totals before merging. Quarantine failures and report them to an owner rather than coercing them silently.
Improve a consolidation process
Bring representative files, owners, deadlines, checks and the current combined output.
LCR can help determine whether a controlled file pipeline, reporting model or focused application is the smallest reliable solution.