How to audit and explain Excel formulas with Copilot, Claude or ChatGPT

Copilot in Excel, Claude for Excel and ChatGPT for Excel can all read a formula, describe what it calculates and point to the cells it depends on, and the vendors' documentation also describes error tracing for Claude and ChatGPT. For a controller or analyst who inherits a workbook built by someone else, that turns a slow cell-by-cell reading exercise into a conversation: select a cell, ask what it does, and get an explanation tied to specific references. The documentation of each product, checked on November 4, 2026, confirms these capabilities and also sets out the plans, Excel versions and data-handling terms that apply.

The explanation is only half of an audit. A formula can be described accurately and still be wrong for the business, for example when a range stops one row short of the data or when a commission threshold uses "greater than" where the policy says "at least". Those are the errors that pass a quick review because the formula returns a plausible number without any warning from Excel.

The worked example below uses a small illustrative gross margin workbook with three deliberate formula errors, the prompts to ask each assistant, and the correct figures computed independently, so the reader can test whether the AI found every problem. Excel's own Formula Auditing tools then serve as the second, deterministic check before anyone signs off on the workbook.

··········

⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣

WHY FINANCE TEAMS AUDIT EXCEL FORMULAS BEFORE RELYING ON A WORKBOOK.

Finance workbooks tend to outlive the people who built them. A budget model, a commission calculator or a month-end reconciliation file is copied forward period after period, rows are inserted, tabs are renamed and new data is pasted below old data. Each of those changes can break a formula silently, because Excel only raises an error such as #REF! or #DIV/0! when a reference is no longer valid or a calculation is impossible. A range that simply excludes the newest rows produces a valid number, and the number is wrong.

Formula review is therefore a routine control in close, budgeting and audit preparation. Reviewers typically check that every summary formula covers the full data range, that references which should stay fixed are anchored with dollar signs, that thresholds and rates match the written policy, and that lookups return the intended match. Doing this by hand means reading nested functions, following references across tabs and recalculating samples on the side, which is where an AI assistant inside Excel saves time.

The time saving is in reading and tracing. The judgement about whether a formula reflects the policy, the contract or the accounting treatment stays with the reviewer, because the assistant only sees the workbook and whatever context the prompt provides. Related tasks such as drafting entries follow the same pattern, as covered in the guide on drafting journal entries with Copilot, Claude or ChatGPT and checking them before posting.

··········

COPILOT, CLAUDE AND CHATGPT IN EXCEL: DOCUMENTED FORMULA FEATURES AND REQUIREMENTS.

Microsoft's support article on understanding formulas with Copilot describes a simple flow: open Copilot from the icon in the lower-right corner of Excel, select a cell that contains a formula, and ask Copilot to explain it, or name a different cell in the prompt. Microsoft states that Copilot can explain what a formula does and how. The Copilot in Excel FAQ lists the qualifying licenses as a Microsoft 365 Personal or Family subscription with an AI credits plan, a Microsoft 365 Premium subscription, a commercial Microsoft Copilot subscription, or a Copilot Chat-eligible Microsoft 365 or Office 365 business or enterprise subscription. The same FAQ says Copilot editing is supported only when Calculation Options are set to Automatic, and that Copilot on Windows or Mac does not work for files from a SharePoint site that requires check-out for editing.

Anthropic's documentation for Claude for Excel says the add-in is generally available on the Pro, Max, Team and Enterprise plans. It describes asking Claude to trace assumptions, explain formulas or walk through how a number was derived, with answers that include cell-level citations linking to the referenced cells, and a separate debugging feature that locates the root cause of calculation errors such as #REF! and #DIV/0! and suggests fixes. The documentation lists data tables and macros or VBA operations as unsupported, and says the add-in is not recommended for audit-critical calculations without verification.

OpenAI's announcement of ChatGPT for Excel, published on March 5, 2026 and updated on May 5, 2026 to state general availability across all plans, says the add-in can generate and explain formulas, show how assumptions flow through a model, and trace and fix errors, with answers linked to the cells it references. OpenAI notes that complex formulas or edge cases may still require manual refinement, and that in Enterprise, Edu and Teacher workspaces access is off by default until an admin enables it.

........

Tool	Documented formula features	Plans named in the documentation	Excel versions and limits
Copilot in Excel	Explain a formula in the selected or named cell	M365 Personal or Family with AI credits plan, M365 Premium, commercial Copilot, Copilot Chat-eligible business plans	Windows, Mac, web, iPad, iPhone, Android with varying modes; editing needs automatic calculation
Claude for Excel	Explain formulas, trace assumptions, cell-level citations, debug #REF! and #DIV/0!	Pro, Max, Team, Enterprise	Excel on the web, Windows (Microsoft 365), Mac 16.46 or later; no iPad; no data tables or VBA
ChatGPT for Excel	Generate and explain formulas, show assumption flow, trace and fix errors, linked cells	All plans since May 5, 2026; admin-enabled in Enterprise, Edu, Teacher	Excel add-in; complex formulas may need manual refinement

