A monthly report that currently takes a day of copy-and-paste work can, in most cases, be automated with tools you already have: Power Query comes built into Excel and refreshes your report with a single click. If you want several people to see live figures, a Power BI dashboard is the next step; if you also want to get rid of the manual exports, you connect straight to the source. In this article we walk through the three routes practically — what they solve, what they cost and when each one fits — so you can stop retyping this very month.
Recognise this? Five signals
A report is ready for automation the moment building it takes more time than reading it. In practice you recognise that from five recurring signals — and the more of them feel familiar, the bigger the win from automating.
1. Version chaos. Files are circulating with names like report_may_v7_FINAL_really-final.xlsx, and in the management meeting it turns out two people are looking at two different versions.
2. The month-end close takes days. Exports run out of the accounting system, the CRM and the webshop, tabs get copied, formulas get dragged down, totals get recalculated. Every month again, every month the same.
3. Manual refreshing. The figures in the report are a snapshot of the moment someone ran the last export. If management asks for current figures, the process starts all over again.
4. Formulas that quietly break. One shifted column in an export and a range no longer counts quite everything. Nobody notices — until a total no longer adds up and the hunt begins.
5. One person understands the file. The report leans on that one colleague who knows which tabs to update in which order. If that person is off, there is no report that month.
Anyone googling this search term mostly finds sales pages. That is why here is the real how-to: three routes, increasing in impact, starting with what you can do yourself today.
Route 1: Power Query — refresh with one click
The quickest improvement is Power Query: it comes built into Excel, costs nothing extra and replaces the copy-and-paste work with editing steps that Excel remembers and re-runs on request. The idea: you don't rebuild the report every month, you build the recipe once — after that the month-end close is a click on Refresh All.
Here's how to go about it:
- Step 1 — give your sources a fixed location. Always put the monthly exports in the same folder (for example on SharePoint or OneDrive) with consistent naming. Power Query can read an entire folder, so new monthly files slot in automatically.
- Step 2 — pull in the data via Data → Get Data. Connect to the file, the folder or straight to a database. Important: you change nothing in the source; the query only reads.
- Step 3 — record your edits once. Removing columns, filtering, fixing dates, merging tables: every action in the query editor becomes a saved step. This is exactly the work you now do by hand every month — but done once.
- Step 4 — load the result to a table or pivot table. On top of that you build your overviews and charts, just as you're used to.
- Step 5 — next month: just refresh. New export in the folder, Refresh All, done. The report recalculates itself.
What route 1 does not solve: the source stays a manual export, the result stays one file, and whoever wants to see the report has to open the file. For a report you mainly use yourself that's fine; if sharing becomes a pinch point, that's the signal for route 2.
Route 2: a dashboard that refreshes itself
If several people are looking at the same figures, a Power BI dashboard is the logical step: it refreshes itself on a schedule, everyone looks at the same current version and it works on laptop, phone and the screen on the wall. Power Query sits inside here too as the engine — so your Excel knowledge moves along with you — but a data model is added that neatly relates sources to each other, along with visualisations you can click through instead of static tabs.
The practical difference from an Excel file: you arrange access per user, the refresh runs overnight without anyone having to watch it, and "can I have the latest version?" disappears from your mailbox. You don't have to guess what something like that looks like — on our dashboards page there's a live demo cockpit you can operate yourself.
Costs: a professionally built entry-level dashboard costs roughly €3,000–€8,000, including the data model and delivery; the breakdown is in What does a Power BI dashboard cost?. On top of that you pay licences per user. If you work in the Google ecosystem, Looker Studio is the alternative — in Looker Studio vs Power BI we put both side by side. How we approach such a project you can read on Power BI dashboard development.
Route 3: connect to the source — skip the exports
The most thorough route scraps the manual export itself: a connection pulls the data directly and automatically from your accounting system, CRM or webshop, and stages it in one central place your report or dashboard reads from. That removes the last manual link — and with it the classic mistakes: the forgotten export, the wrong date range, the file that stayed on someone's desktop.
When is this worth it? Three situations: your report combines several systems that need to complement each other (revenue from the webshop alongside costs from the accounting system), you want daily or real-time current figures instead of a monthly snapshot, or the exports are error-prone and nobody wants to own them. Many systems have ready-made connectors for this; for the rest you build a connection on the API. A bespoke API integration costs roughly €1,500–€15,000 — in What does an API integration cost? you'll find where you land within that range. Curious what such an automated data flow looks like? In the Flow-Lab you build one yourself in an interactive demo.
Which route fits you?
The rule of thumb: if the report stays an Excel file and the source is an export, choose Power Query — doable today, without extra cost. If several people are looking too and you want current figures on any device, choose a dashboard. If the manual exports themselves are the problem, connect to the source. The routes don't exclude each other; if anything, they build on one another.
| Route | Solves | Indicative cost | Fits when |
|---|---|---|---|
| Power Query (in Excel) | Copy-and-paste work, formula errors, month-end close | Free — comes with Excel | Report stays in Excel, source is an export |
| Power BI dashboard | Version chaos, sharing, currency, mobile | Entry-level €3,000–€8,000 + licences (see cost article) | Several viewers, steering on current figures |
| Connection to the source | The manual exports themselves | Connector or bespoke €1,500–€15,000 (see cost article) | Several systems, current daily, error-prone exports |
A sensible path for most organisations: convert your most important report to Power Query this month and measure how much time it saves. If the need for shared, current figures still turns out to be there afterwards — and it usually does — then with those queries you've already done the groundwork for the dashboard. If you're unsure where most of your reporting time leaks away, the free Operations Scan maps it out; the report is yours to keep regardless.
In short
- A manual monthly report shows itself in version chaos, days-long closes, manual refreshing, breaking formulas and one indispensable colleague.
- Route 1: Power Query comes free with Excel — record your edits once and then refresh with a single click.
- Route 2: a Power BI dashboard (entry-level roughly €3,000–€8,000) gives everyone the same current figures, on any device; view the live demo at /dashboards.
- Route 3: a connection to the source (bespoke roughly €1,500–€15,000) scraps the manual exports themselves.
- Start small: one report to Power Query this month; the queries are immediately the groundwork for a later dashboard.
Read more
- Power BI dashboard development: approach and indicative price
- What does a Power BI dashboard cost?
- Looker Studio vs Power BI: which do you choose?
- What does an API integration cost?
- KPI dashboard examples per role
Frequently asked questions
Can I automate my monthly report without buying new software?
Yes. Power Query comes built into Excel and can connect to files, folders and databases, remember your editing steps and then refresh the report with a single click. For most monthly reports that are currently built by copying and pasting, that is the quickest improvement — without extra licences.
What is the difference between Power Query and Power BI?
Power Query is the engine that pulls in and cleans up data; it sits in both Excel and Power BI. Power BI adds a data model, interactive visualisations, automatic scheduled refresh and shared access per user on top of that. Rule of thumb: if the report stays an Excel file just for yourself, Power Query is enough; if several people are looking too and you want current figures on any device, Power BI is the logical step.
What does automating a report cost?
Power Query costs nothing extra: it comes with Excel. A professionally built Power BI dashboard costs roughly €3,000 to €8,000 for an entry-level dashboard; on top of that come licence costs per user. If you also want to replace the manual exports with a connection to the source, a bespoke API integration costs roughly €1,500 to €15,000, depending on the number of systems and the complexity.
How do I stop my automated report from breaking?
Three agreements prevent most problems: put source files in a fixed location with fixed naming, work in Excel tables instead of loose cell ranges, and don't change column names in the source without updating the query too. For connections and dashboards, monitoring belongs with that, so you notice when a refresh fails — before anyone steers on outdated figures.
Does this also work with Google Sheets instead of Excel?
Yes. In the Google ecosystem you fill the same roles with Google Sheets and Looker Studio: pull in data automatically, transform it and show it in a shareable dashboard. We work with both Microsoft and Google, so the route depends on the environment you already have — not the other way around.
Prefer these kinds of insights in your inbox?
Leave your email address and we'll add you to the list and email you as soon as the next edition on IT, automation and dashboards comes out. You can unsubscribe at any time with a single email.