Microsoft Copilot in Excel lets you ask questions about your data in plain English, generate formulas, create charts, and get an instant summary of what your spreadsheet contains — all without writing a single formula yourself. This guide explains what it can do, what subscription you need, and how to get the most out of it.
What Copilot in Excel Actually Does
Copilot is an AI assistant built directly into Excel. Instead of having to know which formula to use or how to structure a pivot table, you just describe what you want in plain English and Copilot works it out.
For example, you can type things like:
- “What are my top five products by revenue?”
- “Add a column showing the profit margin for each row.”
- “Create a chart comparing sales this year versus last year.”
- “Highlight all rows where the order value is over £500.”
Copilot reads your data, carries out the task, and shows you what it’s done — including the formula or chart it created, so you can see and verify the logic.
Which Plans Include Copilot in Excel
This is where many people get caught out. Copilot in Excel is not included in standard Microsoft 365 Personal or Family subscriptions. It requires a separate add-on.
For businesses
You need Microsoft 365 Copilot, which is an add-on to eligible Microsoft 365 business plans (Business Basic, Business Standard, Business Premium, or enterprise E3/E5). As of 2024 the add-on is priced per user per month on top of your existing subscription. Check with your IT administrator or Microsoft’s website for current pricing.
For home users
Microsoft has been rolling out a version of Copilot to Microsoft 365 Personal and Family subscribers, but availability and features vary by region and continue to change. Check File > Account in Excel — if Copilot is available on your plan, you’ll see it on the Home tab.
If you don’t have a Copilot subscription, ChatGPT is a free and very capable alternative for writing formulas and analysing data — more on that in a separate guide.
How to Access Copilot in Excel
- Open Excel with a Microsoft 365 subscription that includes Copilot.
- Open or create a workbook with your data.
- Look for the Copilot button on the Home tab (towards the right side of the ribbon). If you don’t see it, your plan may not include it yet.
- Click the Copilot button — a panel opens on the right side of the screen.
Important: your data must be formatted as a Table
Copilot only works with data that’s been formatted as an Excel Table. If your data is just a plain range, Copilot will prompt you to convert it. To do this yourself: click anywhere in your data, go to Insert > Table, confirm the range, and click OK. Make sure “My table has headers” is ticked.
Asking Copilot to Analyse Your Data
Once the Copilot panel is open, you can type a question or request in the chat box at the bottom. Copilot will respond with an answer, a suggested formula, a chart, or a modification to your table — depending on what you asked.
Useful prompts to try
- “Show me a summary of this data.”
- “Which month had the highest total sales?”
- “Are there any unusual values or outliers in this dataset?”
- “What is the average order value by region?”
Copilot will often respond with both an answer and a suggestion to add the result to your spreadsheet — for example, offering to add a new column or create a pivot table.
Using Copilot to Create Formulas
You don’t need to know Excel formula syntax to use Copilot. Describe what you want to calculate and Copilot will write the formula for you.
Examples
- “Add a column that calculates the profit margin as a percentage.”
- “Write a formula to count how many orders are marked as ‘Pending’.”
- “Create a formula that shows the running total in column E.”
Copilot will show you the formula it plans to use before inserting it, so you can check it makes sense. You can then click Insert column to add it to your table.
Using Copilot to Create Charts and Visualisations
Creating charts with Copilot is as simple as describing what you want to see:
- “Create a bar chart showing sales by product category.”
- “Show me a line chart of monthly revenue over the last 12 months.”
- “Create a pie chart showing the share of revenue by region.”
Copilot will generate the chart and add it to your sheet. You can then format it further using the standard Excel chart tools.
Getting Insights and Highlights
One of the quickest ways to use Copilot is to ask it for insights without knowing what you’re looking for:
- “What are the key trends in this data?”
- “Highlight the rows where the delivery was late.”
- “What stands out in this dataset?”
Copilot will often return a list of observations — for example, “Sales in Q3 were 23% higher than Q2” or “Product X accounts for 40% of total revenue”. It can also apply conditional formatting rules to highlight cells that meet criteria you describe.
Tips for Getting Better Results from Copilot
Be specific
Vague prompts get vague results. Instead of “analyse my data”, try “show me the total revenue by sales rep for Q1 2024”. The more context you give, the better Copilot performs.
Tell Copilot what your columns mean if they’re not obvious
If your columns are labelled “Col_A” or have abbreviations, mention what they represent in your prompt: “The ‘Rev’ column contains revenue in pounds.”
Ask follow-up questions
Copilot remembers the context of your current session. If the first answer isn’t quite right, ask it to refine: “Can you break that down by month instead of quarter?”
Current Limitations to Be Aware Of
- Data must be a Table. Plain ranges won’t work — convert first.
- Limited to the current workbook. Copilot can’t pull in data from other files or external sources.
- Can make mistakes. Always check formulas and results before relying on them, especially for financial data.
- Large datasets may be slow. Very large tables can take longer to process or may hit limits.
- Not available offline. Copilot requires an internet connection as processing happens in Microsoft’s cloud.
Privacy Considerations
When you use Copilot in Excel, your data is sent to Microsoft’s servers for processing. For most business data this is covered under your Microsoft 365 data protection agreement, but you should check your organisation’s policies before using Copilot with sensitive or confidential data. Microsoft has committed not to use Copilot inputs to train its AI models for commercial customers, but it’s worth reading the current terms if data sensitivity is a concern.
If you’d prefer not to send your data to any cloud service, using ChatGPT by copying and pasting a sample of your data — rather than connecting it directly — gives you more control over what you share.
