Copilot in Excel 2026: analyse your data without any prior knowledge

You open an Excel file with a hundred rows of sales data and you already know what is coming: the next hour belongs to fiddling about with VLOOKUP, pivot tables and nested IF formulas. Somewhere in there hides a mistake you only notice at the very end. Then you start all over again.

That is exactly what changes with Copilot in Excel. Instead of memorising formulas, you write in plain language what you want to know. Copilot does the rest. Microsoft rebuilt Copilot in Excel substantially in 2026: the former Agent Mode is now built right into Copilot. Copilot plans multi-step tasks, changes the workbook itself and checks the result. On top of that there are three modes that let you control how much Copilot is allowed to do on its own.

In this post I show you how to use Copilot in Excel step by step for your daily work, even if you are not an Excel pro.

Step 1: open Copilot in Excel

You get the full feature set with a Microsoft 365 Copilot licence. According to Microsoft, many business subscriptions without the add-on also have basic access, in which case Excel shows «M365 Copilot (Basic)». Exactly what is enabled is up to your IT team. You also need Excel for Windows, Mac or the web in a current version.

Here is how to open Copilot: 🚀

1. Open an Excel workbook in the desktop app or at excel.cloud.microsoft.
2. Click the Copilot icon in the lower-right corner of the Excel window.
3. The Copilot pane opens. In the upper-left corner you can pick the model if needed, by default it is set to Auto.
4. Use the mode menu to choose how Copilot should work (more on that in step 6).

If the icon is missing, check your licence at microsoft365.com or ask your IT team. Depending on the organisation, Copilot in Excel may not be enabled.

Step 2: structure your data cleanly

Many people skip this step and then wonder why Copilot gets it wrong. Copilot works most reliably with cleanly structured data: a single header row, no empty rows or columns in the middle of the data. The easiest way to get there is a formatted table.

Here is how to format your data as a table: 📊

1. Click any cell inside your data.
2. Press Ctrl+T (or go to Home > Format as Table).
3. Confirm whether the first row contains headers.
4. Click OK.

Excel turns your data into a formatted table. That way Copilot recognises every column with its label.

Tip: give your columns meaningful names. Instead of «Column C», use «Revenue CHF» or «Region». Use those names in your questions too, Microsoft itself recommends this in its tips for good prompts.

Step 3: analyse data with natural language

This is where it gets interesting. You can ask Copilot questions about your data just as if you were writing to a colleague. Open the Copilot pane and type something like this: 💬

• «Which region had the highest revenue in the first quarter?»
• «Show me the five largest customers by order value.»
• «Are there any outliers in the Revenue CHF column?»

In Chat only mode, Copilot analyses the data and answers in the pane without changing the workbook. In edit mode (Allow editing), Copilot puts the result straight into the workbook, for example as a new column, formatting or a chart. Start with a broad question and then get more specific if the result is not right yet.

Step 4: create formulas and have them explained

You used to spend a long time hunting for the right formula. Today you describe the result you want, Copilot writes the formula and, if you ask, applies it across several sheets.

Here is how: ✍️

1. Choose edit mode (Allow editing).
2. Describe what should be calculated, for example: «Add a column that calculates the margin in per cent from Revenue CHF and Costs CHF.»
3. Copilot inserts the column with the formula. Check a few rows by hand.
4. If something is off, undo the change or ask a follow-up question.

Copilot will also explain how a formula works if you ask. Just type «Explain the formula in column F», ideal when you want to understand what is behind the calculation.

Practical examples of formula requests:

• «Create a column that checks whether revenue is above average.»
• «Add a column that extracts the month name from the date in column A.»
• «Calculate the cumulative revenue from row to row.»

Important: Copilot only edits when the calculation options are set to Automatic (Microsoft FAQ). If you have set calculation to manual, switch it back first.

Step 5: create charts and PivotTables

Numbers become pictures and Copilot takes care of that too. According to Microsoft, Copilot creates and edits charts, PivotTables and shapes that stay linked to the source data. Here is how to create a chart: 📈

1. In edit mode, type: «Create a bar chart showing revenue by region.» or «Show me the trend in monthly sales as a line chart.»
2. Copilot creates the chart and inserts it into your workbook.
3. You adjust colour, axis labels and title directly in the chart or ask Copilot to do it.

For several analyses at once you can also ask for a whole report, for example: «On a new sheet, create an overview with revenue by region, the top 5 customers and the monthly trend.»

Step 6: choose the right mode

Copilot in Excel has three modes (Microsoft Support). By default it opens in edit mode.

• Allow editing: for multi-step tasks such as reshaping data, merging sheets or building reports with several elements. Copilot changes the workbook directly.
• Plan: Copilot first creates a structured plan. You review it before anything changes.
• Chat only: Copilot analyses your data and provides insights without changing the workbook.

Here is how to use Plan for a complex analysis: 🔍

1. In the Copilot pane, choose Plan mode.
2. Describe your goal, for example: «Analyse customer profitability by region and product category and show me where we have the most potential.»
3. Read through the plan, adjust it if needed and then approve it.

Because Copilot writes directly into the file in edit mode, Microsoft advises caution with sensitive or shared files. Undo, version history and change history help you trace changes and roll them back.

Practical tip: questions instead of formulas

If you do not know how to write a formula, simply describe the result you want in plain language: «I want to know which customers had more than CHF 10,000 in revenue last year but less than CHF 5,000 this year.» Copilot turns that into a filtered view or a new column with exactly this logic. Work on a copy until you trust the result. 💡

Conclusion

In 2026, Copilot in Excel is no longer a nice gimmick, it is a real help for anyone who works with data regularly. You do not have to memorise formulas, configure pivot tables by hand or build charts manually. You describe what you need. Copilot delivers, you check. Give it a go right now: open your next Excel file, get the data into shape and ask Copilot your first question in Chat only mode. 🚀