Skip to content

Copilot in Excel — what it does well, and where it is wrong

Copilot is genuinely useful and confidently wrong in ways that are hard to spot. This is both halves — what to hand it, what to keep, and the verification habits that make it safe on work that matters.

Updated August 15, 2026

There are two kinds of Copilot guide. One demonstrates the impressive cases and stops. The other tells you where it fails.

This is the second kind, because the second half is what you need if you are going to use it on work that matters. We teach four Copilot courses and use it daily; both halves below come from that rather than from the launch material.

The short version: Copilot is excellent at first drafts, explanation and reshaping, and unreliable at arithmetic it cannot see, anything depending on data outside the file, and knowing when it does not know. Hand it the first set, verify the second, and it earns its licence fee.

Where it genuinely helps

Explaining a formula somebody else wrote

This is the best thing it does, and it is underrated. Select a cell containing an inherited seven-line nested formula and ask what it does. Copilot reads it and explains it in English, usually accurately, because this is a language task rather than a numeric one.

For anyone maintaining a workbook they did not build, this alone changes the job.

Getting from a blank cell to a first draft

Describing what you want in words and getting a formula back is much faster than searching for the function name. It is particularly good with the functions people find fiddly — SUMIFS argument order, nested IF structures, date arithmetic.

Treat the result as a draft by a competent colleague who has not seen your data. It is usually structurally right and needs checking against reality.

Suggesting what to look at

Ask it what stands out in a Table and it will produce a set of observations — this region is down, this category has outliers. Some of that is obvious, some is genuinely a prompt you would not have thought of. It is a starting point for analysis rather than analysis.

Reformatting and reshaping

Turning a paragraph into a table, splitting a column of names, converting a layout into a different one. Excel could always do these; Copilot removes the step of remembering how.

Where it goes wrong

It does arithmetic it cannot actually see

This is the important one. Ask Copilot to calculate something across your data and it may answer from a summary rather than from the numbers. The answer arrives with the same confidence as everything else and there is no indication of which mode it used.

Never take a number straight from Copilot’s prose. If you want a total, have it write the formula and read the formula’s result. A formula is auditable; a sentence is not.

It writes formulas that are subtly wrong

The failure mode is not a formula that errors — you would notice that. It is a formula that works on the visible rows and is wrong elsewhere: a range that stops short of the data, a MATCH without its third argument, a lookup that returns the first match when you wanted the last.

That last one is worth dwelling on. MATCH’s third argument defaults to approximate matching, so omitting the 0 gives wrong answers on unsorted data with no error at all. Copilot omits it often enough to be worth checking every time — see XLOOKUP compared with INDEX MATCH for why that default is so dangerous.

It cannot see outside the file

It does not know your fiscal year runs to March, that the Vancouver rows are double-counted since the merger, or that “Region” means something different after 2024. It will produce a confident analysis that is correct arithmetic on data you know to be wrong.

It does not say “I don’t know”

Given an ambiguous question it picks an interpretation and answers. It rarely tells you the question was ambiguous. If you ask about “sales last quarter” without defining the quarter, you get an answer to a question you did not quite ask.

Three verification habits

These are the whole skill, and they take seconds each.

  1. Read the formula, not the sentence. Make Copilot show its work as a formula in a cell. Then check the ranges: does the range cover all the data, including rows added since?
  2. Test it against something you already know. Have one number you can verify independently — last month’s total from the report you already sent — and check that Copilot’s version matches. If it does not, stop.
  3. Ask it the same question twice, differently. Inconsistent answers to equivalent questions are the clearest signal you will get that it is guessing.

Prompting notes that actually matter

  • Put the data in a Table first. Ctrl+T. Most “Copilot can’t analyse this” responses are a layout problem, not a Copilot problem, and the same rules apply as in how to use Excel — no merged cells, no blank rows, headers in row one.
  • Name your columns properly. Amount and Region give better results than Col3. It is reading the headers to work out what the data means.
  • Say what you want the output to be. “Write a formula in cell H2 that…” gets a formula. “How much did we sell?” gets a sentence, which is the less useful form.
  • One question at a time. Compound questions get compound answers where one half is right.

Who this is actually for

Copilot amplifies judgement rather than replacing it. If you can look at a formula and tell whether it is right, it makes you meaningfully faster. If you cannot, it produces confident output you have no way to check, which is worse than no tool at all.

So the honest recommendation is order-of-operations: learn the application, then add Copilot. If you are still building confidence in Excel, the Excel courses come first and Copilot afterwards is worth much more. If Excel is already solid, the $99 Copilot courses are a short, cheap way to find the edges without discovering them on live work.

Not sure which side of that line you are on? The skill assessment asks eight questions and says so.

Common questions

Do I need a paid licence to use Copilot in Excel?
Yes. Microsoft 365 Copilot is a paid add-on to a Microsoft 365 subscription, and the Excel features are not in the base plans. There is a consumer tier with limited Copilot in some plans, but the workbook-aware features described here need the add-on.
Can Copilot write formulas for me?
It can, and they are often right. The problem is that a subtly wrong formula looks exactly like a correct one — there is no error, just a wrong number. Copilot is most valuable to people who can tell the difference, which is the opposite of how it is usually marketed.
Why does Copilot say it cannot work with my data?
Most often because the data is not in a Table. Copilot's analysis features want a proper Table with headers, no merged cells and no blank rows. Pressing Ctrl+T on your range solves this more often than anything else.
Is it safe to use Copilot on confidential data?
Microsoft 365 Copilot operates inside your tenant and states that prompts and data are not used to train the underlying models. Whether that satisfies your organisation's policy is a question for whoever owns it — check before pointing it at payroll or client data rather than after.
Will Copilot replace learning Excel?
No, and the honest version is more useful than the marketing one. It raises the ceiling for people who know Excel and raises the risk for people who do not, because catching a confident wrong answer requires knowing what right looks like.