How to move from spreadsheets to real-time analytics

Name the decisions, agree the definitions, build one governed model, publish from it and retire the spreadsheets, then add alerts. The sequence that works.

You move a business from spreadsheet-based decisions to real-time analytics in five steps, in this order: name the decisions the spreadsheets currently feed and who makes them; agree the definitions behind each number and reconcile them to the ledger, with finance signing them off; build one governed Power BI semantic model over the source systems that produces those numbers automatically; publish the pack from the model, run it in parallel for one month end and then retire the spreadsheets; and only then add alerts and triggers for the decisions that have a clear threshold. For one business domain that is typically a four-week Establish phase and an eight to twelve week build. Most decisions need yesterday's numbers rather than the last minute's, which the standard refresh of up to eight times a day on Power BI Pro, or 48 on a Fabric capacity, already gives; true real time is the exception, delivered through a lakehouse or mirroring where it pays. This post sets out each step, how real-time the analytics actually needs to be, and what changes for the people involved.

Why the spreadsheets persist

Spreadsheets are not the problem. They persist because they work: they hold logic nobody else has written down, they adapt in an afternoon, and the person who owns them trusts them. The cost is in the hours spent rebuilding them each month, the versions that disagree, and the decisions made on numbers that were already out of date. The hidden cost of manual reporting puts figures on it. Replacing them works when the new route is more trusted than the old one, which is why the sequence starts with decisions and definitions rather than dashboards.

Step one: name the decisions

List the decisions the spreadsheets feed: pricing, stock, cash, staffing, which customers to chase, which jobs are losing money. For each, note who makes it, how often, with which spreadsheet, and what happens next. This is a few conversations, not a project, and it tells you which spreadsheets matter and which can simply stop.

Step two: agree the definitions

Every number in the pack needs a written definition that finance has signed: what counts as revenue, when a sale is booked, how margin treats discounts, what an active customer is. Reconcile each to the ledger before anyone builds a chart. This is where most of the hidden logic in the spreadsheets surfaces, and where most of the arguments are settled once instead of every month. How do you build a single trusted analytics layer? covers the method.

Step three: build one governed model

Build a Power BI semantic model over the source systems, with the agreed measures defined once, the business's own names and descriptions, row-level security, and refreshes on a schedule. For a single system such as Business Central this can sit directly on the connector; when three or more systems need combining, a Fabric lakehouse sits underneath. What is a semantic model, and why does it matter? explains the layer, and how do you migrate business reporting from Excel to Power BI? covers the migration itself.

Step four: publish, run in parallel, retire

Produce the pack from the model, with each page naming the decision it serves. Run it alongside the spreadsheets through one month end and reconcile the two. When they agree, retire the spreadsheets on a date the owners have agreed, and keep Excel connected to the model for the analysis people still want to do there. Retiring the old route is the step most often skipped, and the reason many businesses end up with both.

Step five: add alerts and triggers

Once the numbers are trusted and refreshed automatically, the decisions with a clear threshold can be raised rather than found: a customer past their credit limit, stock below cover, a job past its margin floor. Power BI alerts, Fabric Activator or Power Automate send the right person a message with the context and the next action. This is where analytics stops being something people visit and starts arriving when it matters. How do you turn dashboards and reports into decisions? shows how it works.

How real-time does it need to be?

Ask how quickly each decision is made and acted on. A weekly pricing review needs last week's numbers; a daily stock decision needs this morning's; a dispatch or service decision may need the last few minutes. The standard Power BI refresh covers most finance and commercial decisions. Near real time comes from frequent loads into a lakehouse, Direct Lake, Real-Time Intelligence or, for Business Central from release 29.1, mirroring into OneLake. How do you get real-time financial reporting from Business Central? explains the options.

What changes for the people involved

The person who built the spreadsheets usually becomes the most valuable person in the project: their logic becomes the definitions, and their time moves from rebuilding the pack to analysing it. Managers stop reconciling versions and start discussing the decision. Training should be built around the reports people open, not around Power BI as a product.

Where to start

Pick the one spreadsheet pack that costs the most time each month and the decisions it feeds, and take it through all five steps before starting the next. Our Power BI page describes how we deliver it, and the free audit will tell you which pack to start with.

How do you move a business from spreadsheet-based decisions to real-time analytics? The five-step sequence