# 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.

- Topic: Data & Analysis
- Framework: RTF
- Level: Advanced
- Tags: Data & Analytics, Meetings, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/automate-my-monthly-excel-report-so-it-refreshes-in-minutes

## Prompt

```text
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.
```

## What to fill in

- e.g. "monthly sales pack for regional managers"
- systems, file names, where the files land each month
- PASTE

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.