From Excel to Power BI: Migrating Legacy Reporting Without Losing Trust

Somewhere in your business there is a workbook that runs the company. Forty tabs, a few hidden ones, links pointing at a file on somebody's laptop, and a formula in column AJ that no one currently employed can explain. Every month-end it gets rebuilt by hand, emailed round, and argued over. You have already decided to move to Power BI. This post is about doing that without damaging the one thing worth more than the reporting itself: the organisation's belief that its own numbers are right.

If you are still deciding whether to move at all, our Power BI dashboards guide covers that argument. Here the decision is made, and the real work starts.

Why Excel Became Your Reporting Layer

It is worth saying clearly: the spreadsheet sprawl is not a failure of judgement. Excel won because it is genuinely excellent. It is instant, it is flexible, it needs no project approval, and everybody already knows it. When someone in finance needed a number on a Tuesday afternoon, Excel gave it to them that afternoon. No BI tool has ever beaten that on speed.

What Excel does not do is scale into a shared reporting layer for a whole organisation. The typical symptoms, in the order they usually appear:

• No single version of truth - two people pull the same report and get two answers, and both can defend theirs.
• Logic buried in cells - the business rules live inside nested formulas rather than anywhere documented or reviewable.
• Key-person risk - one person understands the model, and the month-end close depends on their calendar.
• No audit trail - you cannot see who changed what, or when a definition quietly shifted.
• Manual assembly - hours of copy, paste, refresh and check, repeated every single period.
• Version by email - Final_v3_USE_THIS.xlsx circulating while three people work from older copies.

What Actually Breaks in the Move

Migrations rarely fail on the technology. Power BI connects to your sources, the model builds, the visuals render. What breaks is everything the spreadsheets were doing that nobody wrote down.

• Undocumented business logic. A workbook applies a rule - excluding intercompany transactions, or a cut-off date, or a rounding convention - that exists nowhere except in a formula. Rebuild without it and your numbers are wrong. Rebuild with it and you may be inheriting a mistake.
• The same metric defined three ways. Sales, finance and operations each have a version of revenue, and each is correct within its own workbook. Power BI forces you to pick one, in public.
• The workbook that is secretly an application. It has data entry, validation, workflow and approvals bolted on with macros. That is not a report, and it does not belong in a BI tool. It usually needs a small custom application instead.
• Manual adjustments with no system home. Accruals, reclassifications, a correction typed in because the source system was wrong. These are real and needed, and they have to be given a proper, auditable home rather than being quietly dropped.
• Sources that are exports of exports. A workbook fed by a CSV that came from another workbook that someone downloaded from the ERP last quarter. Tracing back to the actual system of record is often the single largest piece of work.

Inventory and Triage Before You Build

The first deliverable is not a dashboard. It is a list. Walk the finance, operations and sales teams through every recurring report they produce or consume, and record who builds it, who reads it, how often, what it feeds, and where its data comes from. This exercise is dull and it is the highest-value week of the whole project, because it usually reveals that a third of the reports are no longer read by anyone.

Then triage. Every workbook lands in one of four buckets:

Definitions Before Dashboards

This is the step everyone skips, and it decides whether the project works. Before anyone opens Power BI Desktop, get the relevant people in a room and agree, in writing, what your core metrics mean. What counts as revenue, and at what point is it recognised? What makes a customer active - a purchase in ninety days, or an open contract? Does margin include freight? Which entity, currency and date does the figure use?

Then do the harder part: give each definition a named owner. Not a department, a person. The owner is who decides when the definition changes and who signs off that the Power BI implementation matches it. Without owners, you get a governance vacuum, and in a vacuum the old workbook wins because at least it has an author.

Expect this to be uncomfortable. Agreeing definitions surfaces the fact that two directors have been reporting different numbers to different audiences for years. Surfacing that is a benefit of the migration, not a side effect of it, but it needs a sponsor senior enough to close the argument.

Once definitions are settled, the model can be built properly - a star schema with conformed dimensions and central DAX measures, not a flat copy of the spreadsheet's shape. That modelling work is the bulk of a real Power BI development engagement, and it is what makes every later report consistent by construction.

