Someone who inherited a report built on pivot tables, which was a pain to update because the pivots could move when refreshed, asked Copilot in Excel to redo it. Even with a rough, loosely worded request, Copilot worked out how to read the pivots and rebuilt the report on a separate tab, which they said came out flawlessly.
I've inherited this workbook. The report on the [SHEET NAME] tab is built from pivot tables on [SHEET NAMES], and it's hard to update because [WHAT GOES WRONG, E.G. THE PIVOTS MOVE WHEN I REFRESH]. Please rebuild the report on a new tab called [NEW TAB NAME] so it shows [WHAT THE REPORT NEEDS TO SHOW, E.G. MONTHLY TOTALS BY REGION AND PRODUCT]. Use formulas that update when the source data changes, keep the original tabs untouched, and list which cells and ranges you used so I can check the figures against the old report.
Replace the [PLACEHOLDERS] with your own details before you send it.
How to try it
- Save a copy of the workbook first, so the original report stays safe whatever happens.
- Open the copy in Excel with Copilot and describe, in plain words, what the report needs to show and what’s annoying about the current version.
- Ask Copilot to build the new version on a separate tab rather than changing the existing sheets.
- Compare a few key totals in the new tab with the old report and the raw data before you rely on it.
- Refresh the source data once and check the new report still updates correctly.
Why this works
Inherited spreadsheets are often a tangle of someone else’s decisions: pivot tables feeding other pivot tables, references that break when a range moves, and no notes on why anything was done. Rebuilding one by hand means first working out how it all fits together, which can take longer than the rebuild itself. The person who shared this found that a rough description of the end result was enough for Copilot to dig into the pivots and produce a cleaner version on its own tab. If you’d like to understand the formulas it uses, our guide to learning Excel formulas with AI is a good next step.
Tips to get a better result
- Describe the finished report, not the steps: ‘totals by month and region, newest month first’ gives Copilot a clear target.
- Ask it to explain each new formula in one line, so you or a colleague can maintain the report later.
- Keep the old report for a month or two and compare the two after each update.
- If you use Copilot in other Office apps too, our guide to Copilot in Word and Outlook covers the basics.
Common mistakes
- Letting it change the original sheets, which makes it hard to check whether the new figures are right.
- Skipping the spot-check: one wrong range can quietly leave out a whole month of data.
- Working on a file with confidential data without checking your organisation’s rules on Copilot.
Try a variation
Here’s a variation of our own to try next:
Look at the report on the [SHEET NAME] tab and explain, step by step, how it's built: where each figure comes from, which tabs feed it and anything that's likely to break when the data changes.
Related
- AI for work and business: our hub of ideas
- Learn Excel formulas with AI
- Use Copilot in Word and Outlook
More work use cases
Find where a rulebook actually says something with NotebookLM
A Hacker News commenter said they use Google's NotebookLM to answer 'where does it say that?' questions about ISO technical standards, traffic codes and other documents. You upload the documents and ask questions, and the answers are tied to references in those documents.
Using only the documents in this notebook, answer this question: [YOUR QUESTION, E.G. WHAT DOES THE CODE SAY ABOUT PARKING NEAR A JUNCTION?]. Quote the exact wording that answers it, and give the section or clause number for each quote. If the documents don't answer the question, say so clearly rather than guessing, and point me to the closest related section.
Name your team for a work competition and write the announcements with Copilot
As her team's wellness ambassador for an organisation-wide competition, one employee asked Copilot for team name ideas. The team picked 'From Excel to Excellent', and she used Copilot to write all the announcement posts too.
My team at [ORGANISATION] is entering [COMPETITION OR CHALLENGE]. We work in [DEPARTMENT OR WHAT WE DO] and like [IN-JOKES, TOOLS, THEMES]. Suggest [NUMBER] fun, work-appropriate team names with a one-line reason for each. Then write a short, upbeat announcement post for [CHANNEL, E.G. TEAMS OR THE INTRANET] revealing the winning name and asking people to join in.
Sort a messy list of expenses into tax categories with ChatGPT
Someone preparing a client's tax return gave ChatGPT the client's list of expenses, including a few items they didn't recognise, and it sorted them into the broad categories needed for the return. They said it saved them hours.
Here is a list of business expenses for [TYPE OF BUSINESS] in [COUNTRY] for [TAX YEAR]: [PASTE LIST WITH DATES, SUPPLIERS AND AMOUNTS]. Sort each one into these categories: [YOUR CATEGORIES, OR 'the usual categories for a self-assessment return']. Put anything you're unsure about, or that might be personal rather than business, in a separate 'check' list with a short reason, and give me totals per category.
Reference: X @Bouje99