Microsoft Copilot in Excel: What It Can't Do for Accountants (and the Workarounds)

Guide · Updated July 2026 · ~11 min read

You turned on Copilot expecting it to reconcile the bank, tie out the trial balance and pull last month's invoices from QuickBooks. It summarised a table and offered you a chart. At $30.00 user/month, paid yearly, you deserve a straight answer about where the line sits.

Below: what it does well, the limits that matter to accountants — sourced to Microsoft's documentation — a workaround for each, and whether it's worth the licence.

What Copilot in Excel actually does well

Per Microsoft's Get started with Copilot in Excel page, it:

• Uses Excel's native tools — tables, charts, PivotTables, formulas — not static pasted text.
• Creates charts, PivotTables and shapes with editable source-data links, so the output is a real Excel object.
• Performs web searches with citations.
• Retrieves data from OneDrive, SharePoint and local Excel files.
• Offers three modes — edit, plan and chat — from the Copilot icon in Excel's lower-right corner.

The jobs it does well are the five-minute ones you'd rather not do: "summarise this table by region", "build a PivotTable of spend by supplier", "flag anything over 60 days". There is also a worksheet function — the part most accountants have misunderstood.

The COPILOT() worksheet function — read this before you plan around it

The function exists. Its syntax is:

=COPILOT(prompt_part1, [context1], [prompt_part2], [context2], ...)

prompt_part (required) is text describing the task; context (optional) is a grid reference — a cell or a range:

=COPILOT("Classify each expense description as Travel, Software, Fees or Other.", B2:B40)

But Microsoft's own COPILOT function documentation attaches constraints that rule it out for most accounting work.

It isn't generally available. "This function is currently available in the Frontier program and the Microsoft 365 Insider program", and it needs a Microsoft 365 Copilot add-on licence, or Microsoft 365 Premium for individuals. On a standard tenant Excel will not recognise the name.

It is rate-limited. "You can calculate up to 100 COPILOT functions every 10 minutes" — fill it down a 900-row expense ledger and that is roughly an hour and a half of recalculation.

Sensitivity labels block it. "The COPILOT function cannot calculate in workbooks labelled Confidential or Highly Confidential" — precisely how client files, payroll and management accounts get classified in most firms.

It is non-deterministic. "The COPILOT function's model will evolve and improve in the future and formula results may change over time, even with the same arguments." Microsoft's mitigation is to convert results to values. For a workpaper, reperformance next quarter may not reproduce this quarter's figure from the same inputs.

It is context-blind. "The COPILOT function only has access to data provided through the context arguments. It does not have access to: Other data from the workbook, Enterprise data" — not your Chart of Accounts tab, unless you pass it.

Microsoft also publishes a when not to use it list: avoid it for numerical calculations requiring accuracy or reproducibility (use SUM, AVERAGE, IF); avoid it for lookups (use XLOOKUP); avoid it where the answer needs workbook context beyond the ranges you passed; and avoid AI-generated outputs for financial reporting, legal documents, or other high-stakes scenarios with legal, regulatory or compliance implications. That is honest scoping rather than a flaw, but it defines the boundary: the vendor is telling you not to use its AI function for financial reporting.

Workaround: AI for judgement, formulas for numbers

1. Use =COPILOT(...) only to categorise or normalise messy text — expense descriptions, supplier name variants.
2. Copy the whole result range and paste it back over itself with Paste Special > Values, so it is frozen, auditable and reproducible.
3. Do every number that lands in a statement with a real formula: =SUMIFS(Ledger[Amount], Ledger[Category], $A2, Ledger[Date], ">="&$B$1).
4. Do every lookup with =XLOOKUP($A2, Accounts[Code], Accounts[Name], "NOT FOUND") — never with a prompt.

Excel skills for accountants covers the formula set that should be doing this work.

Limitation 1: it reasons over what you point it at

