Copilot in Excel: the step-by-step guide (formulas, analyses, charts)
Tutorial published on October 4, 2026, updated on October 24 and November 11, 2026. The screenshots were taken on October 4 in Excel for the web, using a Microsoft 365 Copilot license, on a demo workbook with fictitious data. The access conditions and license price are from our comparison. Copilot Chat or Microsoft 365 Copilot, compiled from official Microsoft documentation.
Short answer: Copilot in Excel adds a calculated column based on a French instruction, answers a numerical question about your data, creates a pivot table with its chart and highlights rows. This requires a Microsoft 365 Copilot license, which many call " Copilot Premium ", starting at €18.20 excluding VAT per user per month: as of April 15, 2026, Excel no longer offers Copilot to Copilot Chat (Basic) accounts. In our test on a table of 180 sales, the four results obtained were correct, verified one by one by recalculation.
Simply open the Copilot pane in a workbook saved on OneDrive or SharePoint, write a precise instruction, then check and validate the result.
This guide shows you the process step by step, using a concrete example: a table of 180 sales (date, region, salesperson, category, product, quantity, unit price, discount). Each result obtained with Copilot was recalculated separately to verify its accuracy. To have an entire team practice these techniques on their own spreadsheets, see our Copilot training in companies.
To practice on your own files, the free NextStart library offers a prompt to spot anomalies in a table, to be pasted as is into Copilot. The others are gathered in our Examples of AI prompts by profession.
Copilot in Excel in brief: four tested uses
| Usage | Tested instruction | Results in our test | Point of vigilance |
|---|---|---|---|
| Add a calculated column | Add a Sales Revenue column that calculates Quantity x Unit Price x (1 – Discount) | Column filled across all 180 rows in about twenty seconds, formula displayed | Read the proposed formula before submitting. |
| Ask a question about the data | Which regions and salespeople generate the most revenue? | Île-de-France leads with €134,557.65, exact totals | Recalculate any figure intended for dissemination |
| Create a pivot table | Create a pivot table of sales figures by region and category, with a graph | New sheet, total of €430,475.05, stacked bar chart | The pivot table can still be edited manually. |
| Highlight lines | Highlight in light green the lines whose turnover exceeds €5,000 | 17 lines highlighted, number confirmed by recalculation | That's a rule: it adapts if the data changes |
What you need to use Copilot in Excel
- A Microsoft 365 Copilot license assigned to your work account. This is what grants access to the workbook editing functions described here. If you are unsure, your IT department can tell you which plan you have been assigned.
- A workbook saved in the cloudon OneDrive or SharePoint, with automatic saving enabled. This is the safest configuration: Copilot works on the online version of the file.
- Clean and structured data : one row of explicit headers, one row per record, no merged cells. Ideally, they should be formatted as a table (Home, then Format as Table).
How to open Copilot in Excel
In Excel for the web, the Copilot icon appears in the bottom right corner of the grid. Clicking it opens the Copilot pane on the right side of the sheet.

The panel opens to the "Edit with Copilot" input area (formerly called Agent mode) and three starting suggestions. Two settings deserve your attention:
- The "Automatic" menu At the top of the panel: Copilot automatically selects the AI model best suited to the request. You can specify a model, but the automatic mode is suitable for most uses.
- "Allow modification" above the input area: it determines whether Copilot can directly modify the workbook or only respond to you.

Step 1: Add a calculated column without writing the formula
Our table contains the quantity, unit price, and discount, but not the revenue from each sale. Rather than writing out the formula, we describe it:
Add a Sales Revenue column that calculates Quantity x Unit Price x (1 – Discount), in euro currency format
In about twenty seconds, Copilot adds the column to the table, fills it in across all 180 rows, and displays the formula used in the pane. =[@Quantité]*[@[Prix unitaire]]*(1-[@Remise])with two examples of control. The "Done" and "Cancel" buttons allow you to confirm or go back.

The key is to read the formula before submitting. Here, it uses structured references (the column names in the table), making it readable and robust even if the table grows. A check of the first row confirms that 17 screens at €260 each (without discount) do indeed yield €4,420.
Step 2: Ask a question about your data
Copilot can also answer a question without changing anything. The instructions would benefit from specifying the expected answer format:
Which regions and salespeople generate the most revenue? Answer in 3 short points, with the amounts.

Copilot identifies Île-de-France as the top region (€134,557.65) and Léa Petit as the top saleswoman (€90,156.95). We recalculated these totals using the source data: they are accurate. This verification remains essential whenever a figure is intended for publication.
Step 3: Create a pivot table and a chart
Pivot tables are often the Excel feature that people put off because they don't know how to configure them. With Copilot, one sentence is all it takes:
Create a pivot table of sales figures by region and category in a new sheet, with a stacked bar chart.
Copilot creates a new sheet "Region-Category Revenue", places the pivot table there (5 regions, 3 categories, grand total of €430,475.05), then adds the requested chart and a layout title. The panel summarizes what has been done and where.


