How to use Microsoft Copilot in Excel: data analysis made easy

Copilot in Excel has grown up a lot. It started as a side panel that could suggest a chart. Today you can type a prompt straight into a cell, ask Copilot to work through a multi-step task for you, and run Python analysis without writing a line of code.

Here's what's available, how to get started and the habits that make the difference between useful answers and confident nonsense.

What you need to get started

Copilot Chat is available to most Microsoft 365 business users at no extra cost, and it can help with general Excel questions. To have Copilot work directly with your workbooks, you'll need a Microsoft 365 Copilot licence.

Copilot works best when your file is saved to OneDrive or SharePoint, and your data is set out as a proper table with clear column headings. Open the workbook, select the Copilot button on the Home tab and start asking questions.

The COPILOT function

The COPILOT function lets you write a plain-English prompt into a cell, just like any other formula. The syntax is =COPILOT(prompt, [data]), where the data reference is optional.

It's ideal for jobs that used to need manual reading, such as sorting customer feedback into themes, tagging transactions by category or writing a one-line summary for every row. Because it's a formula, the results update when your data changes.

Agent Mode for multi-step work

Agent Mode lets Copilot plan and carry out a task across several steps. You might ask it to build a monthly sales tracker with a summary sheet and charts, and it'll create the tables, formulas and visuals, checking its own work as it goes.

It's a big time saver for building models from scratch, but you should still review what it's built before anyone relies on it.

Six everyday ways to use Copilot in Excel

1. Clean up messy data. Ask it to "remove duplicate rows", "split full names into first and last name columns" or "highlight rows with missing values".

2. Write and explain formulas. Describe what you want, such as "show the average order value for each region over the last 12 months", and Copilot writes the formula. You can also ask it to explain an inherited formula in plain English.

3. Visualise trends. "Create a chart of monthly revenue against target" gets you a starting point in seconds.

4. Forecast. Ask Copilot to "forecast next quarter's sales based on the last two years" or "show seasonal patterns in this data".

5. Run deeper analysis with Python. Copilot can use Python in Excel to run more advanced analysis, such as correlations or clustering, and explain the result in plain English.

6. Summarise for other people. "Summarise the three most important trends in this sheet for the leadership team" turns a workbook into talking points.

Habits that get better results

• Be specific. "Show Q2 sales by product category" beats "show sales".
• Keep data tidy. One table, one header row, no merged cells. Copilot's answers are only as good as the structure underneath.
• Check the output. Treat Copilot like a capable new starter. Spot check its figures, especially for anything going to the board or the auditors.
• Use it for the slow parts. It's most valuable on the repetitive work that eats your time, not the judgement calls you're already good at.

When Excel isn't the right home for your data

If the same figures are being copied between several spreadsheets every month, Copilot will speed that up, but it won't fix it. Bringing that data into Microsoft Fabric with reporting in Power BI gives everyone a single source of truth. A data readiness review is a sensible first step if you're not sure where you stand.

For help getting Copilot working across your finance and operations teams, see our Microsoft Copilot consultancy or get in touch.