Reporting and KPIs

Excel to Power BI: A Controller's Migration Guide

Moving from Excel to Power BI the right way. What a controller should migrate first, what to leave, and how to keep every number tying out.

Austin Levine

·

Updated

Moving from Excel to Power BI works best when you migrate the data model first, not the spreadsheet. Rebuild your source tables and calculations as a clean semantic model, then connect Power BI to that. A straight copy of the workbook carries every wrong calculation and manual step with it, so the numbers stop tying out. Do the model right and the reports stay correct on their own.

Your month-end close still runs through one giant spreadsheet, and every month you hope nothing in it has slipped out of place. That's the real reason most finance teams look at moving from Excel to Power BI. You want the charts, yes, but underneath that you want to stop babysitting a workbook that only one person fully understands. What you actually want is simpler. You want to trust the number without checking it by hand every time.

Here's the part that trips people up. Power BI works from a data model, and if you hand it a copy of your spreadsheet with the same tangle of manual steps inside, you haven't fixed anything. You have moved the mess into a tool that hides it better. The formulas that were wrong in Excel are still wrong, they just render as a clean chart now, which makes them harder to catch.

Key takeaways

  • Moving from Excel to Power BI works best when you migrate the data model first, not the spreadsheet.

  • Rebuild your source tables and calculations as a clean semantic model, then connect Power BI to that.

  • A straight copy of the workbook carries every wrong calculation and manual step with it.

  • Start where the payoff is clear and the risk is contained, not with the biggest report.

What does moving from Excel to Power BI actually mean?

It helps to be plain about what you're migrating, because "Excel to Power BI" gets used to mean two very different jobs.

The first job is a straight lift. You take the workbook, import it, and point a few visuals at the same cells. This feels fast and it's what most people try first. It also carries every problem with it: the hardcoded numbers, the VLOOKUPs that silently return the wrong row, the tab someone renamed last March that breaks the whole chain.

The second job is a rebuild. You look at where the numbers really come from, the source systems and exports, and you build a proper data model that Power BI connects to directly. The spreadsheet stops being the source. It becomes one more thing you can retire.

The first job looks cheaper on day one. The second job is the one that actually gets you off the manual close. A controller who wants numbers they can defend needs the rebuild, even though it's more work up front.

Why do the numbers stop tying out after a migration?

Because a spreadsheet hides a lot of manual work that the eye never notices, and a migration exposes all of it at once.

Think about what actually happens in your close workbook. Someone pastes an export from the accounting software. Someone adjusts three cells by hand for a late invoice. A formula down column H references a range that shifted when a row was inserted. None of that's written down anywhere. It lives in habit and memory. When you move to Power BI, the tool only knows what's in the model, so every one of those undocumented steps has to be found and rebuilt, or the total comes out different from the spreadsheet everyone already trusts.

Spreadsheet-based reporting carries a real risk here. A peer-reviewed study by Panko and colleagues in 2024 found that 94% of business spreadsheets used in decision-making contained errors. Most of those errors sit undetected in a working file for months. When you migrate, you don't add new errors. You finally surface the ones that were there all along, which is a good thing as long as you plan for it instead of being caught off guard.

The fix is to treat reconciliation as part of the migration, not an afterthought. You rebuild each number, then you tie the new Power BI figure back to the old spreadsheet figure and you find out exactly why any gap exists before you switch over.

What should a controller migrate first?

Not everything, and not the biggest report. Start where the payoff is clear and the risk is contained.

  • The recurring report you rebuild by hand every month. The one that eats a day of copy and paste. Automating that first gives you time back immediately and proves the approach to your team.

  • The numbers people argue about. If sales and finance show different revenue figures, that's a single-source problem, and a modelled version with one agreed definition ends the argument.

  • Reports where the source data is already reasonably clean. A quick early win builds trust in the new setup before you tackle the messy corners.

Leave for later the one-off analyses and the personal working files. Not every spreadsheet needs to become a dashboard. Some are fine as spreadsheets. Power BI is for the reports that repeat, that many people read, and that need to be right every time.

Here's how the two approaches compare once you lay them side by side.

Approach

Straight lift of the workbook

Rebuild as a data model

Time to first result

Fast

Slower up front

Numbers tie out long term

No, hidden steps break

Yes, every step is rebuilt

Refresh

Still manual in places

Automatic on a schedule

One agreed number

No, old duplicates carry over

Yes, one definition each

Who can maintain it

Still the one spreadsheet owner

The whole team, it's documented

The pattern is the same across the table. The lift is faster to start and slower to trust. The rebuild is slower to start and the only one that gets you a report you don't have to check by hand.

What does a clean migration look like step by step?

A migration that stays correct follows a plain order, and none of the steps are exotic.

First, you map where each number really comes from. Not the spreadsheet cell, the actual source: the accounting software, the CRM export, the bank file. Second, you build a semantic model that pulls from those sources directly, with one clear definition for each measure and a proper date table so "last quarter" always means the same range. Third, you rebuild the calculations as DAX measures instead of buried spreadsheet formulas, so the logic is written down and visible. Fourth, you reconcile every headline number against the old workbook until you can explain any difference. Only then do you switch people over and retire the manual file.

Skip the reconciliation step and you get the classic problem: the new dashboard goes live, someone spots a total that doesn't match last month's spreadsheet, and trust in the whole project evaporates in one meeting. Do the reconciliation and the switch is quiet, which is exactly what you want.

How CaseWhen can help

If you're planning a move from Excel to Power BI, the question underneath it's whether you rebuild the model properly or just copy the mess into a nicer tool. That's the work we do at CaseWhen. We map where your numbers come from, build a semantic model with one agreed definition per measure, and reconcile every figure back to the spreadsheet your team already trusts, so nothing goes live until it ties out.

Start with our free Power BI Governance and Cost Scorecard. It's a five-minute assessment that scores where your reporting stands today and flags the top risks before you migrate anything. If you have a manual close you want off Excel, book a migration scoping call with us and we'll look at your real reports and give you a plan.

FAQ

Can I just import my Excel file into Power BI? You can, and it will work at a basic level, but a straight import carries every hidden manual step and faulty formula with it. The numbers often stop tying out because the undocumented adjustments in the workbook don't come across. Rebuilding the data model is the approach that gives you reports you can trust.

How long does an Excel to Power BI migration take? It depends on how many reports and how clean the source data is. A single recurring report can be a matter of weeks, including reconciliation. A full close process takes longer because each number has to be mapped, rebuilt, and tied back to the old spreadsheet before you switch over.

Do I still need Excel after moving to Power BI? Yes, for what Excel is good at: quick one-off analysis and personal working files. Power BI is for the reports that repeat, that many people read, and that have to be correct every time. Most teams keep both and just stop running the monthly close through a spreadsheet.

Read next

Is your Power BI set up the right way?

1

Book 45 minutes and tell us what you want looked at.

2

We walk through your reports and setup with you, live.

3

Within two working days, you get our plan in writing.

Is your Power BI set up the right way?

1

Book 45 minutes and tell us what you want looked at.

2

We walk through your reports and setup with you, live.

3

Within two working days, you get our plan in writing.

Is your Power BI set up the right way?

1

Book 45 minutes and tell us what you want looked at.

2

We walk through your reports and setup with you, live.

3

Within two working days, you get our plan in writing.

CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.

Berlin, Germany

© CaseWhen Consulting GmbH

English

CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.

Berlin, Germany

© CaseWhen Consulting GmbH

English

CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.

Berlin, Germany

© CaseWhen Consulting GmbH

English