Microsoft Copilot in Excel: What It Can Do (2026)
An honest breakdown of what Copilot actually does well, where it falls short, what it costs, and whether it is worth it for your use case.
Quick Verdict
Worth it if you work in Excel daily, manage large datasets, or regularly write complex formulas. The formula generation, data analysis, and Python integration save real time at that level of use. Not worth it if you use Excel occasionally for simple tasks — free tools like ChatGPT cover 80% of the same formula-writing capability at no extra cost.
Capabilities: What Works and What Doesn't
Formula generation
StrongDescribe what you want in plain English — Copilot writes the formula, places it in the selected cell, and explains what it does. Works well for XLOOKUP, SUMIFS, nested IF, and most standard functions.
Example prompt: "Add a formula in column F that shows the difference between the current row's sales and the average sales across the whole table."
Data analysis and insights
StrongAsk Copilot to find trends, outliers, or summaries in your data. It reads your table and returns a written summary plus optional charts or pivot tables.
Example prompt: "Which region had the lowest average order value in Q1, and how does that compare to Q2?"
Python code generation
StrongAsk Copilot to write Python code for data analysis tasks — statistical modelling, visualisations, or complex transformations — and it inserts the code directly using the =PY() function.
Example prompt: "Create a scatter plot of column B (revenue) vs column C (cost) with a trend line."
Conditional formatting
GoodApply conditional formatting rules using plain English. Copilot creates the correct rule and applies it to the range you specify.
Example prompt: "Highlight all cells in column D where the value is more than 20% above the column average."
Chart creation
GoodRequest a chart type and Copilot selects the appropriate data, creates the chart, and applies basic formatting. Good for getting a first draft quickly.
Example prompt: "Create a bar chart comparing monthly sales by region for the data in this table."
Explaining existing formulas
GoodClick a cell with a complex formula and ask Copilot to explain it. Useful for understanding inherited workbooks.
Example prompt: "What does the formula in cell G12 do, and are there any potential issues with it?"
VBA macro generation
LimitedCopilot can generate basic VBA, but complex automation — file handling, external connections, custom UI — is better handled by ChatGPT with a detailed prompt.
Example prompt: "Write a macro to send the active sheet as a PDF attachment in Outlook."
Auditing formulas for errors
WeakCopilot can spot obvious issues when asked about a specific formula, but it does not proactively audit your workbook for broken formulas, inconsistent patterns, or hidden errors across sheets.
Example prompt: Use the ExcelErrorFinder audit tool for this instead.
Requirements and Setup
Business Basic, Business Standard, Business Premium, or Enterprise. Personal plans (Home, Family) do not include Copilot.
Microsoft 365 Copilot licence — approximately $30/user/month on top of your M365 plan.
Data must be in a formatted Excel Table (Insert → Table) for best results. Plain ranges work but give less accurate responses.
File must be saved to OneDrive or SharePoint — Copilot does not work on files saved only to local drives.
Excel for the web (browser) or Excel desktop app (Windows or Mac) with M365 channel updates.
How to Use Copilot in Excel
Once licensed, the Copilot button appears in the Home ribbon. Clicking it opens a chat sidebar on the right side of Excel. You type your request in natural language and Copilot responds in the sidebar — showing suggested formulas, analysis results, or code it wants to insert.
For formula generation, Copilot shows a preview before inserting anything. You can accept, discard, or ask it to adjust. The formula appears in the sidebar so you can inspect it before it touches your sheet.
Copilot vs ChatGPT for Excel: When to Use Each
These tools are complementary, not competing:
- Use Copilot when you want AI integrated directly into Excel, working on your actual data without copy-pasting. It sees your column names and table structure, so descriptions can be less precise.
- Use ChatGPT or Claude when you need a formula without a Copilot subscription, want more explanation, are writing complex VBA, or are working on a file not saved to OneDrive.
- Use Formula Bot or Ajelix for quick, free formula generation when you know exactly what function you need and just want the syntax written out.
Tips for Better Results with Copilot
- Format your data as an Excel Table (Insert → Table) before asking Copilot anything — it reads table column names and uses them in formulas, making results dramatically more accurate.
- Be specific about what "done" looks like: "Show me which rows have a value in column C more than 2 standard deviations above the mean, highlighted in red" gets better results than "find outliers".
- Ask follow-up questions: if the first formula is close but wrong, say what is wrong and ask Copilot to adjust rather than starting over.
- For Python tasks, describe the output you want rather than the code — "create a bar chart of sales by month" rather than "write Python that uses matplotlib to...".
AI can write formulas — but not audit a workbook
Copilot and ChatGPT write new formulas well. Finding errors hidden across 20 sheets in an existing workbook is a different problem. The ExcelErrorFinder audit tool scans every formula and flags errors, inconsistencies, and broken links.
Frequently Asked Questions
How do I get Copilot in Excel?
You need a Microsoft 365 Business plan (Basic, Standard, Premium, or Enterprise) plus the Microsoft 365 Copilot add-on (~$30/user/month). Personal M365 plans do not include Copilot. The file must also be saved to OneDrive or SharePoint.
Can Copilot analyse data in Excel?
Yes — it surfaces trends, outliers, and summaries from plain-English questions. For best results, format your data as an Excel Table (Insert → Table) so Copilot can read column names directly.
Does Copilot replace VLOOKUP and XLOOKUP?
No — it helps you write lookup formulas by generating them from plain English, but the formulas still use XLOOKUP, INDEX MATCH, and standard functions. It removes the need to remember exact syntax, not the functions themselves.
Related Guides
Excel and AI: Complete Guide
The full landscape — Copilot, ChatGPT, Formula Bot, Python in Excel.
Read →
ChatGPT for Excel Formulas
12 proven prompt templates for writing and fixing Excel formulas.
Read →
Best Excel Tools (2026)
AI tools ranked alongside traditional add-ins for every use case.
Read →
Learn Excel
Best courses and resources — knowing formula basics makes AI output easier to verify.
Read →