Copilot's chat is anchored to the data in front of it. It is not holding a model of your 14-tab consolidation with its intercompany eliminations and scenario switches. Ask "why is Q3 gross margin down 180bps" across a linked model and the answer will be scoped far more narrowly than the question.

Workaround. Build one flat, AI-readable tab: use Power Query to append your working tabs into a single long table with a Source column — our Power Query for beginners walkthrough covers the append step — then ask your questions of that table. Microsoft's formatting guidance is essentially one point: name the columns or ranges you want Copilot to work with. Formatting the range as an Excel Table (Ctrl+T) is good hygiene regardless, though current Microsoft pages do not state a Table as a hard requirement — practice, not a rule.

Limitation 2: no documented connection to QuickBooks, Xero or any ledger

The data sources Microsoft documents for Copilot in Excel are OneDrive, SharePoint, local Excel files and the open web. No accounting-system connector appears anywhere in that documentation. "Copilot, pull all unpaid supplier invoices from Xero" has no documented route.

Nor does Microsoft document whether Copilot can create or modify Power Query queries or drive ODBC and SQL connectors. It hasn't said it can't — it simply hasn't said it can, which for the capability most relevant to accountants is itself informative.

Workaround A — the free route. Export from the ledger, import with Power Query, refresh on demand.

1. In QuickBooks Online or Xero, run the report and export to CSV or XLSX.
2. Save it to a fixed folder, e.g. `C:\Finance\Exports`, with a consistent filename.
3. In Excel, Data > Get Data > From File > From Folder, point at that folder, then Combine & Transform Data.
4. Clean up once inside Power Query — remove header junk, set data types, unpivot the month columns.
5. Next month, overwrite the file and hit Data > Refresh All.

It costs nothing. Step-by-step in how to connect QuickBooks and Xero data to Excel; merging multiple Excel files into one covers the folder-combine mechanics, and alternatives to the retired QuickBooks Power Query connector covers the QuickBooks-specific route.

Workaround B — its ceiling. It is one-directional. You cannot push a corrected journal, a batch of invoices or a payment allocation back without re-keying it into the accounting system's own UI or import template — the bottleneck in a close with fifty adjustments.

Workaround C — an add-in built for the ledger. HISAB 360 targets that gap: a paid Excel add-in (VSTO) that docks an AI chat panel inside Excel and holds authorised OAuth connections to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage, with read and write access. It pulls invoices, bills, payments, journals, contacts and reports into the sheet and posts or updates them back where the API allows, and it writes and applies the Excel work itself — formulas, Power Query M, PivotTables, Power Pivot tables and DAX measures, charts and VBA.

Judge it on its limits: Windows desktop Excel only — not Mac, not Excel on the web; paid (Own AI $7/month or $60/year if you bring your own AI provider key, HISAB AI Basic $10/month, HISAB AI Plus $20/month, or a $199 perpetual licence, with a limited free trial credit allowance — there is no free tier); you authorise each connection via OAuth yourself; and any ledger write should be reviewed before you approve it and reconciled afterwards. Write-back details and pricing.

Limitation 3: nothing goes back to the ledger

Even where Copilot could read your accounting data, its output surface is the workbook: cells, PivotTables, charts. It has no concept of a journal entry, a posting period, a control account or an approval workflow.

Workaround. Stage in Excel, post through the ledger's own import.

1. Build a staging tab whose columns exactly match your journal import template — date, account code, description, debit, credit, reference, tracking category.
2. Put two checks in cells outside the data columns — say H1 and H2, so they cannot reference themselves. =SUM($D$2:$D$1000)-SUM($E$2:$E$1000) must be zero, and so must =SUMPRODUCT(($A$2:$A$1000<>"")*(COUNTIF(Accounts[Code],$A$2:$A$1000)=0)), which confirms every code you used exists.
3. Export the tab as CSV and use the ledger's bulk journal import — importing journal entries into QuickBooks Online from Excel walks through the QBO version.
4. Retain the CSV and the workbook as the workpaper.

