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

Turn a week of rough notes into a weekly update with ChatGPT

On Hacker News, one worker said his company's weekly update format is a pain, so he keeps notes through the week and sends them to a custom GPT that writes the update in the required template. He spends about ten minutes tidying it, saving a couple of hours.

Full guide →
Source: Hacker News

Write a tricky Excel formula with ChatGPT

One user had ChatGPT write an Excel formula for a billing file he fills in every month, saving him two to three hours of manual work each time.

Source: X @BOOSTkdv

Turn client calls into filed notes and action items with Notion AI

A Notion consultant uses Notion AI meeting notes on client calls. It transcribes the call and produces a summary of what was discussed and decided, with action items and owners, filed straight into the consultant's own meetings database, with no outside bot joining the call.

Full guide →

Reference: X @Bouje99