How do I set up Copilot in Excel?

Copilot requires a Microsoft 365 subscription with Copilot add-on. Once licensed, Copilot appears in the ribbon. Format your data as an Excel Table (Ctrl+T), click the Copilot button, and start typing requests. Copilot reads your table structure and can add formulas, create charts, analyse data, and format your workbook.

Do I need a special subscription for Copilot in Excel?

Yes, Copilot in Excel requires a Microsoft 365 Copilot subscription, which is an add-on to your existing Microsoft 365 plan. The base Microsoft 365 subscription alone does not include Copilot. Check with your IT administrator or Microsoft account to add the Copilot license.

Does Copilot in Excel work with local files saved on my computer?

No. Copilot requires the file to be saved to OneDrive or SharePoint with AutoSave enabled. If you open a local file (saved to your desktop or Documents folder), the Copilot button is greyed out and shows a message asking you to save to the cloud first. Save the file to OneDrive and AutoSave will switch on automatically.

Can Copilot in Excel write VBA macros?

No. As of 2026, Copilot in Excel cannot generate or run VBA macros. Its automation capabilities are limited to formula suggestions, column creation, and chart generation within the Copilot pane. For VBA macro generation, use Claude AI — paste a plain English description and Claude returns complete, runnable VBA code. See the Claude AI + Excel macros tutorial on this site.

Is my data sent to Microsoft when I use Copilot in Excel?

Yes. Your spreadsheet data is sent to Microsoft's servers to generate Copilot responses. Microsoft states it does not train its models on Copilot tenant data and that enterprise customers' data stays within their Microsoft 365 compliance boundary. However, if your workbook contains personally identifiable information or sensitive commercial data, check your organisation's Copilot data governance policy before using it.

Does Copilot in Excel work on Mac?

Yes. Copilot in Excel is available on the Microsoft 365 desktop app for Mac as well as in Excel for the web (browser). The feature set is the same — formula suggestions, chart creation, data analysis prompts. The only Mac limitation is that VBA-related Copilot features (if ever added) would be Windows-only, since VBA on Mac has OS-level restrictions.

Copilot + Excel

How to Set Up and Use Microsoft Copilot in Excel (2026)

By Sagnik Bhattacharya2 Mar 202610 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for Getting Started with Microsoft Copilot in Excel, with AI assistant styling, onboarding cues, and spreadsheet cards.

Microsoft Copilot in Excel is an AI assistant built directly into the spreadsheet — you describe what you want in plain English and Copilot generates formulas, creates charts, highlights trends, and summarises data without you having to write a single function manually. It is genuinely useful for analysts who spend hours doing repetitive data prep, and it is now available as part of the Microsoft 365 Copilot subscription.

I teach Flutter and Excel with AI — explore my courses if you want structured learning.

The catch: Copilot has strict setup requirements that catch many first-time users off guard. Your file must be saved to OneDrive or SharePoint, your data must be formatted as an Excel Table, and you need the right Microsoft 365 licence tier. This tutorial covers every setup step, eight real prompt examples that work, honest limitations, and a troubleshooting section for the most common setup failures.

If you are on a free or standard Microsoft 365 plan without the Copilot add-on, jump to the Claude AI for Excel formulas tutorial instead — Claude's free tier covers most of the same use cases without a subscription requirement.

What Copilot in Excel Can Do

Copilot reads your table structure and data, then responds to natural language requests. Its main capabilities:

Data summaries — totals, averages, min/max, distribution across categories
Formula creation — adds a new column with the correct formula applied to all rows
Chart generation — creates bar, line, pie, and scatter charts from a text description
Highlighting — colour-codes rows matching a condition (e.g. revenue above £50k)
Sorting and filtering — reorders or shows a subset of rows on request
Trend and outlier spotting — flags anomalies in a column or summarises direction of change over time
PivotTable creation — generates a PivotTable layout from a description

What Copilot cannot do: write VBA macros, access external data sources, run on local files, or work offline.

Prerequisites

All four of these must be true before Copilot works. Skipping any one of them causes the Copilot button to be greyed out or produces an error message.