........

Documentation checked on November 4, 2026. Plan names and availability change often, so the vendor pages are the reference before a team standardizes on one tool. Earlier comparisons on the site, such as Microsoft Copilot vs ChatGPT for Excel in financial reporting, give broader context on how the assistants differ outside formula review.

··········

AN ILLUSTRATIVE GROSS MARGIN WORKBOOK WITH THREE FORMULA ERRORS.

The dataset below is illustrative and was built for this example. It sits in columns A to D of a worksheet, with headers in row 1 and eight transactions in rows 2 to 9. Total revenue is $212,500 and total COGS is $116,380.

........

Row	Region (A)	Product (B)	Revenue (C)	COGS (D)
2	East	Widgets	42,000	25,200
3	West	Widgets	38,500	23,870
4	East	Gadgets	27,300	15,015
5	Central	Gadgets	19,800	11,880
6	West	Services	31,000	12,400
7	Central	Widgets	22,600	13,560
8	East	Services	18,400	7,360
9	East	Gadgets	12,900	7,095

........

Three formulas in the workbook contain errors of the kind that survive a casual review. The first calculates East region gross profit, but its COGS ranges stop at row 8, so the last East transaction loses its cost while keeping its revenue. Because both SUMIFS ranges in the second term have the same size, Excel returns a number and no error.

F2:  =SUMIFS(C2:C9,A2:A9,"East")-SUMIFS(D2:D8,A2:A8,"East")

The second returns a sales commission rate for a representative whose quarterly revenue in cell G2 is exactly $100,000. The written policy pays 5% on revenue of at least $100,000 and 3% on revenue of at least $50,000, but the formula uses strict "greater than" tests.

G3:  =IF(G2>100000,0.05,IF(G2>50000,0.03,0))

The third calculates each line's share of total revenue in column E. It was written in E2 without absolute references and filled down to E9, so the denominator shifts one row with every copy.

E2:  =C2/SUM(C2:C9)      (filled down to E9)

··········

[[ADPLUS_300x250_1]]

PROMPTS FOR EXPLAINING AND TRACING EXCEL FORMULAS WITH AI.

The prompts work the same way in the three add-ins because each one reads the open workbook. The first prompt asks for a plain explanation, which tests whether the assistant reads the ranges literally instead of assuming what the formula is meant to do.

Explain the formula in cell F2 step by step. List every range it uses,
the rows each range covers, and the value each SUMIFS returns.

The second prompt adds the business rule, because no assistant can know the commission policy unless it is stated. Supplying the rule is what allows the AI to compare the logic with the policy instead of only describing it.

Our commission policy pays 5% when quarterly revenue is at least 100,000
and 3% when it is at least 50,000, otherwise 0%. Check whether the formula
in G3 applies this policy correctly for the value in G2, and explain any
difference.

The third prompt covers the whole column and asks for a reconciliation figure the reviewer can test independently.

Review the formulas in E2:E9. Tell me whether the denominators are
consistent across rows, what the column adds up to, and what the formula
in E2 should be so that it can be filled down correctly.

A final prompt asks for a summary the reviewer can file with the workpapers. Asking for cell references in every finding makes the output checkable, and Claude and ChatGPT both document answers that link to the cells they cite.

List every formula issue you found in this sheet with the cell reference,
the current result, the corrected formula and the corrected result.
Do not change any cells.

··········

EXPECTED RESULTS: THE CORRECT FIGURES TO CHECK THE AI AGAINST.

The correct figures below were computed independently from the illustrative dataset, so they can be compared line by line with whatever the assistant reports. A response that misses one of the three issues, or that reports a corrected figure different from these, needs a second look before anyone relies on it.

........

Check	Formula as written	Corrected result	Error amount
East gross profit (F2)	$53,025 (52.71% margin)	$45,930 (45.66% margin)	COGS understated by $7,095
Commission rate (G3) at $100,000	3%, or $3,000	5%, or $5,000	Commission understated by $2,000
Revenue share column (E2:E9)	Adds up to 319.2%	Adds up to 100.0%	Denominator shifts each row

........

East revenue is $100,600 from rows 2, 4, 8 and 9. East COGS is $54,670 across the same rows, but the formula as written reaches only rows 2, 4 and 8, which total $47,575. The corrected formula extends both COGS ranges to row 9.

F2:  =SUMIFS(C2:C9,A2:A9,"East")-SUMIFS(D2:D9,A2:A9,"East")
G3:  =IF(G2>=100000,0.05,IF(G2>=50000,0.03,0))
E2:  =C2/SUM($C$2:$C$9)  (filled down to E9)

