Moving reporting off spreadsheets

A spreadsheet to database migration is worth doing when your reports depend on people copying numbers between files, and nobody can say which version is right. The goal isn’t to ban spreadsheets. It’s to stop using them as the system of record.

This is the plan we follow when we move reporting into a database: how to tell it’s time, which target to pick, how to model and test the data, how to migrate without a cliff-edge cutover, and how to keep the finance and operations people who built those spreadsheets on your side.

How do you know it’s time to move reporting off spreadsheets?

It’s time when the monthly report takes days of manual consolidation, when two people bring different totals to the same meeting, or when one person’s absence stops reporting altogether. File size is rarely the real trigger. The trigger is that you can’t trace a number back to its source with confidence.

Other signs we look for:

• Several teams keep their own copy of the same list, such as customers, projects or products, and the copies disagree.
• Reports are built by pasting exports from two or three systems into a master workbook.
• Formulas reference other files on someone’s laptop or a shared drive, and they break when files move.
• There’s no record of who changed a figure, or when.
• Access is all or nothing. Anyone who can open the file can see, and edit, everything in it.

If none of these apply, you may not need a migration yet. A well-kept spreadsheet with one owner and one source is fine.

Which target architecture should you choose?

For most organisations moving off spreadsheets, a single Postgres database with a separate reporting schema is enough. Move to a cloud warehouse such as BigQuery or Redshift when many analysts query large history across many sources. Pick the simplest option your team can run, because the hard part is the data model, not the engine.

The reporting layer on top can be whichever BI tool the organisation already licenses, or a small custom dashboard. We’d rather match what people already use than introduce a new tool alongside a new database.

How should you model the data?

Model the business, not the spreadsheet. Spreadsheets mix inputs, lookups, calculations and presentation on one tab. Split them into raw tables loaded as received, cleaned tables with consistent types and keys, and business tables that match how people ask questions. Definitions such as “active project” should live in one place.

In practice that means three layers:

• Raw: each source loaded as-is, with a load timestamp, so you can always replay or audit a load.
• Cleaned: correct data types, standard date formats, deduplicated records, consistent identifiers across sources.
• Business: facts (transactions, payments, milestones) and dimensions (project, region, cost centre, fiscal period) that reports query directly.

Two modelling decisions cause most of the pain later. First, agree on stable identifiers. If one sheet calls it “Kathmandu Metro” and another “KMC”, you need a mapping table, not a find-and-replace. Second, decide how to handle history. When a project changes region, do old reports show the old region or the new one? Write the answer down before you build.

Keep the transformation logic in version control, as SQL or a tool such as dbt, so changes are reviewed like any other code.

How do you keep bad data out of reports?

Test every load before reports can see it. Check row counts against the source, look for nulls in required fields, duplicates on keys, values outside expected ranges and data that’s older than it should be. When a check fails, block the load or alert a named owner. Don’t let a dashboard quietly show half a month.

The failures we see most aren’t exotic. A source system adds a new status value. An export changes its date format. A scheduled job loads zero rows on a public holiday and nobody notices. Simple tests catch all three.

Add reconciliation checks that finance will recognise: totals by period and cost centre that should match the general ledger or the bank, within an agreed tolerance. Those checks build more trust than any amount of explaining.

What are the migration steps?

Migrate one report or subject area at a time, and run old and new side by side until the numbers reconcile. Start with a report that’s painful but not the most politically sensitive one. Each cycle should end with a report people actually use from the database, before you move to the next.

1. Map the spreadsheets. Find which files people actually rely on, who maintains them, where their data comes from and which reports they feed. The list is usually longer than anyone expects.
2. Pick the first report. Choose one with clear pain and a willing owner.
3. Design the model for that subject area, including identifiers and history rules.
4. Build loads and tests from the original sources where possible, not from the spreadsheets that copied them.
5. Replace manual entry with a form or a validated import, so data is entered once.
6. Run in parallel for at least one full reporting cycle. Investigate every difference. Some will be bugs in the new pipeline, and some will be long-standing errors in the old spreadsheet.
7. Cut over and retire. Make the old file read-only, note where the new report lives, and archive it after an agreed period.

The Town Development Fund’s Real-Time Monitoring System is an example of where this leads. Project monitoring had relied on manual reports, spreadsheets and periodic field visits. The system we built brings project, financial and progress data into one PostgreSQL-backed platform with dashboards by project, municipality, programme and fiscal year.

Who should own the data, and who gets access?

Every table needs a named business owner who decides what it means and a technical owner who keeps it loading. Access should follow roles: most people read business tables through reports, a few analysts query cleaned tables, and only pipelines write to raw tables. Write these rules down before go-live, not after a leak.

Restrict columns that hold personal or salary data, and keep an audit log of who queried what. That’s usually a step up from a spreadsheet anyone with the link could open.

How do you keep finance and operations teams on side?

Involve the people who built the spreadsheets from the first week. They know the exceptions, the manual adjustments and why a column exists. Give them a way to keep working in a familiar format during the transition, and let them sign off the parallel-run reconciliation. If they don’t trust the new numbers, they’ll keep the old file going.

A few things help:

• Keep an export button. Analysts will still want to pull data into a spreadsheet for one-off work, and that’s fine once the source is the database.
• Don’t migrate around month-end or year-end close. Finance teams have no spare attention then.
• Document each metric in plain language next to the report, including the known differences from the old calculation.
• Agree how manual adjustments are recorded. If finance needs to override a figure, give them a table with a reason column rather than letting them edit totals.

When is a database migration the wrong move?

If one person maintains one spreadsheet from one source for a small audience, a migration adds cost and moving parts for little gain. The same goes for exploratory analysis that changes every week. Fix ownership and naming first. Migrate when a second team depends on the numbers or audit trails start to matter.

It’s also the wrong move if nobody will own the new system. A database that no one maintains drifts faster than a spreadsheet, because fewer people can see inside it.

What we don’t do: we don’t replace your ERP or accounting system with a custom database, and we don’t build reporting on top of data nobody has agreed to own. If the real problem is that the source system is wrong, we’ll say so before writing a pipeline around it.

Our data engineering work usually starts with a 1–3 week data audit that maps the spreadsheets, sources and reports before anything is built. If the cleaned data is later meant to feed forecasting or ML models, the same foundations carry over to AI/ML engineering.

Related reading: data labelling for AI, for teams whose next step after clean reporting data is a model.

Frequently asked questions