Automate Excel reports: Power Query, AI or a service?
In short. To automate Excel reports, save the repeated steps as a Power Query that you refresh, let formulas do the calculations and, if you want a written summary, let AI draft it from the finished figures for a person to check. Doing it yourself fits stable files. A service fits when the format is fixed and the report eats hours. Ours starts from 290 € of set-up plus from 90 € a month, and with the assumptions below it pays above about four hours a month.
Almost every small business has one spreadsheet report that someone rebuilds every month: copy the files, clean them, recalculate, write two paragraphs, send. It is dull, and it is exactly the kind of work that can be automated. This guide shows which parts to automate with which tool, where AI helps and where it should stay out, and how to check whether paying for it makes sense.
What can you automate in an Excel report?
You can automate most parts, but not with the same tool. Split the report into six parts and give each its own tool and its own checker.
| Part of the report | Best tool | Who checks |
|---|---|---|
| Collecting and combining the source files | Power Query | The totals against the source, once a month |
| Cleaning: dates, names, duplicates | Power Query steps | A sample of rows |
| Calculations | Formulas or Power Query, not AI | Three lines recalculated by hand |
| Tables and charts | An Excel template | A glance at the finished file |
| Written summary | AI, from the finished figures | A person reads every sentence |
| Sending to clients | A person | The recipient list |
The order matters. Figures first, words after: a model that writes a summary from numbers that are already correct can only get the wording wrong, and wording is easy to check.
How does Power Query automate the repeated steps?
Power Query records your preparation steps and repeats them. According to Microsoft Learn, it is a data transformation and preparation engine with a graphical editor, and it lets you save the transformation steps as a repeatable query that you can refresh to get up-to-date data. The queries do not modify the source data. You can use it without writing code, because the editor creates the underlying M code for each step, and Excel for Windows and Excel for Mac include Power Query Desktop. Refreshing can be done by hand, on a schedule in some products such as Power BI, or programmatically through the Excel object model.
For a monthly report this means: drop the new files into the same folder, press refresh, and the cleaning and combining happen again exactly as before. The limits are also clear. Power Query shapes data. It does not decide which figures matter, and it does not write the commentary.
Where does AI help with a report, and where not?
AI helps with words, and it should stay away from arithmetic. Let code compute the figures, then give the finished table to a model and ask for a short summary and a list of anything unusual, such as a total that moved sharply or a missing month. A person then reads the text against the table.
This follows the advice in Anthropic’s guide to building agents: find the simplest solution possible, and note that workflows suit well-defined tasks because they are predictable and consistent. A monthly report is such a task. A fixed workflow of query, formulas, template and one model call is easier to test than an agent that decides its own steps. If a second model checks the summary, test it with planted errors first.
Should you do it yourself, use Power Query or order a service?
Pick by how stable the source is and how much time the report takes.
| Your situation | Sensible choice |
|---|---|
| The report takes an hour or two and the files are always the same | Do it yourself with formulas and a saved Power Query |
| The files are stable, the report takes several hours, nobody has time to maintain it | A service with fixed calculations and a monthly issue |
| The source format changes every month | Fix the format first, then automate |
| You also need data pulled from PDFs | Start with PDFs into checked tables |
When does paying for report automation pay back?
It pays back when the hours saved are worth more than the monthly fee plus the checking you still do. The formula is simple: monthly saving equals hours of manual work times your hourly cost, minus the hour or so you still spend checking, minus the monthly fee.
Here is an example with invented numbers, to show the method and not a result we measured. The report takes 8 hours a month and your time is worth 30 € an hour, so 240 €. After automation you spend 1 hour checking, which is 30 €, so you save 210 €. Subtract the 90 € monthly fee and the net saving is 120 € a month. The 290 € set-up is repaid in about 2.4 months.
Now the break-even. With the same assumptions, a report of H hours saves 30 × H, minus 30 for checking, minus 90 for the fee. That is above zero once H passes 4. At 2 hours a month, 60 − 30 − 90 is negative: the automation does not pay. Change the figures to your own, and be honest about the checking hour.
What does the service cover?
Our regular reports from your spreadsheets start from 290 € of set-up plus from 90 € a month. Set-up covers one source format, the calculations we agree with you, a trial issue and a handover. Each month you receive one issue with human review, and up to 30 minutes of maintenance is included, without unlimited support. Sources are traceable and incomplete data is flagged. It is not an accounting audit, it makes no forecast and it does not send reports to your clients automatically.
What should you prepare?
Bring five things, and the set-up goes faster:
- The last report you produced, as the target.
- The source files in their real format, and where they come from.
- A line for each figure explaining how it is calculated.
- Who reads the report and by which day it must be ready.
- Who checks it, and what they look at first.
Which reports are the best candidates?
Look for reports that repeat on a fixed day, use the same source files and have a reader who acts on them: a monthly sales summary for the owner, a stock list for a supplier, a cost overview for the accountant. Skip the report that is rebuilt from scratch every time because the question keeps changing. If you can describe the report in one sentence and show last month’s version, it is ready to be automated, and the work that remains is mostly agreeing on the definitions.
When is automation not worth it yet?
When the report is small, when the source format keeps changing, or when nobody can say how a figure is built. Automating a report that nobody trusts only makes the doubt faster. Fix the definitions on paper first. If you are unsure which task to automate first, our five-point score helps you choose, and the five-step implementation plan shows how to start small.
How does Castel Studio automate reports?
We start from your last real report, fix the source format and the calculations, build the smallest version and run a trial issue beside your manual one. We compare the two, you approve, and then the monthly issue runs with a person checking it. Anything we cannot calculate from the source is flagged instead of guessed.
Send us your last report and the files behind it. We reply in writing within 48 hours.
Questions
How can I automate Excel reports without coding?
Use Power Query, which is built into Excel for Windows and Mac. You connect to the source files, apply the cleaning steps in a visual editor and save them as a query that you refresh each month. The source data is not modified.
Can AI write my Excel report?
AI is useful for the written summary and for flagging unusual values, but the figures should come from formulas or a query, not from a model. A person should read every sentence before the report goes out.
How much does it cost to automate a monthly Excel report?
At Castel Studio regular reports from your spreadsheets start from 290 € of set-up plus from 90 € a month. That includes one source format, agreed calculations, a trial issue, one monthly issue, human review and up to 30 minutes of maintenance.
When is it not worth paying for report automation?
When the report takes only a couple of hours a month, when the file format changes every time, or when nobody can say how each figure is calculated. With the assumptions in this guide the break-even is about four hours a month.