Slow, but defensible — and those checks are what people skip and regret.

Limitation 4: licence cost, stacked

Copilot in Excel is not something you already own. From Microsoft's own pages:

• Microsoft 365 Copilot (enterprise add-on): $30.00 user/month, paid yearly; $31.50 paid monthly. "A separate license for a qualifying Microsoft 365 plan is required."
• Microsoft 365 Copilot Business (small-business add-on): $18.00/user/month paid yearly (promotional), on top of an eligible Microsoft 365 Business plan.
• Business Standard with Copilot: $23.50/user/month paid yearly ($20.30 no-Teams). Business Premium with Copilot: $32.00/user/month paid yearly ($28.80 no-Teams).

Eligible plans: Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft 365 Copilot subscription, or a Copilot Chat-eligible Microsoft 365 or Office 365 plan.

Note the additive structure — the add-on sits on top of the base plan. Ten finance seats on the enterprise add-on is $3,600 a year in Copilot licences alone, before the underlying Microsoft 365 E3 or E5 seats. Buy two or three seats first and run a four-week trial against a written list of tasks.

Limitation 5: the file-state gotchas that waste an afternoon

Calculation mode. Per Microsoft's Copilot in Excel FAQ, "Copilot editing is only supported when Calculation Options are set to Automatic." If you work with Formulas > Calculation Options > Manual — most modellers do — edit mode is dead until you switch back, so do the Copilot work on an extracted flat copy.

File format. Legacy formats and Strict Open XML Spreadsheet are unsupported; Microsoft's advice is "Save the file in a modern format such as Excel Workbook (.xlsx)." Use File > Save As.

SharePoint checkout. A file checked out in SharePoint produces an "Unsupported file state" error — check it back in, then close and reopen it. Where your organisation mandates checkout, Copilot will not function on Windows or Mac and you must use Excel on the web.

AutoSave and OneDrive — Microsoft's own docs disagree. You will read everywhere that Copilot requires AutoSave and therefore that the file must live on OneDrive; a footnote on Microsoft's consumer comparison page still says so. The current Copilot in Excel tips page says the opposite: "Copilot in Excel runs in workbooks with autosave enabled or disabled." The product documentation is newer and more specific, so the requirement looks like it was true and has since been relaxed — verify it on your own tenant.

How to turn Copilot off in Excel

Close the pane via the Copilot icon in Excel's lower-right corner. Permanent removal is a licensing action — remove or reassign the Copilot licence in the Microsoft 365 admin centre, which on a managed device means asking IT. Separately, labelling a workbook Confidential or Highly Confidential already blocks the COPILOT function from calculating in it.

Is Copilot worth it for a finance team?

Copilot is enough when your work is analysis within a workbook you already have, when you want a fast suggestion for a formula you half-remember, and when the output is internal management information rather than a filed statement.

An accounting-specific tool fits better when the bottleneck is getting data in and out of your ledger rather than manipulating it once it is in Excel; when you post adjustments back and re-keying is the real cost; when you reconcile bank statements against ledger rows monthly — a matching problem with amount and date tolerance, many-to-one deposits and fuzzy descriptions (reconciling a bank statement in Excel with AI covers the mechanics either way); or when PDF statements and supplier invoices have to become structured ranges first.

These aren't mutually exclusive: plenty of teams keep Copilot for analysis and add something like HISAB 360 — Windows desktop Excel only, and paid — for the ledger connection. Best AI Excel tools for accountants compares the field, what AI in Excel actually does for accountants sets out where the technology genuinely earns its keep, and Excel automation for finance teams covers the non-AI automation you should have first.

The honest summary: Copilot in Excel is a good Excel assistant. It is not an accounting assistant — and Microsoft's own documentation, telling you to use SUM and XLOOKUP for accuracy and to avoid AI outputs in financial reporting, says so more plainly than any competitor would dare.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.