Copilot in Excel for Business Reporting: What It Can and Can't Do in 2026
10 min read
Copilot in Excel changed a lot this year. In April 2026 Microsoft made its agent features generally available, and Copilot can now add sheets, write formulas, build PivotTables and charts directly in your workbook instead of mostly answering questions in a side pane. Then in September the =COPILOT() worksheet function was retired. If you run monthly reporting for a small or mid-sized business, you are probably wondering whether Copilot can take that work off your plate. Below is how it works now, which licence you need, where it helps, and where I would still not trust it with reported numbers.
What Copilot in Excel is in 2026
Microsoft announced on 22 April 2026 that the agentic capabilities in Word, Excel and PowerPoint were generally available and had become the default Copilot experience for Microsoft 365 Copilot and Microsoft 365 Premium customers. In Excel, that means Copilot plans a task, carries it out in the workbook, and checks the result, while you watch the changes appear.
Edit, plan and chat modes
Copilot in Excel has three modes, and it opens in edit mode by default:
- Edit mode changes the workbook directly. Use it for multi-step jobs such as reshaping data, merging sheets or building a report with several parts.
- Plan mode writes out a step-by-step plan first, so you can review and confirm the approach before anything changes.
- Chat mode analyzes your data and answers questions without touching the workbook.
Not every platform has every mode:
| Platform | Modes available (October 2026) |
|---|---|
| Excel for Windows, Mac and the web | Edit, plan and chat |
| Excel for iPad | Chat and plan (edit is rolling out) |
| Excel for iPhone | Chat (edit is rolling out) |
| Excel for Android | Chat |
Which licence you need
According to Microsoft’s Copilot in Excel FAQ, any of these gives you access:
- a Microsoft 365 Personal or Family subscription with an AI credits plan
- Microsoft 365 Premium
- a commercial Microsoft Copilot subscription
- a Copilot Chat-eligible business or enterprise plan, which includes Microsoft 365 Business Basic, Business Standard and Business Premium
There are two levels. Without the paid add-on, Excel labels your experience “M365 Copilot (Basic)” and you get standard access. With the add-on it says “M365 Copilot (Premium)” and you get priority access. For companies with up to 300 users, the add-on is Microsoft 365 Copilot Business, listed in the US at $21 per user per month on an annual commitment. Microsoft is offering it to eligible existing customers at $18 until 31 December 2026. The enterprise version, Microsoft 365 Copilot, is listed at $30 per user per month. Prices vary by country and exclude tax, so check the pricing page for your market.
You can also choose which AI model does the work. Copilot in Excel supports Claude models from Anthropic and GPT models from OpenAI, or Auto. The model switcher needs a commercial Copilot subscription or Microsoft 365 Premium, and for Claude your admin first has to enable Anthropic as a Microsoft sub-processor.
The COPILOT function is gone
The =COPILOT() function, which let you send a prompt from a cell, was only ever a preview for Frontier and Insider users. Microsoft removed it on 14 September 2026. Workbooks that used it keep their last calculated values, but as soon as one of those cells recalculates it returns #NAME?. Microsoft suggests asking Copilot in the pane instead, for example “Summarize the feedback in A2:A20”. If any of your templates rely on it, search for COPILOT( with Find (Ctrl+F, Look in: Formulas) and replace those cells with values or regular formulas.
How to use Copilot in Excel for a monthly report
Open Copilot with the Copilot icon in the lower-right corner of Excel. A few settings trip people up before they type the first prompt:
- Copilot editing only works when Calculation Options are set to Automatic (Formulas > Calculation Options).
- Save the file as a normal
.xlsxworkbook. Strict Open XML files give an “Unsupported file state” message. - If your SharePoint library requires check-out, Copilot on Windows and Mac won’t work on files from it. Excel for the web still does.
Here is a small expense table formatted as an Excel table named Expenses:
| Month | Cost centre | Category | Amount |
|---|---|---|---|
| 2026-08 | Sales | Travel | 1,240 |
| 2026-08 | Sales | Software | 380 |
| 2026-08 | Operations | Rent | 2,500 |
| 2026-08 | Operations | Software | 220 |
| 2026-09 | Sales | Travel | 960 |
| 2026-09 | Sales | Software | 380 |
| 2026-09 | Operations | Rent | 2,500 |
| 2026-09 | Operations | Utilities | 410 |
A prompt in plan mode could be:
Create a PivotTable on a new sheet called Summary that shows total Amount
by Cost centre in rows and Month in columns, with grand totals.
Then add a clustered column chart of the same data.
Read the plan, approve it, and let Copilot build the sheet. Before the summary goes to anyone, check it against figures you calculate yourself. The correct result for this data is:
| Cost centre | 2026-08 | 2026-09 | Total |
|---|---|---|---|
| Operations | 2,720 | 2,910 | 5,630 |
| Sales | 1,620 | 1,340 | 2,960 |
| Total | 4,340 | 4,250 | 8,590 |
Two formulas do the checking. The grand total of the PivotTable must match the source:
=SUM(Expenses[Amount])
Result: 8590
And any single cell can be tested with SUMIFS, for example Operations in September:
=SUMIFS(Expenses[Amount], Expenses[Cost centre], "Operations", Expenses[Month], "2026-09")
Result: 2910
With eight rows you can check this by eye. With 40,000 rows from three systems you can’t, which is why these control totals should be part of the report itself.
What Copilot in Excel does well
Copilot is good at the first draft. Microsoft lists the things it can do: edit sheets, write formulas across several sheets, create charts, PivotTables and shapes linked to the source data, find trends and outliers, sort and filter, pull information from the web with citations, and import data from other workbooks on OneDrive, SharePoint or your computer.
In reporting work, that makes it useful for:
- explaining a formula someone else wrote, which saves time with an inherited workbook
- writing a first version of a tricky formula, such as a lookup with two conditions
- building a quick PivotTable and chart to explore a dataset before you design the real report
- classifying or summarizing text columns, like expense descriptions or customer comments
Custom skills let you describe how Copilot should handle a process you repeat. Microsoft also says Copilot can take on multi-step jobs like building a dashboard or a planning template from scratch.
Where Copilot falls short for reported numbers
Microsoft’s own FAQ is direct about it: Copilot “can sometimes make mistakes, misinterpret information, or produce inaccurate results”, and the FAQ advises avoiding it “for decisions in sensitive areas such as finance, legal, or medical topics.” Every formula and table it creates needs a review before you rely on it.
An independent test points the same way. In January 2026, the financial modelling firm SumProduct gave Excel’s agent mode and the Claude add-in for Excel the same prompt: build a three-statement model with a DCF valuation. Agent mode got the WACC and discounting right, but its fixed asset schedule calculated depreciation inconsistently, sometimes from revenue and sometimes from the asset balance. Claude produced a cleaner layout but miscalculated WACC and free cash flow. SumProduct’s conclusion was that both tools speed up the early stages and neither can work unsupervised.
For month-end reporting, I see a few more practical limits:
- Copilot’s output depends on the prompt and the model, and it is generated fresh each time. A report your management relies on should give the same answer from the same data every month.
- When you save Copilot’s changes, everyone with access to the file sees them, including people coauthoring at that moment. Undo and version history are the way back.
- It works inside one workbook (plus files you point it to). It does not replace a proper import from your accounting system, ERP or data warehouse with checks for missing or duplicate records.
- Features move fast. The COPILOT function was a preview and was then withdrawn, so build recurring processes on features that are generally available.
Copilot in Excel vs Claude
Copilot in Excel can already run on Claude models if your licence and admin settings allow it. Anthropic also sells a separate add-in, Claude for Excel, on its paid Pro, Max, Team and Enterprise plans. Its documentation says it is not recommended for final client deliverables without human review or for audit-critical calculations without verification, and it doesn’t support data tables, macros or VBA.
Data privacy questions to ask first
Before you switch Copilot on for a finance team, get answers to these questions. As a CISA-certified IT auditor, these are the ones I would ask:
- Who can see what? Copilot only surfaces data the user already has at least view access to. If your SharePoint permissions are loose, Copilot can surface files that people technically had access to but never opened. Tidy permissions first.
- Is our data used for training? Microsoft states that prompts, responses and data accessed through Microsoft Graph aren’t used to train the foundation models.
- Where is it processed? Microsoft Copilot is an EU Data Boundary service for EU customers, and it is covered by Microsoft’s GDPR commitments. Anthropic models are currently excluded from the EU Data Boundary, so an EU company that enables Claude inside Copilot should make that decision knowingly.
- What is stored? Prompts and responses are kept as Copilot activity history, which admins can search and put under retention policies in Microsoft Purview.
- What about files from outside? Anthropic warns that downloaded templates and vendor files can contain hidden prompt injections that try to trick the add-in into extracting or changing data. Microsoft says Copilot uses classifiers to help block prompt injection, though not in every scenario, so treat external files with care whichever assistant you use.
If your organization has turned off connected experiences that analyze content, Copilot won’t appear in Excel at all.
Copilot vs a properly built automated report
| Copilot in Excel | Automated report (Power Query, formulas, checks) | |
|---|---|---|
| Best for | Drafts, exploration, one-off analysis | The monthly report management relies on |
| Same result each month | Not guaranteed, output is generated each time | Yes, same steps on new data |
| Data import | Workbooks and files you point it to | Direct connections to exports, folders, databases |
| Controls | You review each change | Built-in checks for totals, missing records, duplicates |
| Monthly effort | Prompt, review, fix | Click Refresh, review the checks |
| Cost | Included in eligible business plans, more with the Copilot Business add-on | One-off build |
The two work well together. Copilot can draft a formula or a chart while you design the report. The report itself should be built so that the numbers come out the same every time and someone can explain where each one came from.
FAQ
Is Copilot in Excel good for financial reporting?
It is good for drafting and exploring. Microsoft itself advises against relying on it for decisions in sensitive areas such as finance without review, and independent tests found errors in AI-built financial models. Use it to speed up the build and keep the final numbers in a checked, repeatable process.
Do I need to pay extra for Copilot in Excel?
Not necessarily. Copilot Chat-eligible business plans such as Microsoft 365 Business Basic, Standard and Premium include standard access. The paid Microsoft 365 Copilot Business add-on adds priority access and features such as Work IQ, model choice and Microsoft’s prebuilt agents.
What happened to the COPILOT function?
Microsoft retired it on 14 September 2026. Existing results stay as cached values, but cells return #NAME? when they recalculate. Use the Copilot pane instead.
Why don’t I see the Copilot button in Excel?
The usual causes are an ineligible licence, an outdated Excel version, admin settings, or connected experiences being turned off. Microsoft’s guide “How to find and enable missing Copilot button in Microsoft 365 apps” walks through the checks.
Can I automate Excel reports using AI?
Partly. Copilot can build parts of a report on request, and custom skills help with repeated tasks. A report that refreshes from your source data with built-in checks, and gives the same answer every month, still needs to be designed and built properly.
Get a report you can trust every month
If your team rebuilds the same report by hand each month, my Report Automation service turns it into a one-click refresh in the Excel you already use, with checks for missing records and totals that don’t add up. It’s a fixed price, agreed before work starts. Not sure whether you need Copilot, an automated report or both? Book a free 20-minute call and tell me what’s slowing you down.