Most Excel reports that eat a day a month can be automated inside Excel, with Power Query, which is already in your copy of Excel and costs nothing. The day usually goes on exporting from the accounts system, pasting, fixing the columns, and re-pointing the formulas; Power Query does the export, the paste and the fixing on a click, and the formulas stay put. That's the first move and it's the right one for a report read by one or two people. Power BI is the right move when the report has to be shared with people who should each see their own slice, when it has to refresh itself on a schedule without anyone opening a file, when it needs history the source systems don't keep (last twelve month-ends of aged debt), or when it joins two or more systems on a shared key. Those four are the line, and everything on the Excel side of it should stay in Excel.
What does "automate an Excel report" actually mean?
Three different things, usually conflated:
Automate the data getting in. The export-paste-fix cycle. This is Power Query's whole job: connect to the source (a database, a folder of exports, an API, another workbook), apply the cleaning steps once, refresh forever. Ninety percent of the time saved is here.
Automate the calculations. The formulas that break when a row is inserted. Power Pivot (the data model inside Excel) and measures written in DAX replace fragile cell formulas with calculations over tables. It's the same engine Power BI uses, inside Excel.
Automate the distribution. Refreshing on a schedule and sending it to people without a human opening the file. Excel can't do this on its own; this is where Power BI, or at least Power Automate, comes in.
If your problem is the first two, stay in Excel. If it's the third, or one of the other three symptoms below, it's Power BI.
Symptom by symptom
| Symptom | Fix | Tool | Effort |
|---|---|---|---|
| Someone exports from the accounts system and pastes into a template each month | Power Query connection to the source, refresh on open | Excel | Hours to a day |
| Formulas break when rows change | Load to the data model, write measures instead of cell formulas | Excel (Power Pivot) | A day or two |
| Several exports have to be combined (branches, months, entities) | Power Query "combine files" from a folder | Excel | Hours |
| The report is sent as an attachment and nobody's sure which version is current | Publish once, everyone opens the same thing | Power BI (or SharePoint for the workbook) | Depends |
| Different people should see different slices (their region, their department) | Row-level security | Power BI | Part of a build |
| It has to refresh at 6am without anyone opening it | Scheduled refresh (8/day in a Pro workspace) | Power BI | Part of a build |
| You want a trend the source system doesn't hold (aged debt at each month end) | Stored snapshots in a model | Power BI (or a database) | Part of a build |
| Two systems have to be joined on a customer or project key | A model with one key table and relationships | Power BI | A build |
| It's read on a phone | Mobile layout | Power BI | Part of a build |
| It's read by a board that doesn't open Excel | A page, not a workbook | Power BI | Part of a build |
Source: my working rule from moving reports across the line in both directions; effort estimates are typical, not quotes.
The pattern is visible in the tool column: everything that's about getting data in and calculating stays in Excel, and everything that's about sharing, scheduling, history and joining moves to Power BI.
Why start in Excel?
Because it's already there, the person who built the report already knows it, and Power Query in Excel is exactly the same Power Query that's in Power BI. Any work done in Excel's Power Query (the connection, the cleaning steps, the shaping) copies straight into Power BI later if the report crosses the line. Nothing is wasted.
It's also honest about scale. A report read by the FD and the MD, refreshed on a Monday by clicking a button, doesn't need a Power BI workspace, two licences and a gateway. It needs Power Query and twenty minutes of setup. I've written about where Excel is the right answer at Power BI vs Excel: When the Spreadsheet Is the Right Answer, and it's a longer list than most Power BI consultants would like.
When does it become Power BI?
When one of the four applies:
Sharing with slices. The moment "send Sarah the version with just her region" is a monthly task, it's row-level security, and Excel doesn't have it.
Unattended refresh. The moment someone has to open the file for it to update, it's stale whenever they're on holiday. Power BI refreshes on a schedule and the page is current whenever it's opened.
History. The moment someone wants "how has this looked over the last year", and the source system only shows today, a model needs to store the snapshots. Excel can do this with a folder of dated exports and Power Query combining them, which is a fair interim, but it gets fragile.
Joins. The moment the report needs the CRM and the accounts, or the timesheets and the projects, on one shared key, it's a model. Excel's data model can technically do it, but the maintenance, the refresh and the sharing all push it over the line.
The costs on the Power BI side: Pro is £10.80 per user per month on Microsoft's UK page as of September 2026, paid yearly, excluding VAT, for each viewer. The build, if it's one source (the Excel report was already reading one system), is what the First Report is for: one system, one report, the headline figure reconciled to the source to the penny, £1,950 fixed, ten working days (see the First Report), and "a folder of spreadsheets" is one of the sources named on the page. If the Excel report was joining two systems by hand, it's a Reporting Sprint. Moving an existing Excel report across as-is, reconciled to the old version, is Report Migration.
What about macros, Python and "automate Excel with AI"?
Macros still work and there are a lot of them in finance teams. They're the thing most likely to break on an Excel update and least likely to be understood by the next person. If a macro is doing what Power Query does (getting data in and cleaning it), replace it with Power Query; it survives Excel updates better and anyone can read the steps by clicking through them.
Python in Excel and the various scripting routes are useful for genuinely complex transformations, and Power BI's Fabric side has notebooks for the same. For the ordinary month-end report, they're more machinery than the job needs.
AI-generated dashboards are a separate question, covered at Should You Replace Power BI with an AI-Built Dashboard?. Short version: the reconciliation still has to tie, and something has to refresh it.
Common questions
How do I automate an Excel report from my accounting software?
Use Power Query inside Excel to connect to the source (a database, an export folder, an API or a connector), apply the cleaning steps once, and refresh on a click. That removes the export-paste-fix cycle, which is where most of the time goes.
Is Power Query the same in Excel and Power BI?
Yes. The connections and transformation steps built in Excel's Power Query copy directly into Power BI. Starting in Excel wastes nothing if the report later needs sharing or scheduling.
When should I move an Excel report to Power BI?
When it needs row-level sharing (each person sees their slice), unattended scheduled refresh, stored history the source doesn't keep, or a join between two systems on a shared key. If none of those apply, stay in Excel.
Can Excel refresh a report automatically on a schedule?
Not on its own. A workbook refreshes when opened or when someone clicks refresh. Scheduled unattended refresh is a Power BI feature (up to eight times a day in a Pro workspace), or a Power Automate flow with some limitations.
Should I replace my Excel macros with Power Query?
If the macro gets data in and cleans it, yes: Power Query survives Excel updates better and the steps are visible to the next person. Macros that drive Excel's interface or produce formatted output are a different case.
How much does it cost to move an Excel report to Power BI?
Power BI Pro is £10.80 per viewer per month (Microsoft UK, September 2026). Rebuilding a single-source report properly is a First Report at £1,950 fixed; migrating an existing report as-is, reconciled to the old one, is priced per report at Report Migration.
Does automating the report mean I lose control of the numbers?
The opposite, if a reconciliation is built in. A Power Query or Power BI report with a page showing its totals against the source system's is more trustworthy than a hand-built spreadsheet, because the check is visible every time it refreshes.