Expert guide
Copilot in Excel: What It Actually Does for Business Spreadsheets
A practical look at setup, useful prompts, the mistakes it makes, and where it stops
Call us at +1 (385) 386-3860
Short answer: Copilot in Excel is an AI assistant that reads the workbook you have open and can add formula columns, sort and filter, build PivotTables and charts, summarise trends, and run Python-based analysis from a plain-English request. It needs a Copilot-enabled Microsoft 365 licence and a file saved to OneDrive or SharePoint. It speeds up one-off analysis; it does not replace scheduled automation for recurring reports.
Microsoft has put Copilot into every Office app, and Excel is where business users have the highest expectations and the most confusion. Teams buy licences expecting their monthly reporting to run itself, then find Copilot greyed out, or answering a question about the wrong range. This guide explains what Copilot in Excel is good at, what it needs to work, and how to decide whether a task belongs to Copilot or to proper automation.
Features change frequently. What follows reflects Copilot in Excel for Microsoft 365 at the time of writing; check Microsoft's release notes for anything added since.
What You Need Before Copilot Will Work
A licence that includes Copilot
Business and enterprise users need the Microsoft 365 Copilot add-on. Some consumer plans include Copilot with monthly usage limits.
A file in the cloud with AutoSave on
Copilot works on workbooks stored in OneDrive or SharePoint. A file opened from a local folder or network drive often shows the Copilot button disabled.
Clean, tabular data
One header row, no merged cells, no blank columns in the middle. Converting the range to an Excel Table (Ctrl+T) gives the best results.
Your organisation's permission
IT can disable Copilot for some users or apps. If colleagues have it and you do not, ask your Microsoft 365 admin.
What Copilot in Excel Does Well
Adding formula columns from a description
Ask "Add a column that shows days between Order Date and Ship Date" or "Add a column that flags orders over $5,000 from new customers." Copilot proposes the formula, shows a preview, and inserts it into the table. This is the most reliable feature and the one most teams use daily.
Highlighting, sorting and filtering
"Highlight invoices more than 60 days overdue in red" applies conditional formatting. "Show only West region orders from Q3" applies a filter. These are quick wins for people who never learned the ribbon.
PivotTables, charts and summaries
"Show total revenue by product category and month" creates a PivotTable on a new sheet. "What are the main trends in this data?" returns a few observations with supporting charts. Treat these as starting points to check, not conclusions to forward.
Advanced analysis with Python in Excel
For questions such as forecasting or outlier detection, Copilot can write and run Python in Excel code for you, then place the results and charts in the sheet. It is powerful, but you should be able to read the code or have someone who can. Our guide to Python in Excel for business users covers what that code is doing.
Prompts That Work Better
Copilot responds best to specific, outcome-focused requests that name the columns it should use.
| Task | Vague prompt | Better prompt |
|---|---|---|
| Analysis | Analyse this data | Which 5 customers grew revenue most between Q2 and Q3? Use Customer, Order Date and Amount. |
| Formula | Calculate margin | Add a column Margin % = (Amount − Cost) / Amount, formatted as a percentage. |
| Formatting | Make it look better | Highlight rows where Status is Overdue and Balance is over 1,000. |
| Charting | Make a chart | Create a line chart of monthly revenue for 2025 and 2026 on the same axis. |
Where Copilot Gets Things Wrong
In client workbooks we see the same failure patterns repeatedly. None of them are unique to Copilot; they are what happens when any analyst, human or AI, works from data they did not prepare.
Numbers stored as text
Imported amounts with a leading apostrophe or trailing space are ignored in sums. Copilot reports a lower total without warning you.
Ambiguous headers
Two columns called Date, or a header like 'Amt (excl)', lead to the wrong column being used.
Partial ranges
If the data has blank rows, Copilot may only see the block above the first blank.
Confident summaries
Written insights read well even when based on a filtered view. Always confirm one number by hand.
Multi-sheet logic
Questions that need data from several sheets or files are where results are least reliable.
Copilot vs Automation: Which Does the Job Need?
The useful question is not "is Copilot good?" but "does this task happen once, or every week?"
| Feature | Copilot in Excel | Built automation |
|---|---|---|
| Best for | One-off questions and quick analysis | Recurring reports and processes |
| Runs without a person | No | Yes (scheduled or triggered) |
| Same result every time | Not guaranteed | Yes, tested logic |
| Pulls from other systems | Limited | Yes: SQL, APIs, QuickBooks, email |
| Audit trail | Conversation only | Documented steps and logs |
If your team spends hours every week merging files, refreshing the same PivotTables and emailing the result, that is an automation problem. Copilot will make each run a little faster; automation removes the run. See Excel data consolidation and AI workflow automation for the approach we use.
A Simple Rollout Plan for Teams
Pick three real, recurring questions your team answers in Excel today.
Prepare one clean table per question: single header row, correct data types, no merged cells.
Try Copilot on each and record whether the answer matched a known figure.
Keep Copilot for the tasks it got right; flag the rest for automation or a redesigned workbook.
Write a one-page guide for staff with the prompts that worked on your data.
Copilot Also Exposes Bad Workbooks
A common side effect of a Copilot rollout is discovering that core workbooks are not structured well enough for AI to read: merged headers, numbers as text, one giant sheet with hidden columns. Fixing that structure pays off whether or not you keep using Copilot. Our Excel consultants restructure those files into clean tables and models, and add the automation for the steps Copilot cannot take.
FAQ
Copilot in Excel FAQ
Common questions from teams rolling out Copilot