Most corporate Excel reports are still built manually. Every month someone opens last month's file, deletes the old numbers, pastes new data, and adjusts everything by hand. It works until it breaks — wrong paste, overwritten formula, new rows that destroy the totals, or the original creator leaves the company.
The solution is to replace the manual steps with formulas that pull data automatically. Claude can't rebuild the entire report in one click, but it's excellent at analysing your process and writing the exact formulas to replace each manual step.
What Claude does — maps each manual step to a formula:
- You describe the manual steps and Claude identifies which can be replaced with formulas
- Claude writes SUMIFS, XLOOKUP, FILTER, or dynamic array formulas to replace each step
- Claude redesigns the structure so source data flows into the output automatically
- Claude documents the new model so anyone on the team can maintain it
Six steps — from a manual copy-paste process to a formula-driven model that updates itself.
Document your current manual process
Write down every step you currently do — be specific. "Copy column D from the export into column F", "Delete rows where Status = Closed", "Add subtotal by region". This list is your starting point for Claude.
Show Claude the structure
Describe your source data — columns, location, update frequency — and the final report — what it shows and who uses it. Paste small samples of both if possible. The more specific, the better the formulas.
Let Claude analyse what can be automated
Claude will tell you which manual steps can be replaced with formulas and which ones still need human judgment. Review the analysis before building anything — this shapes the whole approach.
Build the model one section at a time
Start with the most important parts — subtotals or key lookups. Ask Claude for the formulas, test them with real data, then move to the next section. This prevents mistakes from compounding across the model.
Standardise your source data
Dynamic reports need clean, consistent source data. Ask Claude to recommend the best table structure, column names, and formatting rules — then apply them to your raw export every month before pasting.
Create proper documentation
Once everything works, ask Claude to write a clear maintenance guide: what to paste each month and where, what the main formulas do, and what to check if something looks wrong. Save it inside the workbook.
Copy these prompts into Claude. Replace the bracketed placeholders with your actual process and structure.
Analysis prompt
Formula replacement prompt
Documentation prompt
Dynamic arrays in Excel 365: if you're on Excel 365, Claude will likely suggest FILTER, UNIQUE, or SORT instead of manual SUMIFS for some steps. These functions return multiple results automatically and eliminate entire categories of manual work. Tell Claude your Excel version and it will choose the right approach.