You’ve got a 43,000-row CRM export open, the headers are a mess, and someone in finance wants “a quick breakdown by region” before the 4pm call. This is the exact moment Microsoft thinks you’ll reach for Copilot.
Sometimes that instinct pays off. Sometimes you spend twelve minutes rewording a prompt and end up writing the SUMIFS yourself anyway. Both outcomes are normal, and knowing which one you’re heading for is most of the battle.
What Copilot for Excel actually is
Copilot in Excel is the AI assistant baked into the Microsoft 365 spreadsheet app. It sits in the ribbon and opens a pane on the right. From there it reads your data, drafts formulas, builds PivotTables, applies conditional formatting, and in newer builds runs Python underneath for genuine statistical work.
Two hard limits are worth knowing before you start:
- The file has to live in OneDrive or SharePoint and be saved as .xlsx. A copy sitting on your desktop won’t be recognised.
- It works far better on data formatted as an actual Excel Table (Ctrl+T). Loose ranges and stray blank rows confuse it badly.
It also isn’t a cell-level editor in the way people expect. It rarely writes into individual cells the way you would. Instead it suggests helper columns, proposes formulas, and answers questions about the data. Picture a fast junior analyst sitting beside you who needs precise instructions and won’t tell you when it’s guessing.
Where it genuinely earns its keep
Formulas you’d otherwise Google
Ask it to “add a column that flags orders more than 30 days past due” and Copilot drafts the formula, explains how it works, and inserts it as a new column. For nested IFs, XLOOKUPs across two sheets, or tidying up concatenated names with TEXTJOIN, that alone can save 10 to 15 minutes a day for anyone who lives in spreadsheets.
PivotTables without the field dragging
Describe the summary you want and Copilot proposes a PivotTable: region down the rows, quarter across the columns, sum of revenue in the values. You still drag fields to tidy the formatting, but the scaffolding appears in seconds. If you rebuild the same pivot every Monday morning, this is the win that justifies the licence.
Cleanup and formatting at speed
“Highlight every row where margin falls below 12%” returns a conditional formatting rule, applied and explained. Colour scales, duplicate detection, blank cells, date inconsistencies: all quick and usually correct on well-structured data.
Analysis with Python
On plans that include it, Copilot can run Python over your range for correlation, regression, forecasting or clustering. It shows the code it ran, which matters if you ever have to defend a number to an auditor or a sceptical CFO.
Where it still stumbles
An honest feature list matters more than a marketing one, so here are the walls people hit most often.
- Messy data beats it. Merged cells, blank rows mid-table, dates stored three different ways, headers spanning two rows. Copilot reads all of that as noise and produces confident nonsense. Clean first, prompt second.
- Multi-sheet logic. It manages a second sheet fine. Ask it to reconcile five linked workbooks and the suggestions turn vague fast.
- It won’t restructure your sheet. Deleting columns, reshuffling layout, fixing a badly designed template: still your job, still by hand.
- Wrong answers delivered with total certainty. It will happily reference the wrong column and present it as fact. Check the first few rows of any output before you trust the rest.
- Cost. This is a per-seat add-on on top of your existing Microsoft 365 subscription, and it’s hard to justify for occasional users. Reading through what each Microsoft Copilot plan actually costs before you request a licence will save you an awkward conversation with IT.
How to prompt it so it stops guessing
Name the columns, not the concept
“Analyse my sales data” gets you a generic summary nobody asked for. “In the Revenue column, calculate month-over-month growth grouped by Region, and put the result in a new column called MoM %” gets you something usable.
One task per prompt
Long chains of requests lose the thread somewhere around step three. Break the job into single steps and check each output as you go.
State the output format you want
“Give me a single formula I can copy” and “build me a summary table” produce completely different results from the same data. Say which one you mean.
Ask it to explain itself
“Walk me through what this formula does line by line” is the prompt most people skip. It teaches you the logic and catches the errors in one pass.
Where it fits next to the rest of Copilot
Excel is only one corner of a much bigger suite, and the assistant behaves quite differently depending on where it’s sitting. The breakdown of what Copilot really does inside Word, Outlook and Teams is a useful contrast, because drafting an email and auditing a spreadsheet are very different problems. On the desktop itself, Copilot for Windows is a separate product again with its own quirks.
If you’re working out where your AI budget goes, comparisons help. Copilot vs ChatGPT is worth reading if you’re weighing up spreadsheet grunt work, since ChatGPT is often better at explaining a formula you paste in while Copilot’s advantage is simply being inside the file already. And Copilot vs Claude is the better comparison if you tend to think out loud before you build anything.
A realistic Monday morning run
Here’s how a typical session actually plays out with Copilot for Excel. At 9:10am the export lands. Convert to a table, delete the blank rows: 90 seconds. Then ask for total revenue by region and month for 2025, flagging any region down more than 10% year over year. Copilot builds the pivot and the conditional format in about 40 seconds, and you correct two things. Next prompt: create a column flagging accounts with no order in 90 days. Check the first five rows, confirm, done.
That’s perhaps 25 minutes the tool just handed back. And you spend it on the part it cannot touch: working out why the Nordics dropped off in Q3.
A sensible way to trial it
Don’t switch on 200 seats and hope for the best. Pick three people who genuinely live in spreadsheets, give them two weeks, and ask them to log every prompt that worked and every one that wasted their time. That log tells you more than any demo. If those three are saving 20 to 30 minutes a day on real work, roll it out. If they’re mostly fighting formatting rules on messy data, spend the money on cleaning up your exports instead.
The mechanical parts of spreadsheet work, the lookups and the pivots and the conditional formatting, are exactly what this tool is built for, and on tidy tables it delivers. The judgement calls stay with you. Which is probably how you’d want it.

