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, and 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 and Copilot does the rest. Since the beginning of 2026, Microsoft has massively expanded the AI features in Excel: data analysis in natural language, automatic formula generation, interactive charts and an Agent Mode that carries out multi-step analyses 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
Before you start, you need two things: a Microsoft 365 Copilot licence and Excel on the web or as a desktop app in a current version.
Here is how to open Copilot: 🚀
- Open an Excel workbook in the desktop app or at excel.cloud.microsoft.
- In the ribbon, click the Home tab.
- On the far right you will see the Copilot button with the coloured icon.
- Click it and the Copilot pane opens on the right.
If the button is missing, check your licence at microsoft365.com. Copilot in Excel is part of the Microsoft 365 Copilot licence.
Step 2: format your data as a table
This is the most important step and many people skip it, then wonder why Copilot does not work properly. Copilot works best with structured tables, not with plain cell ranges.
Here is how to format your data as a table: 📊
- Click any cell inside your data.
- Press Ctrl+T (or go to Home > Format as Table).
- Confirm whether the first row contains headers.
- Click OK.
Excel turns your data into a formatted table. Copilot now recognises every column with its label and can access it precisely.
Tip: give your columns meaningful names. Instead of «Column C», use «Revenue CHF» or «Region». Copilot will understand your questions much better.
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?»
Copilot analyses the data and answers directly in the pane, with a summary, a chart or a suggestion for a new column. You can take the answer straight into your file with Add to sheet.
The Get insights (Advanced Analysis) feature turns Copilot into your personal data analyst. Click Insights in the Copilot pane and Copilot automatically delivers trends, patterns and anomalies from your data, without you having to formulate a single question.
Step 4: generate formulas and have them explained
In the past you spent hours looking for the right formula for a calculation. Today you describe the result you want and Copilot writes the formula.
Here is how to generate a formula: ✍️
- In the Copilot pane, click Add formula column.
- Describe what should be calculated, for example: «Calculate the margin in per cent from Revenue CHF and Cost CHF.»
- Copilot generates the formula and shows you a preview before you insert it.
- Click Insert column and the new column appears in your table straight away.
Copilot will also explain how the formula works if you ask. Simply type «Explain this formula», which is ideal when you want to understand what sits behind the calculation.
Practical examples for formula requests:
- «Create a column that checks whether the 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.»
Copilot uses the new Excel functions such as GROUPBY and PIVOTBY, which have been generally available since 2025 and produce dynamic reports directly as a formula.
Step 5: create charts and visualisations
Numbers turn into pictures and Copilot handles that too. Here is how to create a chart: 📈
- Type in the Copilot pane: «Create a bar chart with revenue per region.» or «Show me a trend of monthly sales as a line chart.»
- Copilot creates the chart and inserts it as an object into your file.
- You can adjust colour, axis labels and title directly in the chart.
New in 2026 is the Data Storytelling feature. Copilot can generate a sequence of charts with explanatory text, a kind of mini presentation straight out of Excel.
Step 6: Think Deeper for complex analyses
For more demanding analyses you switch on Think Deeper mode in the Copilot pane. It uses an advanced reasoning model and builds a structured analysis plan before it answers.
Here is how to use Think Deeper: 🔍
- Type your question in the Copilot pane.
- Instead of Send, click the small dropdown arrow next to it and choose Think Deeper.
- Copilot works a few seconds longer and then delivers a more detailed, multi-step analysis.
Example: «Analyse customer profitability by region and product category and show me where we have the most potential.»
Practical tip: questions instead of formulas
If you do not know how to write a formula, just describe the result you want in plain language: «I want to know which customers generated more than CHF 10,000 in revenue last year but less than CHF 5,000 this year.» Copilot understands that and creates a filtered view or a new column with exactly that logic. 💡
Conclusion
In 2026, Copilot in Excel is no longer a nice gimmick, it is a real productivity boost for everyone who works with data on a regular basis. You do not have to memorise formulas, configure pivot tables by hand or build a chart from scratch. You describe what you need and Copilot delivers. Try it now: open your next Excel file, format the data as a table and ask Copilot your first question. 🚀