With the anchored denominator, the revenue shares for rows 2 to 9 are 19.8%, 18.1%, 12.8%, 9.3%, 14.6%, 10.6%, 8.7% and 6.1%, which add up to 100.0%. With the drifting denominator, the same rows show 19.8%, 22.6%, 20.7%, 18.9%, 36.5%, 41.9%, 58.8% and 100.0%. Only the first row is right in the broken version, which is why a reviewer who checks only the top cell of a filled column can miss the problem entirely.

··········

⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁤

CHECKING AI FORMULA EXPLANATIONS WITH EXCEL'S FORMULA AUDITING TOOLS.

An AI explanation is a reading of the workbook, and the reviewer still needs a deterministic confirmation. Excel's Formula Auditing group on the Formulas tab provides it. Trace Precedents draws arrows from the cells a formula uses, and clicking it again shows the next level; Trace Dependents does the same in the other direction. On F2 in the example, the precedent arrows would show the COGS range ending at row 8, which confirms the first finding without relying on the assistant. Microsoft's documentation notes that tracing into another workbook requires that workbook to be open, and that arrows disappear when the formula is edited or rows are inserted or deleted.

Evaluate Formula, in the same group, steps through a formula one calculation at a time, with Step In and Step Out for referenced formulas. It suits nested IF tests like the commission formula, because the reviewer can watch the comparison G2>100000 evaluate to FALSE for a value of exactly 100,000. Microsoft lists limits: some parts of IF and CHOOSE formulas are not evaluated and show #N/A, formulas with circular references may not evaluate as expected, and volatile functions such as OFFSET, INDIRECT, NOW and TODAY can produce results that differ from the displayed value.

Three error types deserve particular attention when AI reviews a finance workbook. The first is the short range, as in F2, which the assistant can describe correctly while failing to flag it unless the prompt asks which rows each range covers. The second is the policy mismatch, as in G3, which no tool can detect without the policy text. The third is the relative reference that breaks on fill-down, as in column E, where checking the total of the column is the fastest test. The month-end routine described in the guide on month-end close with Copilot in Excel, Claude and ChatGPT uses the same principle of tying AI output to an independent total.

Corrections should be made by the reviewer or approved explicitly before the assistant writes them. Claude's documentation describes overwrite protection and a confirmation step before risky operations, and OpenAI says ChatGPT asks permission before editing a workbook. Microsoft's FAQ adds that saved Copilot changes are visible to anyone with access to the file, including people in a coauthoring session, so edits made during a review are not private drafts.

··········

DATA HANDLING WHEN AN AI ASSISTANT READS A FINANCE WORKBOOK.

Formula review usually happens on live financial data, so the data terms of each add-in belong in the decision. Anthropic's documentation says that when a user signs in with a Claude account, the add-in sends conversations to Anthropic, including the content Claude reads from the workbook and any attached files. Inputs and outputs are deleted on the backend within 30 days, subject to the exceptions in Anthropic's retention policy, and chat history is stored locally in the browser. The add-in does not inherit custom data retention settings an organization may have set, its activity is not included in Enterprise audit logs, and sessions are covered by the Compliance API for Enterprise organizations that have it enabled. Anthropic also warns against using the add-in with untrusted spreadsheets, because downloaded templates or vendor files can contain hidden prompt injections.

OpenAI's announcement states that data shared with ChatGPT Enterprise is not used to train or improve its models by default, and that it is encrypted in transit with TLS 1.2 or higher and at rest with AES-256. The announcement does not set out separate data terms for the Excel add-in outside the Enterprise context, so teams on other plans should check their workspace data controls. Microsoft's Copilot FAQ refers to Microsoft's approach to security, compliance and privacy, says individual Copilot conversations cannot yet be deleted, and advises against relying on Copilot for decisions in sensitive areas such as finance without verification.

For many teams the practical rule is to run formula reviews on a copy of the workbook, remove or mask personal data such as employee names in commission files where the review does not need it, and keep the original file under the usual version control.

··········

AUDITING EXCEL FORMULAS WITH AI: WHAT THE TOOLS CONFIRM AND WHERE REVIEW STAYS HUMAN.

All three assistants are documented as able to explain formulas inside Excel, and Claude for Excel and ChatGPT for Excel are also documented as tracing and fixing calculation errors with answers linked to specific cells. The requirements differ: Copilot depends on the Microsoft 365 or Copilot license, Claude on a Pro, Max, Team or Enterprise plan and a supported Excel build, and ChatGPT on the add-in being enabled in the workspace. None of the vendors presents the output as final, and Anthropic and Microsoft both say outright that finance or audit-critical calculations need verification.

The worked example shows the review pattern that holds regardless of the tool. Ask for ranges and row coverage, state the business rule in the prompt, check every filled column against a total, and confirm each finding with Trace Precedents or Evaluate Formula before correcting it. For a broader view of the assistant itself, the walkthrough on how to use Copilot in Excel covers the features beyond formula work. In the illustrative workbook, the three errors moved East gross margin by more than seven percentage points, cut a commission by $2,000 and produced a share column totaling 319.2%, and each was visible only to a reviewer who compared the result with an independent figure.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org