Microsoft 365 Copilot subscription — the standard Microsoft 365 Personal or Business plan does not include Copilot. You need either a Microsoft 365 Copilot add-on (sold per user per month) or a plan that bundles it (Microsoft 365 E3/E5 with Copilot, or Microsoft 365 Business Standard/Premium with Copilot). Check your account at account.microsoft.com or ask your IT administrator.
File saved to OneDrive or SharePoint with AutoSave on — Copilot is cloud-based. Open a local .xlsx, go to File → Save a copy → OneDrive, and AutoSave will enable automatically. Files on your desktop or Documents folder cannot use Copilot.
Data formatted as an Excel Table — select any cell in your data range, press Ctrl+T, confirm headers, and click OK. The Table name appears in the Table Design tab. Copilot reads the Table's column headers to understand your data — good headers matter (see Excel Tables best practices).
Excel updated to the latest Microsoft 365 build — go to File → Account → Update Options → Update Now. Copilot features roll out with updates so an older build may not show the Copilot button even with the correct licence.

Step-by-Step: Your First Copilot Session

Step 1: Prepare your data

Clean your data before inviting Copilot to analyse it. Blank rows, merged cells, and columns named "Column1" or "D" will produce unhelpful responses. A clean table has: one header row with descriptive names, no blank rows, no merged cells in the data area, and consistent data types per column (all numbers or all dates — not mixed). See how to clean messy data in Excel if you are starting from raw exports.

Then: select any cell in the data → press Ctrl+T → confirm the header row checkbox is ticked → click OK. Your data is now a Table.

Step 2: Save to OneDrive

File → Save a copy → OneDrive → pick a folder → Save. AutoSave appears in the top-left of the window. If AutoSave stays off after saving, go to File → Options → Save and check "AutoSave files stored in the Cloud by default".

Step 3: Open the Copilot pane

Click the Copilot button in the Home tab (far right of the ribbon). A panel slides in from the right. If the button is missing, see Troubleshooting below.

Step 4: Send your first prompt

Click the text box at the bottom of the Copilot pane and type a request. Start simple:

Summarise this table

Copilot returns a natural language summary — total rows, key column statistics, any patterns it detected. Review it and send a follow-up to go deeper.

Step 5: Iterate

Copilot remembers the context of the current session. Follow-up prompts build on previous ones: "Now show the same breakdown but only for Q1" or "Apply this formula to the whole table" after it has generated a formula in the previous step.

8 Practical Prompt Examples

1. Instant summary

"Give me a summary of this table including total revenue, average order value, and the top 3 products by quantity sold."

Copilot returns a formatted summary card. Useful as a first pass before building a proper dashboard — see how to build an interactive dashboard in Excel.

2. Add a calculated column

"Add a column called Profit Margin that divides the Profit column by the Revenue column and formats it as a percentage."

Copilot inserts the column into the Table with the formula applied to every row. You can see and edit the formula in the formula bar.

3. Highlight rows meeting a condition

"Highlight all rows where Revenue is greater than 50000 in green."

Copilot applies conditional formatting. For more advanced formatting rules, the conditional formatting guide covers formula-based rules that Copilot cannot yet create.

4. Create a chart

"Create a bar chart showing Revenue by Region for Q1 only."

Copilot generates a chart preview with an Insert button. Review it before inserting — chart type and axis labels are usually correct, but axis scales sometimes need manual adjustment. See the professional charts guide for formatting.

5. Sort and filter

"Filter the table to show only rows where the Status column is 'Pending' and sort by Date ascending."

Copilot applies the filter and sort to the Table. You can undo both with Ctrl+Z if the result is not what you expected.

6. Spot outliers

"Are there any outliers in the Order Value column? What is the highest value and which row does it belong to?"

Copilot identifies statistical outliers and names the row. Useful for a quick sanity check before presenting data to a client.

7. PivotTable from a description

"Create a PivotTable that shows total Revenue by Product Category by Quarter."

Copilot builds the PivotTable on a new sheet. You can then customise it manually — see the pivot table guide for deeper customisation.

8. Formula explanation

"Explain what the formula in column F is doing in plain English."

Copilot reads the formula and explains each part — useful when you inherit a workbook and don't recognise a nested function. For debugging formulas that return errors, see fixing Excel formula errors with Claude AI.