Reconciliation Is the Trust Mechanism

Here is the moment every one of these projects turns on. The new dashboard shows 4,187,320. The spreadsheet everyone has used for six years shows 4,191,880. The difference is small. The reaction is not.

If you cannot explain that gap the same day, credibility drains fast. People will not tell you they have stopped believing the dashboard. They will simply keep updating the workbook alongside it, and within two months the migration has quietly failed while every status report still says green.

So build reconciliation in as a formal phase, not a QA afterthought:

• Run both systems in parallel for at least two full reporting cycles, ideally three.
• Reconcile to the penny on the headline figures, and line by line beneath them - by entity, period, product, cost centre, whatever your dimensions are.
• Investigate every variance and write down the cause. No variance gets waved through as a rounding difference until someone has proven it is one.
• Assume the new number is sometimes right and the old one wrong. In most migrations, a meaningful share of variances turn out to be genuine defects in the spreadsheet - a range that stopped growing when rows were added, a filter left on, a hardcoded value from a prior year.
• Keep a reconciliation log the finance team can read. It becomes the evidence base when someone challenges a figure six months later.
• Do not go live until the finance lead signs off in writing. Their signature is what transfers trust from the old system to the new one.

The Migration Plan, Step by Step

A sequence that holds up in practice, whether you run it in-house or with an external team:

1. Inventory every recurring report - producer, consumer, cadence, source, downstream use.
2. Triage into retire, replicate, rebuild properly, or deliberately keep in Excel.
3. Agree metric definitions and assign a named owner to each one.
4. Trace every source back to its system of record, and stop depending on exports of exports.
5. Model the data properly - star schema, conformed dimensions, central measures. Do not replicate the spreadsheet's layout in a dataset.
6. Build the single most painful report first. Highest pain, not easiest win - it earns the goodwill that funds the rest.
7. Reconcile in parallel until finance signs off, logging every variance and its cause.
8. Train the people who will use it, and separately train whoever will maintain it.
9. Cut over, with a named owner and a support route for the first month.
10. Retire the old workbook deliberately - announce it, archive a read-only copy, and remove write access. If people can still update it, some of them will.

Adoption Is a Real Workstream

Name the political reality: the person who built and maintained the master workbook has, for years, been the one who knows. Their expertise had visible value every month-end. A migration can feel to them like being made redundant by a tool, and a quiet lack of cooperation from that one person can stall the whole thing.

The fix is straightforward and it is not a communications plan. Make them the owner of the new thing. Put them in the definition workshops, give them the reconciliation sign-off, let them be the person who approves changes to the model. Their domain knowledge is the most valuable asset in the project - they know why that odd exclusion exists in column AJ - and the migration is far more likely to survive if they are its author rather than its subject.

Budget real time for training and for the awkward first month. If your internal team is thin on Power BI skills, it is common to hire Power BI developers for the build and reconciliation phase and then hand over to an internal owner once the model is stable.

What Good Looks Like Afterwards

Six months past a migration that went well, the change is less dramatic than people expect and more valuable than they hoped. There is one definition of each metric, and it has an owner. The refresh runs itself overnight rather than consuming two days of somebody's week. Month-end is shorter because reporting is no longer a build step. New questions get answered by slicing an existing model instead of starting a new workbook.

The clearest signal is what meetings sound like. Arguments about whose number is right are replaced by arguments about what to do next. That is the real return on the project, and the reason reconciliation deserves more attention than visual design.

We do this kind of work on finance data, including a Power BI reporting build for a not-for-profit where accounting and budget reporting moved off static spreadsheets onto a modelled, refreshable single source of truth. The pattern was the one described here: model the accounting data first, agree what each figure means, reconcile until the finance team believed it, then hand over something they could run themselves.

If you are staring at a reporting estate you know needs to move and are not sure where to start, a short scoping conversation over the inventory is usually enough to size it honestly. Get in touch and we will walk through what is worth migrating and what is worth retiring.