Analysing data with Copilot in Excel

Copilot in Excel takes a question about your spreadsheet in ordinary English and either answers it or changes the sheet for you. Excel is where Copilot has moved fastest through 2026, and where checking its work matters most, because a wrong paragraph reads oddly and a wrong number gets pasted into a board pack.

What it can do now

There are two ways to use it, and the difference is whether your workbook changes. Chat answers questions about the data (“which region is behind plan?”) and explains formulas, including the inherited monsters someone wrote in 2019. Edit changes the workbook itself: cleaning columns, adding calculated fields, building summaries. To switch, open Copilot in Excel and look for the “Let’s edit together” message, or choose Edit with Copilot from the tools menu.

Underneath, it can now write and run Python, a programming language used for serious data work, to do what formulas do awkwardly: reshaping messy data, statistics, and the kind of chart the built-in set cannot draw. Since August 2026 that works in Edit too, with the results landing in your workbook rather than in a chat reply. You describe the outcome in plain language, and Microsoft says the Python runs in “a secure container on the Microsoft Cloud” with no network access and no access to your computer.

Excel is also the clearest example of Copilot’s model choice. Both OpenAI’s and Anthropic’s models can be selected where your organisation has switched that on, so a colleague’s Copilot may not behave like yours on the same file.

Make your data legible first

Copilot does far better on a proper Excel table, meaning headers on the first row, one record per row and no merged cells, than on a spreadsheet laid out to be looked at. Select your data and use Format as Table before you start. Those two minutes improve everything that comes after them. If one tab holds three tables and a block of notes, split it first. That is not a Copilot quirk, it is what any analyst would ask of the file.

Check the numbers like you mean it

Read every formula it writes before trusting a result built on it, because Copilot can misread what a messy column means and will not mention that it has. Spot-check totals against a manual calculation on a handful of rows. Keep a copy of the original data before letting it edit anything.

September 2026 made the checking easier. Copilot’s reply now links to each new sheet, table and range it changed, so you can jump straight to them, and Review, then Show Changes, marks which edits were Copilot’s. Use both every time: they tell you where to look, not whether the numbers are right.

Copilot is quick at the mechanics of analysis. Deciding what question the data should answer, and noticing when an answer is plausible but wrong, is still your job.

The deeper trade-off: the better it gets at multi-step analysis, the easier it becomes to present work you cannot personally explain. If Copilot’s Python produced the chart, be ready to say what it did. The same rule as with AI coding help: don’t ship what you can’t defend.