Copilot in Excel: smarter than you think, and a different tool than it was in 2024

Using Copilot in Excel through the first half of 2025 was a frustrating experience for a lot of people. Instead of acting like an assistant, the way we'd been promised, it would often just tell us how to do the job ourselves. In most cases, the user still had to do all the actual work.

Plenty of people tried it, came away underwhelmed, and decided Copilot in Excel offered no more value than a quick Google search.

But a lot has changed over the past year, and Copilot in Excel is a different tool today than it was in early 2025. With the agent mode "Edit with Copilot", which rolled out toward the end of 2025, Copilot can finally do real work directly in the sheet. It can plan tasks, run several steps in sequence, and show you what it's doing as it goes.

Microsoft has reported that the thumbs-up rate among Copilot in Excel users has climbed 65% since the newest Copilot experience launched.

Copilot in Excel can probably do more than you think, but still less than the marketing might lead you to believe.

The point of this article is to give you an up-to-date picture of what Copilot in Excel can actually do as of June 2026, and how to use it for data analysis.

The article covers three main areas:

• What Copilot in Excel can do today.

• The different Copilot experiences for data analysis (what's available and what works depends on where you use Copilot).

• How to get better results from Copilot in data analysis: a few practical tips.

What Copilot in Excel can do today

Copilot is at its best right inside the Excel app, working with clean data in table format. Within those limits it's genuinely useful, especially when you give it clear tasks and ask for a specific action.

Here are the main jobs Copilot handles reasonably well as of June 2026.

1. Basic data cleanup

Copilot can help with practical tidying: removing duplicates, fixing inconsistent capitalization, turning a plain dataset into a table, sorting out date or number formatting, and making the sheet easier to analyze.

This isn't the same as cleaning up a completely chaotic, unstructured spreadsheet. Even so, these abilities can help with a lot of common problems in semi-structured datasets.

Example prompts:

"Remove duplicate rows from this table."

"Standardize the date format in the Date column."

"Fix the inconsistent capitalization in the Name column."

2. Summarizing large datasets (in table format)

Copilot can give you a short summary of what a dataset contains, what's changed, and which numbers are worth a closer look. This helps when, say, someone sends you a spreadsheet you've never seen and you want to get your bearings first.

Example prompts:

"Give me a short summary of what this dataset appears to contain."

"Summarize the changes in revenue and costs from last fiscal year."

"What are the three most important things I should notice before I write a report for leadership?"

3. Spotting trends, changes, and outliers

Copilot can find patterns and trends in your numbers, such as month-to-month changes, which categories are growing fastest, or which values stand out in other ways. This helps when you need a first overview before deciding what to dig into.

Example prompts:

"What are the main trends in the sales figures over the past 12 months?"

"Compare marketing costs by quarter and point out the biggest changes."

"Which weeks had the most overtime, and which departments account for most of the increase?"

4. Finding the right formula and explaining it

Copilot can suggest formulas based on the data in your sheet, insert new calculated columns, and explain what the formula does. This helps when you know what you want to calculate but can't remember exactly which Excel formula to use.

Example prompts:

"Add a column that flags every order more than 30 days overdue, and explain the formula."

"Write a formula that calculates average monthly cost per customer based on the Customer, Month, and Cost columns."

"Write a formula that matches employee names to employee IDs from the other table."