The resulting pivot table is a true Excel pivot table: you can then modify it manually (add a filter, change a field) as if you had built it yourself.
Step 4: Highlight the important lines
The last common use is conditional formatting. Back to the sales sheet:
Highlight in light green the lines whose turnover exceeds €5,000

Copilot applies a conditional formatting rule across the entire width of the table and indicates the number of rows affected: 17, which our recalculation confirms. Since this is a rule and not a manually applied color, it will adapt if the data changes.
10 Copilot prompts for Excel to copy
Adapt the column names to your own table: the more precisely the instructions mention the columns, the more reliable the result.
- Adds a Margin column that calculates Revenue minus Cost, in euro format.
- Adds a Quarter column calculated from the Date column.
- Explain to me step by step the formula for the Revenue column.
- What are the three best-selling products in terms of quantity? Give the figures.
- Sort the table by descending revenue.
- Create a line graph of the monthly evolution of revenue.
- Identify duplicates and empty cells in this table, without changing anything.
- Standardizes all dates in dd/mm/yyyy format.
- Calculate the average basket size per region in a new sheet.
- Summarize the sales trends in 5 points for a management committee.
Limitations and pitfalls to be aware of
- Always check before sharing. In our test, all four results were correct. This is not a guarantee: a poorly named column or ambiguous instructions can produce a calculation that appears correct but is actually incorrect. Check at least one row manually and read the formula provided.
- Use the Cancel button. As long as you haven't clicked "Done," you can go back. OneDrive's version history also serves as a safety net.
- Pay attention to the data structure. Merged cells, headers spanning two lines, or interleaved subtotals disrupt Copilot. A clean table does more for the quality of responses than a sophisticated prompt.
- One request at a time at first. Copilot can chain several actions together, but separate instructions make each result easier to verify.
- Consider confidentiality. Copilot operates within your Microsoft 365 environment and respects your access rights, but it does utilize everything in the workbook. Do not leave unnecessary sensitive data in a file that you intend to have analyzed or shared.
Frequently Asked Questions about Copilot in Excel
How do I activate Copilot in Excel?
There's nothing to install: Copilot appears in Excel as soon as the Microsoft 365 Copilot license is assigned to your work account. Open a workbook saved to OneDrive or SharePoint, then click the Copilot icon in the bottom right corner of the grid to open the pane. If the icon doesn't appear, see the last question on this page.
Do I need a license to use Copilot in Excel?
Yes. Since April 15, 2026, Excel no longer offers Copilot for Copilot Chat (Basic) accounts: a Microsoft 365 Copilot license, often referred to as "Copilot Premium," is required, starting at €18.20 excluding VAT per user per month. Your IT department can confirm the offer assigned to your account.
Does Copilot work with a file saved on my computer?
The most reliable scenario remains a workbook saved to OneDrive or SharePoint, with automatic saving enabled. Microsoft is gradually extending Copilot to local files in the desktop version of Excel; if the Copilot icon remains grayed out, save the file to the cloud.
Can Copilot make a mistake in a calculation?
Yes, especially if the instructions are ambiguous or the data is poorly structured. Copilot displays the formula used: read it and check a line by hand before relying on the result.
Does Copilot in Excel write formulas or values?
For a calculated column, it writes a real Excel formula, visible in the formula bar. The result therefore remains up-to-date even if the source data changes.
Why don't I have the Copilot button in Excel?
First, look for the Copilot icon in the bottom right corner of the window: Microsoft has grouped access to Copilot there, and you can then pin it next to the ribbon. If it's not there, open File, then Account: verify that you are signed in with the work account that has the Microsoft 365 Copilot license ("Copilot Premium"), click Update license, and restart the applications. Then install the latest updates: the Enterprise semi-annual channel does not include Copilot, and your IT department can upgrade your computer to the current or monthly channel. Finally, connected experiences must be enabled in the account's privacy settings. In the meantime, Excel for the web remains a backup solution.
Explore more!
- Copilot Studio: Create your first agent step by step
- Copilot in Outlook: the step-by-step guide
- Copilot in Teams: the step-by-step guide
- Copilot in SharePoint: the step-by-step guide
- Copilot in Word: the step-by-step guide
- Copilot in PowerPoint: the step-by-step guide
- Which AI model should be chosen for which task?
- Claude Cowork, Copilot Cowork, ChatGPT Work: the comparison for businesses
- From prompt to context: context engineering
