Agent Mode in Excel: How Copilot Turns Raw Data Into Finished Reports

Excel has been the backbone of business analysis for four decades. It’s also one of the hardest tools to use well. XLOOKUP, dynamic arrays, PivotTables, Power Query, conditional formatting with five rules: the capability is there, but most users only ever touch a fraction of it.

Agent Mode in Excel changes that. Instead of asking Copilot for a single formula, you describe the outcome you want, and the agent plans and carries out the steps: building sheets, writing formulas, creating PivotTables and charts, and checking its own work as it goes. It first appeared in preview through Microsoft’s Frontier programme in late 2025 and is now generally available in Excel for the web and desktop.

Here’s what it does, where I’ve seen it save the most time, and the guardrails I recommend before rolling it out to a finance team.

What Agent Mode is

Classic Copilot in Excel responds to one prompt with one suggestion: a formula, an explanation, a chart. Agent Mode is multi-step:

1. It reads the workbook and works out what the data represents (dates, products, regions, values).
2. It plans the steps needed to reach your goal.
3. It carries out those steps: new sheets, formulas, PivotTables, charts and formatting.
4. It checks the results and iterates if something doesn’t add up.
5. It summarises what it did, so you can review it.

The shift is from an AI that helps you think to an AI that helps you do.

Example: from raw export to management report

A finance analyst receives a raw ERP export of 50,000 revenue transactions, with columns for date, customer, product, region, salesperson, value and cost. They need a monthly management report showing revenue and gross margin by region and product category, with variance against budget.

The traditional approach means cleaning data, building PivotTables, writing margin formulas, linking the budget tab and formatting everything: a solid half-day for an experienced analyst. With Agent Mode, the analyst writes:

“Using this transaction data, create a management report showing monthly revenue and gross margin by region and product category, with a variance column comparing to the budget figures in the Budget tab.”

The agent identifies the data and the budget tab, builds the summary, writes the formulas and formats the result in minutes. The analyst’s job shifts from building the report to checking it, and that’s a much better use of their expertise.

Dashboards without the pain

Executive dashboards take days to build well. With Agent Mode they become a conversation:

“Build a one-page executive summary showing this month’s revenue against target, the top 5 products by margin, performance by region and a 12-month trend line.”

Where it really pays off is recurring reports: monthly board packs, weekly trading summaries and quarterly reviews. Once a structure exists, you can ask the agent to refresh it with the new period’s data and apply the same calculations and formatting.

Everyday tasks it handles well

• Conditional formatting: “Colour the variance column red where it’s below -5%, amber between -5% and 0%, and green when positive.”
• Complex formulas: multi-criteria XLOOKUP, SUMIFS and SUMPRODUCT, and FILTER/SORT dynamic arrays, with an explanation of how each part works.
• Data clean-up: splitting columns, standardising dates, removing duplicates and flagging outliers.
• What-if models: building simple scenario tables from assumptions you describe.

Who benefits most

• Finance and FP&A teams who live in formula-heavy models.
• Operations analysts turning raw exports into recurring reports.
• HR and people analytics teams working with headcount and attrition data.
• Non-specialists, such as marketing managers, project managers and supply chain planners, who need advanced analysis occasionally and don’t have time to learn every Excel feature.

Guardrails before you roll it out

Agent Mode works within your Microsoft 365 boundary and respects your existing permissions and sensitivity labels. But it changes workbooks, so put a few simple rules in place, especially in finance:

1. Human review is mandatory. Agree that agent-built outputs are reviewed before they’re shared. Check totals against the source, and spot-check formulas.
2. Work on a copy for critical models. Or rely on version history, so you can always roll back.
3. Define where it can be used. For example: yes for management reporting and analysis, but with extra review for statutory or regulatory submissions.
4. Label sensitive workbooks with Microsoft Purview sensitivity labels so your existing data protection controls apply.
5. Train people to prompt for outcomes, not steps. Describe the report you want, the audience and the checks you expect.

And as with any Copilot feature, results are only as good as the data. If your workbooks pull from stale or duplicate sources, read why Copilot gives wrong answers.

FAQ

Which licence do I need for Agent Mode in Excel?

Agent Mode is part of Microsoft 365 Copilot. Check Microsoft’s current documentation for availability on your plan and platform. See also Copilot licensing explained.

Can Agent Mode make mistakes?

Yes. It checks its own work, but it can misread ambiguous data or requirements. Treat the output as a strong first draft and review it.

Is Agent Mode a replacement for Power BI?

No. Agent Mode speeds up analysis and reporting in Excel. For governed, shared dashboards on central data models, Power BI is still the right tool. See my Power BI dashboard examples.