Tips for Better Prompts

Tip	Instead of…	Write…
Use exact column names	"Sum the sales"	"Sum the Revenue column"
Specify output format	"Visualise this"	"Create a line chart showing Monthly Revenue"
Describe the condition	"Filter the important rows"	"Filter to rows where Status equals 'Active'"
One request at a time	"Sort, filter, and add a chart"	Send three separate prompts
Confirm before inserting	(insert immediately)	"Show me a preview first" — Copilot usually previews charts before inserting

Copilot vs Claude AI for Excel

Feature	Copilot in Excel	Claude AI
Where it runs	Inside Excel — sees your live data	External chat — you paste data or describe it
Formula generation	Inserts directly into the Table	Returns formula text to copy-paste
VBA macros	Not supported	Yes — generates complete, runnable VBA
Chart creation	Yes — generates and inserts charts	No — describes chart settings only
Subscription	Microsoft 365 Copilot add-on (paid)	Free tier available
Offline use	No — requires cloud connection	Yes — claude.ai works in any browser
Best for	Live data exploration, chart creation, quick summaries	Formula building, VBA, debugging, explanations

Use both: Copilot for real-time data exploration inside the workbook, Claude for formula engineering and automation outside it.

Troubleshooting

Copilot button is missing from the Home tab
Either your licence does not include Copilot, or your Excel build is outdated. Check: File → Account → About Excel — your build number should be 16.0.16924 or higher. Also check: File → Account → Product Information — if "Microsoft Copilot" does not appear, contact your IT admin or check your Microsoft account at account.microsoft.com. Update Excel via File → Account → Update Options → Update Now.
"This file needs to be saved to the cloud" or Copilot button is greyed out
The file is saved locally. Go to File → Save a copy → OneDrive, pick a folder, and click Save. AutoSave will enable automatically. Files on your Desktop or in Documents cannot use Copilot even if you have the correct licence.
"I can only work with Excel Tables" message
Your data is not formatted as a Table. Click any cell in your data range, press Ctrl+T, confirm the header row checkbox, and click OK. The Table Design tab will appear confirming the conversion.
Copilot gives a vague or irrelevant response
Usually caused by poor column names (e.g. "Column1", "D", "Amount2") or blank rows in the data. Rename columns to descriptive names (Revenue, Region, Order Date), remove blank rows, and make sure data types are consistent per column. Then close and reopen the Copilot pane to refresh its view of the Table.
"Copilot is unavailable right now"
This is a Microsoft service outage. Wait a few minutes and try again. Check status.office.com to see if there is an active Microsoft 365 service incident affecting Copilot.

FAQ

Is Copilot free with my Microsoft 365 subscription?: No. The standard Microsoft 365 Personal, Family, Business Basic, and Business Standard plans do not include Copilot. You need a Microsoft 365 Copilot add-on (currently around £25/user/month) or a plan that bundles Copilot. Some enterprise plans include it — check with your IT administrator.
Does Copilot work with local Excel files?: No. Copilot requires the file to be saved to OneDrive or SharePoint with AutoSave enabled. Files saved locally produce a greyed-out Copilot button or a prompt asking you to save to the cloud. Save to OneDrive first: File → Save a copy → OneDrive.
Can Copilot write VBA macros?: No — as of 2026, Copilot in Excel cannot generate or run VBA. For VBA macro generation, use Claude AI: describe the automation task in plain English and Claude returns complete, copy-paste VBA code. See the Claude AI + Excel macros tutorial.
Is my data sent to Microsoft when I use Copilot?: Yes. Spreadsheet content is sent to Microsoft's servers to generate responses. Microsoft states it does not train its models on tenant data, and enterprise customers' data stays within their Microsoft 365 compliance boundary. If your workbook contains PII or sensitive commercial data, check your organisation's Copilot data governance policy before using it.
Does Copilot in Excel work on Mac?: Yes — available on the Microsoft 365 desktop app for Mac and in Excel for the web. Feature parity is the same as Windows. The only Mac limitation is that VBA-related automation features are not available, but VBA itself is restricted on Mac Excel regardless of Copilot.