Rebuild an awkward inherited spreadsheet report with Copilot in Excel

← All use cases / Work

ToolMicrosoft Copilot

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.

Prompt to copy
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

  1. Save a copy of the workbook first, so the original report stays safe whatever happens.
  2. 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.
  3. Ask Copilot to build the new version on a separate tab rather than changing the existing sheets.
  4. Compare a few key totals in the new tab with the old report and the raw data before you rely on it.
  5. 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

Read the full guide →

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.

Full guide →

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.

Source: X @Meedeey

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.

Full guide →

Reference: X @Bouje99