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

An Excel to Power BI migration succeeds or fails on one thing: whether your team still trusts the numbers when the new dashboard disagrees with the old workbook. The technology is the easy part. Power BI connects to your sources, the model builds, the visuals render. The real risk is everything the spreadsheets were doing that nobody wrote down. The reliable path is unglamorous: inventory every recurring report, triage what to retire, replicate or rebuild, agree what each metric means and who owns it, then run both systems in parallel and reconcile line by line until finance signs off in writing. Do that, and trust transfers cleanly from the spreadsheet to the dashboard.

If you are still deciding whether to move at all, our Power BI dashboards guide makes that case. Here the decision is made, and the real work begins: protecting trust while you move.

Why Excel Became Your Reporting Layer

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, they fail on undocumented behaviour. Power BI connects to your sources and the visuals render, but the spreadsheets were quietly doing work that lives nowhere except in a formula. These are the things that break:

• 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

Agreeing what your core metrics mean 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 each figure counts and excludes. Then do the harder part: give each definition a named owner. Not a department, a person. The owner decides when the definition changes and 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.

Reconciliation Is the Trust Mechanism

Reconciliation is the moment every one of these projects turns on. The new dashboard shows one number, the spreadsheet everyone has used for six years shows another, and 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.

Cost and Timeline Factors

What an Excel to Power BI migration costs, and how long it takes, depends far more on the state of your data and definitions than on the number of reports. A focused migration of one reporting area on reasonably clean sources runs a few weeks of build plus two to three reporting cycles of parallel reconciliation. A whole finance estate with messy sources is a phased programme, delivered one area at a time. The star-schema modelling and central DAX measures are the bulk of a real Power BI development engagement, and they are what make every later report consistent by construction.

Common Mistakes Teams Make

Most failed migrations repeat the same avoidable errors. Watch for these:

• Building visuals before definitions are agreed. You end up rebuilding the model when the definition dispute finally surfaces, usually during reconciliation with an executive watching.
• Treating reconciliation as a QA step. It is the trust mechanism, not a checkbox. Under-resource it and people quietly keep the old workbook alive.
• Big-bang cutovers. Migrating everything at once gives you the maximum number of unreconciled variances at the maximum level of scrutiny. Go one reporting area at a time.
• Porting a bug into the new system. If the spreadsheet logic is wrong, rebuild the metric with its owner rather than faithfully replicating the error in DAX.
• Leaving the old workbook writable after go-live. If people can still update it, some of them will, and you will have two competing sources within a month.
• Sidelining the person who built the master workbook. Their undocumented knowledge is the most valuable asset in the project. Make them the owner of the new model, not its casualty. If your internal team is thin on the tool, it is common to hire Power BI developers for the build and reconciliation phase and then hand over to that internal owner.

Conclusion

Six months past a migration that went well, the change is quieter 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, and month-end is shorter because reporting is no longer a build step. The clearest signal is what meetings sound like: arguments about whose number is right are replaced by arguments about what to do next.

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. If you are staring at a reporting estate you know needs to move, 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.