Copilot in Excel can add formula columns, build PivotTables and charts, sort, filter and highlight, summarise trends and outliers, pull in data from other workbooks and, in its editing mode, change the workbook directly while you watch. What it can't do is guarantee the maths: Microsoft's own FAQ advises against using it for decisions in sensitive areas such as finance.
One change to know first: the =COPILOT() worksheet function was retired on 14 September 2026. You can't write new COPILOT formulas, and old results survive only as cached values; a cell that recalculates now shows #NAME?. Everything described here happens in the Copilot pane instead, which you open from the Copilot icon at the bottom right of Excel.
The three modes, and which one to start in
The pane now works in three modes, and choosing the right one is most of the skill. Microsoft's getting-started page describes them like this:
| Mode | What it does | Use it for |
|---|---|---|
| Chat only | Analyses the workbook and answers without changing anything | Questions, first looks at a new file, anything you're unsure about |
| Plan | Writes out the steps it would take, for you to review before it starts | Multi-step jobs such as merging sheets or building a report |
| Allow editing | Works directly in the workbook, showing its reasoning and making changes live | Jobs you've already checked in Plan, or small, easily undone changes |
Edit, plan and chat all work on Windows, Mac and the web. On iPad you get chat and plan, on iPhone and Android chat only, with editing rolling out to iPad and iPhone. For editing, Excel's calculation option must be set to Automatic. If something goes wrong, you can undo or open earlier versions of the file, which is one reason to keep business workbooks in OneDrive or SharePoint, where version history lives.
Microsoft's pages don't agree on AutoSave. The subscription FAQ for home plans says Copilot in Excel needs AutoSave on and the file saved to OneDrive, while the newer Copilot in Excel tips page says it runs with AutoSave on or off, and suggests switching AutoSave off when you want to try Copilot's changes without saving them. In practice: save the workbook to OneDrive or SharePoint before you start; if Copilot is unavailable, turn AutoSave on; and when experimenting on an important file, work on a copy.
A pet shop's sales file, from question to formula
For a worked case, picture an independent pet shop with a year of till exports in one workbook: 5,800 rows with date, product, category, quantity, sale price and unit cost. Sales for the year come to about $412,000. The owner wants three things: which categories make the most margin, how that moves by month, and a margin column to use in future.
Step 1: ask first, in Chat only
Which five categories made the most gross profit last year,
and what was each one's share of total sales?Dry dog food led with $41,300 gross profit on 31% of sales, followed by cat food ($27,900, 22%), treats ($19,400, 9%), accessories ($17,800, 11%) and small-animal bedding ($6,200, 5%).
A useful first answer, and a quick one to check: a filter on the category column and a sum of (price minus cost) times quantity for dry dog food matched Copilot's figure to the dollar. Checking one line of an answer like this takes two minutes and tells you whether to trust the rest.
Step 2: a PivotTable, via Plan
Asked for "a PivotTable of gross profit by category and month, on a new sheet, with a line chart", Plan mode listed its steps: add a gross profit column, create the PivotTable on a new sheet, group dates by month, add the chart. That last step revealed a snag worth catching early: the dates in the till export were text, not real dates, so grouping by month would have failed or produced nonsense. The owner asked Copilot to convert the date column first, then approved the plan. Plan mode earns its place for exactly this kind of catch.
Step 3: the margin column, where it went wrong
In Allow editing mode, the owner asked Copilot to "add a margin % column". It added one, formatted neatly, with this formula in each row:
=([@[Sale price]]-[@[Unit cost]])/[@[Unit cost]]That's markup, not margin. Margin divides by the sale price. For a bag of dog food selling at $50 that costs $35, the column showed 43% when the true margin is 30%. Across the shop it made every category look more profitable than it was, by 13 points on dry dog food and by more than 50 points on high-margin lines such as treats, and the owner nearly based a discount decision on it.
How it surfaced: the owner checked one row by hand, the same two-minute habit from step 1. The fix was to say what you mean: "Margin % = (sale price − unit cost) ÷ sale price". Copilot rewrote the column correctly. The general lesson is that Copilot doesn't know your business definitions unless you give them, and financial terms such as margin, markup, gross and net are easy to swap. Why AI is bad at maths explains why fluent answers hide errors like this.
What it does well with numbers
- Formula columns and rows. Describe the calculation and it writes the formula across the table: a total per job, a days-overdue column, a running total. You can read the formula, which makes checking fast.
- PivotTables and charts. Built with Excel's own features, so they stay editable and linked to the source data rather than pasted as pictures.
- Sorting, filtering and highlighting. "Highlight every order over $500 that hasn't been invoiced" is a quick, low-risk job.
- Spotting trends and outliers. Good for pointing you at the month or product to look at, as long as you then look.
- Tidying and restructuring. Splitting columns, merging sheets, converting text dates, applying data validation.
- Pulling in data. From other workbooks in OneDrive, SharePoint or on your computer, and from the web with citations to the sources it used.
- Repeatable routines. Custom skills let you define how Copilot should do a recurring process, such as a monthly tidy of an export.
What it can't do, or can't be trusted to do alone
- Know your definitions. Margin versus markup, which costs are included, what counts as a "repeat customer". State them in the prompt.
- Explain causes. Asked why March sales fell, it will offer reasons, but the workbook doesn't record weather, roadworks or a competitor's sale. Treat any "why" as a guess to test.
- Rescue badly structured data. Merged cells, several tables on one sheet, totals typed into the middle of data, and dates stored as text all confuse it. A plain table with one header row works best.
- Replace a check. Microsoft's FAQ says Copilot can make mistakes, misinterpret information or produce inaccurate results, and advises against using it for decisions in sensitive areas such as finance, legal or medical topics.
- Work fully on a phone. Android is chat only; editing belongs on a computer.
Preparing a workbook so Copilot's answers can be checked
Most of Copilot's mistakes in the examples here came from the workbook, not the model: text dates, missing units, undefined terms. Twenty minutes of preparation on a file you'll use every month removes most of them.
- Make the data a proper table. Select it and press Ctrl+T, with one header row, no merged cells, no subtotals typed in between rows, and one table per sheet.
- Put units in the headers. "Width (cm)", "Rate ($ per m²)", "Price inc. glass ($)". Copilot reads headers, so units there travel into every formula it writes.
- Add a Definitions sheet and tell Copilot to use it: "Use the definitions on the Definitions sheet for any calculation."
- Keep inputs and outputs apart. Raw exports on one sheet, Copilot's additions on another, so a bad edit never touches the source.
The pet shop's Definitions sheet, filled in:
| Term | Definition used in this workbook |
|---|---|
| Gross profit | (Sale price − Unit cost) × Quantity, excluding delivery charges |
| Margin % | (Sale price − Unit cost) ÷ Sale price |
| Markup % | (Sale price − Unit cost) ÷ Unit cost; not used in reports |
| Month | Calendar month of the sale date, not the till's week number |
| Repeat customer | Loyalty card with two or more purchases in 90 days |
Once that sheet existed, the margin mistake from earlier didn't recur, and answers became quicker to check because every figure pointed back to a written rule.
What to do with old COPILOT() formulas
If anyone in your business used the preview COPILOT function before 14 September 2026, those workbooks need a small repair. Microsoft's COPILOT function page says existing results stay as cached values, but any cell that recalculates returns #NAME? because Excel no longer recognises the function.
An illustrative garden centre had used it to tag 900 product descriptions with a category, such as "bulbs", "tools" or "compost", for its online shop feed. The tags were still visible, but editing a description or forcing a full recalculation turned that row into #NAME?, and the feed export broke on the first one. The repair took ten minutes:
- Select the column of COPILOT results and copy it.
- Paste it back as values, so the categories become plain text that can't recalculate.
- For new products, use the pane instead: select the new rows and ask Copilot to "add a category from this list: bulbs, tools, compost, seeds, pots, furniture, gifts; use only these words".
- Spot-check twenty categorised rows before the next feed export.
The closed list in step 3 matters. Without it, Copilot invents near-duplicates such as "garden tools" alongside "tools", which quietly breaks the shop's filters.
Python through Copilot, for questions a formula can't answer
Copilot can now use Python in Excel as part of its work; Microsoft's Excel team describes it applying Python techniques while it edits, producing Python formulas that sit in the workbook and can be refreshed. You don't write any code yourself. That's useful for questions such as forecasting or finding relationships between columns.
An illustrative optician's practice asked for a forecast of frame sales for the next quarter from three years of monthly figures. The answer came back as a chart with a forecast line and a range around it, plus the Python formula that produced it. Two things to check before using such a forecast. First, the range: with only 36 monthly data points, it was wide, roughly plus or minus 20%, and the honest reading is "similar to last year, give or take". Second, known events: the forecast couldn't know that a large employer nearby had just changed its eyecare scheme, which the practice manager expected to matter more than any trend. Treat Python output as a careful starting point, not a prediction.
Three more jobs, and the fix each needed
A dry cleaner merging two counters' sheets. Asked to combine the weekly takings sheets from two branches and remove duplicates, Plan mode proposed removing duplicate rows by customer name. That would have deleted genuine repeat customers who visited both branches. The owner changed it to "remove rows only where date, ticket number and amount all match", and the merged sheet came out 11 rows shorter instead of 140.
A picture framer's glass cost column. Asked to "add glass cost using width, height and the rate in cell H2", Copilot converted only the width to metres before multiplying by a rate quoted per square metre, so a 50 by 70 cm frame showed $1,225 of glass instead of $12.25. The fix was to state units: "width and height are in cm; H2 is dollars per square metre". Any prompt involving measurements should say the units, and the job costing sheet tutorial shows a layout that keeps units in the headers.
A music teacher's fees summary. Asked in Chat only for "total fees outstanding by family", Copilot answered $2,520. The teacher's own reconciliation said $2,340. The $180 difference was three families who had paid in cash, recorded as notes such as "paid cash 3/9" in a comments column instead of in the payments column, so neither Copilot nor any formula could count them. The fix was in the sheet, not the prompt: every payment now goes in the payments column, with the method in a column of its own.
A two-minute check before you trust a Copilot number
| Check | How, in practice |
|---|---|
| Recalculate one row or one total by hand | Filter to one item and sum it yourself; compare |
| Read the formula it wrote | Click a cell in the new column; check the divisor, the units and the cell references |
| Confirm the row count | After merges or de-duplication, compare the row count with what you expected |
| Question any "why" | Ask what in the data supports the explanation |
| Keep a way back | Work on a copy, or know where version history is, before Allow editing |
For a wider view of analysing a sales file with AI, including what to upload and what to check, see whether AI can analyse your sales spreadsheet. If you'd rather work in Claude or Google Sheets, using Claude with Excel and Google Sheets safely covers the equivalent risks. And which licence gives you what is set out in Copilot Chat vs Microsoft 365 Copilot; Microsoft's Excel FAQ lists a business subscription eligible for Copilot Chat as one of the qualifying options, so test before you buy the paid licence for Excel alone.
Copilot in Excel: follow-up questions
Do I need the paid Microsoft 365 Copilot licence to use Copilot in Excel?
Not necessarily. Microsoft's Excel FAQ lists four ways to qualify: a Personal or Family subscription with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription, or a business subscription eligible for Copilot Chat. The paid licence adds work across your emails, meetings and other files. Test the modes you need in your own account before buying.
Can Copilot in Excel work on my phone?
Partly. Microsoft's table shows edit, chat and plan modes on Windows, Mac and the web; chat and plan on iPad; chat on iPhone and Android, with edit mode rolling out to iPad and iPhone. For anything that changes a workbook, use a computer, where you can also watch the changes being made and undo them.
Why is the Copilot button greyed out in my workbook?
Check four things: the file is saved to OneDrive or SharePoint rather than only on your computer or not yet saved, your account has a qualifying licence, calculation options are set to Automatic, and you're signed in with the work account that holds the licence. Turning AutoSave on usually settles the first point.
Further reads
- Is Microsoft 365 Copilot Worth It for a Small Business? — Whether the paid licence earns its cost for a small team.
- Microsoft 365 Copilot Pricing: Business vs Enterprise Licences — Licence prices and bundles, set out side by side.
- How to Roll Out Microsoft 365 Copilot to a Small Team — Getting a team using Copilot without chaos.
- Outgrowing Spreadsheets: When to Replace Manual Excel With AI — Signs your numbers need more than a spreadsheet and an assistant.
- Does Microsoft 365 Copilot Keep Your Business Data Private? — What happens to the workbook data Copilot reads.
- Gemini in Google Sheets for Small Businesses: 10 Practical Uses — The Google Sheets equivalent, for comparison.
- The Complete Excel Guide 2026: Formulas, AI Copilot, and Modern M365 | Sagnik Bhattacharya — The Excel hub, with every spreadsheet tutorial on the site.
- Can ChatGPT Read PDFs, Spreadsheets and Photos? What Breaks — What ChatGPT reads well in PDFs, spreadsheets and photos, the features that make it misread or skip things, and a five-minute test before you trust a figure.
- How Event Planners Use AI to Build Budgets and Run Sheets — Turn an event brief into a line-item budget and a timed run sheet with AI, while supplier quotes and spreadsheet formulas keep the numbers honest.
- How MSPs Use AI to Write Quarterly Business Reviews — A one-page QBR structure, where each metric comes from, a narrative prompt with sample output, and how to run QBRs for 30 clients without sameness.
- Budget vs Actual: How to Explain Monthly Variances With AI — A materiality rule, price and volume splits, a context-notes template and an AI prompt, worked through a craft brewery's month line by line.
- How to Compare Supplier Quotes Side by Side With AI — Use AI to pull three messy quotes into one like-for-like table, spot what each leaves out, and recheck the totals in a spreadsheet before you choose.
- ChatGPT or Copilot for a Microsoft 365 Business? — Decide whether a Microsoft 365 team needs paid Copilot, ChatGPT or included chat, using a two-user cost and workflow trial.
- Is Copilot Pro Worth It for a Sole Trader? — Copilot Pro is retired. The four options that replaced it for a one-person business, priced for a year, with the traps in a single-user Microsoft account.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: Microsoft Support (Get started with Copilot in Excel; Frequently asked questions about Copilot in Excel; Copilot in Excel tips; COPILOT function; Frequently asked questions about Copilot in Microsoft 365 subscriptions), Microsoft Excel team's What's New posts (2026), checked September 2026.