Automate my monthly Excel report so it refreshes in minutes
Redesigns a manual monthly spreadsheet report into a repeatable workflow with Power Query, Tables, and a checklist, staying in Excel.
The prompt
Help me automate a monthly report I build by hand in Excel, so next month takes minutes instead of hours.
The report and who gets it: [e.g. "monthly sales pack for regional managers"]
Current steps, in order: [LIST everything - exports, copy-paste, lookups, pivots, formatting, manual fixes, emailing - with rough time per step]
Data sources: [systems, file names, where the files land each month]
Key formulas or pivots: [PASTE]
Constraints: [Excel 365 with Power Query? Can I use macros or Power Automate? Shared drive or SharePoint?]
Do this:
1. Spot what's repeatable vs. what needs judgment each month.
2. Redesign the workflow:
- Source files dropped in one folder; Power Query to import, clean, combine, and append them (replacing copy-paste and manual cleaning).
- Data loaded into Excel Tables so formulas and pivots expand automatically.
- One Parameters cell for the report month that drives all formulas and titles.
- Pivots and charts set to refresh with Data > Refresh All.
- Manual adjustments moved to a logged Adjustments table with who, what, and why.
3. Add built-in checks: row counts vs. source, control totals, and a red flag if a check fails.
4. Suggest what could go further (Power Automate for distribution, Office Scripts or a macro for formatting) and when it isn't worth it.
Return:
- A before/after step table: Step | Today | New method | Time today | Time after.
- Power Query steps in plain language for each source.
- A monthly run checklist (under 10 steps).
- Risks: what breaks if a source file changes format, and how I'll know.
Use my time estimates; don't invent savings figures.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- e.g. "monthly sales pack for regional managers"
- systems, file names, where the files land each month
- PASTE
How this prompt is built
This prompt follows the RTF structure (Role, Task, Format). RTF tells the model who to be, what to do and what shape the answer should take. It is the quickest structure that still gives noticeably better results than a bare request.
Related prompts
- Compare performance against targets and find the biggest gapsCompares actuals against targets for every KPI, sizes each gap, and ranks the gaps that matter most so you know where to focus.
- Check whether my KPIs measure activity or real value, and fix themSorts your KPIs into activity, output, and outcome measures, flags vanity metrics, and redesigns the set around the outcomes that matter.
- Write a chart specification I can build in Excel or Power BIA build-ready chart spec covering chart type, data mapping, scale, emphasis, labels, and accessibility for the message you need to show.
- Find missing values in my data and explain their impactProfiles blanks and placeholder values by column, spots patterns in what's missing, and recommends how to handle each gap.