Copilot in Excel: What It Can and Cannot Do
Share
Copilot in Excel is the feature people expect most from and complain about most. Both reactions come from the same misunderstanding about what it is doing.
Direct answer
Copilot in Excel is reliable for writing and explaining formulas, and for summarising a clean, well-structured table. It is unreliable for analysing messy data, and it gives the same confident answer whether it understood your table or not.
Based on the AI and Automation Practitioner standard (ST1512), a Level 4 apprenticeship for people already in work. Funding rules described here are the England position and are set by the funding body.
Potentially free for you
The learner never pays. Employers potentially pay nothing either, and where the apprentice is aged 16 to 24 the government funds the whole cost even if the employer does not pay the levy.
Closing any gap. A levy transfer from another employer covers 100% of the cost. Unused transfer allowance is not carried forward at the end of the tax year, and it must be agreed before the apprentice starts.
From 1 August 2026 the government stopped adding the 10% top-up to new funds entering levy accounts, so there is less in the system than there was.
Part of our guide to Microsoft 365 Copilot Training for UK Teams.
What it does genuinely well
Writing a formula from a description. Tell it what you want calculated and it produces working syntax, including the nested functions most people avoid.
Explaining someone else's formula. Paste in a wall of nested IF statements and get a plain English account of what it does.
Summarising a clean table. Where your data has proper headers, consistent types and no merged cells, the summaries are accurate.
Suggesting charts and highlighting. Useful for a first pass, and you will change the result, which is fine.
Where it quietly gets things wrong
Merged cells. They break its understanding of the table structure and it does not tell you. It answers anyway.
Inconsistent data types. A column with numbers stored as text produces confident totals that are simply wrong.
Blank rows in the middle of a range. It frequently treats these as the end of the data and analyses only part of your table.
Anything requiring domain judgement. It will happily average a column that should never be averaged, because nothing in the data says so.
The prerequisites nobody mentions
Your file needs to be saved in OneDrive or SharePoint. Copilot does not work on a local file, which surprises people mid-task.
Data needs to be formatted as a table. Select the range and press Ctrl and T. This single step improves results more than any prompt.
AutoSave has to be on. It is a condition rather than a preference for several features.
Very large workbooks hit limits. If nothing happens on a big file, that is usually why rather than a bug.
There is more on this in our guide to Microsoft 365 Copilot training for UK teams.
A checking habit for Excel specifically
Check the row count. Ask how many rows it analysed and compare against what you know is there. This catches the blank-row problem instantly.
Spot-check one number by hand. One manual calculation catches the type-mismatch problem that no amount of rereading will.
Ask for the formula rather than the answer. A visible formula can be checked. A number cannot.
Never paste a Copilot figure into a report without doing at least one of the above.
Date and currency formats in UK workbooks
Copilot follows your file's regional settings for display, and its own assumptions when it interprets a description.
Ask for a date calculation in words and check what it produced. American month-first interpretation of an ambiguous date is a real and recurring error.
Financial year assumptions are the other trap. A UK financial year starting in April is not what a generated quarterly formula will assume by default.
State the convention in the prompt. UK dates, sterling, financial year starting April, and then check the first result by hand.
What to use it for this week
The formula you always look up. Stop searching and describe it instead. This alone repays the licence for most people.
The inherited spreadsheet nobody understands. Ask it to explain the calculations before you change anything.
The weekly summary you write by hand from a clean table. Draft it with Copilot, check the numbers, edit the wording.
Do not start with the messy operational workbook. That is where it fails and where people conclude the tool does not work.
Key takeaways
- Reliable for formulas and clean tables, unreliable for messy data.
- Format your range as a table with Ctrl and T before anything else.
- Merged cells and mid-table blank rows break it silently.
- Ask for the formula, not the answer, so you can check it.
- Files must be in OneDrive or SharePoint with AutoSave on.
Frequently asked questions
Can Copilot analyse data in Excel?
It analyses clean, well-structured tables reliably. On messy data with merged cells, mixed types or blank rows it produces confident answers that can be wrong.
Why is Copilot not working in my Excel file?
Most often the file is saved locally rather than in OneDrive or SharePoint, AutoSave is off, or the range is not formatted as a table.
Is Copilot good at Excel formulas?
Yes. Writing formulas from a description and explaining existing ones are the two things it does most reliably.
Does Copilot make mistakes in Excel?
Yes, and it does not flag them. Merged cells, numbers stored as text and blank rows in a range all produce wrong answers delivered confidently.
How do I check Copilot's Excel output?
Ask how many rows it analysed, spot-check one number by hand, and ask for the formula rather than the answer.
Do I need to format data as a table?
It is the single most effective preparation step. Select the range, press Ctrl and T, and results improve immediately.
Can Copilot work on a local Excel file?
No. The file needs to be in OneDrive or SharePoint. This catches people out mid-task more than any other limitation.
What should I try first?
The formula you always have to look up. Describing it instead of searching for it repays the licence for most people on its own.
The funded route, in figures
Figures are the England position. Levy-paying employers usually draw the whole cost from the levy they already contribute, so they pay nothing extra. Employers below the £3 million pay bill threshold pay nothing for an apprentice aged 16 to 24. For an older apprentice the government funds 95 per cent, and a levy transfer from a larger employer can cover the rest. Eligibility and funding rules are set by the funding body and can change.
Where to go next
If you want Excel and AI skills built into an assessed qualification using your own workbooks, the funded Level 4 route covers it.
We help UK employers and employees access government-funded AI training. Training is delivered by third-party providers. We do not guarantee a place, a qualification, a job or any particular outcome. Eligibility and funding rules are set by the funding body and can change. Always check the current position before you commit. See our disclaimer.