Copilot in Excel: the step-by-step guide (formulas, analyses, charts)
Tutorial published on October 4, 2026. All screenshots were taken that day in Excel for the web, with a Microsoft 365 Copilot license, on a demonstration workbook with fictitious data.
Copilot in Excel allows you to ask in French what you want to get from your data, and let it do it: add a calculated column, answer a numerical question, create a pivot table with its chart or highlight rows. 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.
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).
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
Do I need a license to use Copilot in Excel?
Yes. To modify the workbook as shown in this guide (columns, pivot tables, formatting), you need a Microsoft 365 Copilot Enterprise license. 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.