Vendor statement reconciliation in Excel with Copilot and Claude

Vendor statement reconciliation compares the open items a supplier says you owe with the open items in your accounts payable subledger, then explains every difference before the month is closed. In Excel, Copilot and Claude for Excel can match the two lists, sort the differences into timing items and real errors, and draft the proposed corrections, but the final balance has to be recomputed with ordinary formulas and the adjusting entries approved by a person.
The work is repetitive and well suited to a spreadsheet assistant. A typical statement lists invoices, credit notes and payments by reference number, and a typical AP export lists the same documents with slightly different references, dates and signs. Most of the effort goes into matching those rows and classifying what is left over, which is the part an AI assistant handles quickly when the data is clean and clearly labelled.
Both tools carry explicit warnings from their vendors. Microsoft's Copilot in Excel FAQ says Copilot "can sometimes make mistakes" and asks users to review and verify anything it creates, and Anthropic's documentation for Claude for Excel says it is not recommended for audit-critical calculations without verification. Vendor statements also arrive from outside the company, which raises a specific risk that Anthropic's documentation addresses directly and that the control section below covers.
··········
WHY AP TEAMS RECONCILE VENDOR STATEMENTS AT MONTH END.
A supplier statement is independent evidence prepared by the counterparty. When the statement balance and the subledger balance agree after timing items, the AP team has reasonable comfort that invoices have not been lost, payments have been applied to the right documents and no credit notes are sitting unrecorded. When they do not agree, the difference usually points to one of a small number of causes: an invoice the company never received or has not yet approved, a payment the vendor has not yet applied, a credit note issued by the vendor but not booked, a keying error on amount or reference, or a duplicate entry.
The reconciliation also supports cut-off. An invoice dated before period end for goods already received belongs in the period, and a statement is often the first place a missing invoice becomes visible. The site's explainer on accounts payable recognition, discounts and cut-offs covers the accounting side of that question in more detail.
Teams rarely reconcile every supplier every month. A common approach is to reconcile the largest suppliers monthly, suppliers with disputes or credit holds whenever a statement arrives, and the rest on a rotating basis. The accounts payable aging report is a practical way to pick which vendors go first, since old open items on the aging are the ones a statement is most likely to contradict.
··········
COPILOT IN EXCEL AND CLAUDE FOR EXCEL: DOCUMENTED REQUIREMENTS.
The requirements below come from each vendor's official documentation, checked on November 16, 2026. Plans and builds change often, so confirm them against the current pages before rolling either tool out to an AP team.
........
Item | Copilot in Excel | Claude for Excel |
Eligible plans | Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Copilot subscription, or a Copilot Chat-eligible business or enterprise subscription | Claude Pro, Max, Team and Enterprise |
How it is added | Built into Excel, subject to license and tenant settings | Add-in from Microsoft AppSource, or deployed by an admin |
Excel versions | Windows, Mac and the web support edit, plan and chat modes | Excel on the web, Windows (Microsoft 365, build 16.0.13127.20296 or later), Mac (16.46 or later) |
Not supported | Editing requires Calculation Options set to Automatic; Strict Open XML files cause errors | Data tables, macros and VBA; Excel 2016 and 2019 perpetual; iPad and Android |
Vendor caveat | Review and verify anything Copilot creates | Not recommended for audit-critical calculations without verification |
........
Copilot's FAQ recommends saving workbooks in a modern format such as .xlsx and notes that Copilot on Windows or Mac will not work on files from a SharePoint site that requires check-out for editing, although Excel for the web still works in that case. Anthropic's page lists capabilities that suit this task directly: answers with cell-level citations, work across multi-tab workbooks, and native operations such as sorting, filtering and conditional formatting. Anthropic also states that Claude warns before overwriting existing data.
For a statement reconciliation, either tool needs only a workbook with two clearly structured tabs. Neither vendor's documentation describes reading a supplier statement PDF directly inside Excel for this purpose, so the safest workflow is to load the statement into a worksheet first, through the vendor's CSV export, the vendor portal, or a careful manual entry, and then ask the assistant to work on cells it can cite.
··········
A WORKED VENDOR STATEMENT RECONCILIATION EXAMPLE.
The data below is illustrative. A fictional supplier, Harbor Packaging Co., sends a statement of open items as of October 31, 2026. The AP subledger for the same vendor shows a different set of open documents.
........
Reference | Date | Vendor statement (USD) |
INV-1041 | Sep 18 | 4,820.00 |
INV-1047 | Oct 3 | 7,315.50 |
INV-1052 | Oct 14 | 2,960.00 |
INV-1055 | Oct 22 | 5,840.25 |
INV-1058 | Oct 28 | 3,150.00 |
CN-207 | Oct 25 | -640.00 |
Statement balance | Oct 31 | 23,445.75 |
........
........
Reference | Date | AP subledger (USD) |
INV-1041 | Sep 18 | 4,820.00 |
INV-1047 | Oct 3 | 7,315.50 |
INV-1052 | Oct 14 | 2,960.00 |
INV-1055 | Oct 22 | 5,480.25 |
PMT-8812 | Oct 30 | -7,315.50 |
Ledger balance | Oct 31 | 13,260.25 |
........
Put the statement on a tab named Statement and the ledger extract on a tab named Ledger, each formatted as an Excel table with columns for reference, date and amount, using the same sign convention (invoices positive, credits and payments negative). A clear structure helps both assistants and makes their citations easy to check. A first prompt can ask for the match and the classification in one pass.
Compare the Statement table and the Ledger table by reference.
Create a new sheet called Recon with one row per reference that appears
in either table, showing the statement amount, the ledger amount and
the difference (statement minus ledger). Classify each difference as
"payment in transit", "invoice not in ledger", "credit note not in ledger",
"amount difference" or "matched". Do not change the source tables.A second prompt asks for the summary that the reviewer will sign.
Below the Recon rows, build a reconciliation from the ledger balance to the
statement balance using only formulas that reference the Recon sheet.
List each reconciling item on its own line and show a final check cell
that must equal zero. Explain any item you are unsure about instead of
guessing.With Claude for Excel, asking for cell citations on every reconciling item makes the review faster, since each answer can link back to the source row. Microsoft lists plan, edit and chat modes for Copilot in Excel, and asking for a plan first, then reviewing it before allowing edits, keeps the source tabs untouched until the approach looks right.
··········
THE CORRECT RESULT TO CHECK THE AI OUTPUT AGAINST.
The expected answer for the illustrative data, computed independently in Python, is shown below. Any assistant output that does not reproduce these figures needs to be corrected before it goes anywhere near the close file.
........
Reference | Statement | Ledger | Difference | Classification |
INV-1041 | 4,820.00 | 4,820.00 | 0.00 | Matched |
INV-1047 | 7,315.50 | 7,315.50 | 0.00 | Matched |
INV-1052 | 2,960.00 | 2,960.00 | 0.00 | Matched |
INV-1055 | 5,840.25 | 5,480.25 | 360.00 | Amount difference |
INV-1058 | 3,150.00 | 0.00 | 3,150.00 | Invoice not in ledger |
CN-207 | -640.00 | 0.00 | -640.00 | Credit note not in ledger |
PMT-8812 | 0.00 | -7,315.50 | 7,315.50 | Payment in transit |
Total | 23,445.75 | 13,260.25 | 10,185.50 |
........
The total difference of 10,185.50 is fully explained by four items. The payment PMT-8812 of 7,315.50, sent on October 30, settles INV-1047 in the ledger but had not reached the vendor when the statement was produced, so it is a timing item that needs no entry. The other three items are real differences in the company's books once they are verified against source documents.
........
Reconciliation line | USD |
AP subledger balance at Oct 31 | 13,260.25 |
Add: INV-1058 not yet recorded | 3,150.00 |
Add: INV-1055 keyed 360.00 too low | 360.00 |
Less: CN-207 not yet recorded | -640.00 |
Adjusted subledger balance | 16,130.25 |
Vendor statement balance at Oct 31 | 23,445.75 |
Less: PMT-8812 payment in transit | -7,315.50 |
Adjusted statement balance | 16,130.25 |
Unreconciled difference | 0.00 |
........
If the invoice and credit note are confirmed, the proposed correcting entries look like this. The expense account is a placeholder: INV-1058 goes to inventory or to the expense line that matches the purchase order, and its date depends on when the goods were received.
........
Entry | Account | Debit | Credit |
Record INV-1058 | Packaging supplies expense | 3,150.00 | |
Record INV-1058 | Accounts payable, Harbor Packaging | 3,150.00 | |
Correct INV-1055 | Packaging supplies expense | 360.00 | |
Correct INV-1055 | Accounts payable, Harbor Packaging | 360.00 | |
Record CN-207 | Accounts payable, Harbor Packaging | 640.00 | |
Record CN-207 | Packaging supplies expense | 640.00 | |
Total | 4,150.00 | 4,150.00 |
........
A simple formula check in the Recon sheet catches most assistant errors. The formula below uses standard Excel functions and assumes the tables are named Statement and Ledger with columns named Ref and Amount; a missing reference returns zero so the difference column still calculates.
Statement amount: =XLOOKUP([@Ref], Statement[Ref], Statement[Amount], 0)
Ledger amount: =XLOOKUP([@Ref], Ledger[Ref], Ledger[Amount], 0)
Difference: =[@[Statement amount]] - [@[Ledger amount]]
Final check: =SUM(Recon[Difference]) - (SUM(Statement[Amount]) - SUM(Ledger[Amount]))··········
COMMON AI ERRORS IN VENDOR STATEMENT MATCHING AND HOW TO CATCH THEM.
The most frequent problem is sign handling. Statements often show credits and payments as positive numbers in a separate column, and an assistant asked to "compare amounts" can treat a credit note of 640.00 as an invoice. Normalizing signs before the first prompt, and stating the convention in the prompt, removes most of these errors.
Reference formats cause the second group of problems. A vendor may print "1058" while the ledger stores "INV-1058", or add a branch prefix. Assistants are good at fuzzy matching, which is exactly why every match on a reference that is not identical should be listed for a person to confirm. A fuzzy match between two different invoices with similar numbers produces a reconciliation that balances on paper and hides a real error.
Classification is the third weak point. An assistant can label PMT-8812 as a payment in transit only if the payment date and the statement date are in the workbook. Without the payment date, it may call the same row an unexplained difference or, worse, propose an entry for it. Payments in transit should be confirmed against the bank statement or the payment run before they are accepted as timing items; the site's guide to the month-end close checklist and bank reconciliation with Copilot, Claude and ChatGPT covers that side of the close.
Finally, an assistant's arithmetic summary is never the evidence. The final check cell must be a formula that recomputes from the source tables, and the reviewer should confirm that the totals of the Statement and Ledger tables equal the balances printed on the vendor statement and in the AP subledger report.
··········
CONTROLS AND PROMPT INJECTION RISK WITH SUPPLIER FILES.
Supplier statements and portal exports are external files, and Anthropic's documentation for Claude for Excel warns that files from external sources "can contain hidden instructions" that may manipulate the add-in into extracting data, modifying records or performing destructive actions. The documentation names vendor files among the examples and says to use the add-in only with trusted spreadsheets. For AP work, a practical response is to paste the statement values into a clean company template rather than opening the supplier's own workbook with the add-in active, and to read each confirmation prompt carefully before allowing an edit. Anthropic states that Claude asks for confirmation before risky operations.
Separation of duties still applies. The assistant can prepare the Recon sheet and draft entries, but the person who approves the correcting entries should not be the same person who maintains the vendor master or releases payments. Changes to vendor bank details that appear on a statement or in an accompanying email are a known fraud vector and belong in the company's vendor-change procedure, which an AI-assisted reconciliation should never bypass. For teams that already run invoice capture in an AP automation platform, the site's comparison of Ramp, BILL and Brex for AP automation describes where invoice data usually originates before it reaches the subledger.
A short checklist covers the essentials:
........
Step | Owner |
Normalize signs and references before prompting | Preparer |
Tie table totals to the statement and subledger report | Preparer |
Confirm payments in transit against the bank or payment run | Preparer |
Obtain copies of invoices and credit notes not in the ledger | Preparer |
Review fuzzy matches and the final zero check | Reviewer |
Approve and post correcting entries | Approver |
Contact the vendor about disputed items | AP lead |
........
··········
DATA HANDLING IN COPILOT AND CLAUDE FOR AP WORKBOOKS.
Microsoft states that prompts, responses and data accessed through Microsoft Graph are not used to train foundation models, and that Copilot surfaces only organizational data the user already has permission to view. For EU customers Microsoft describes Copilot as an EU Data Boundary service, with the stated exception that models provided by Anthropic as a subprocessor are currently excluded from the EU Data Boundary, which European subsidiaries should weigh when choosing a model in Copilot.
Anthropic's documentation for Claude for Excel says that when a user signs in with a Claude account, conversations are sent to Anthropic, including the workbook content Claude reads and any attached files, and that inputs and outputs are deleted on the backend within 30 days except in the cases its privacy pages describe. Chat history is stored locally in the browser. The add-in does not inherit custom data retention settings an organization may have set, and its activity is not included in Enterprise audit logs, although Enterprise organizations with the Compliance API enabled do get Claude for Excel sessions through that API. Organizations that route AI traffic through Amazon Bedrock, Google Cloud Vertex AI, Azure AI Foundry or an LLM gateway can deploy the add-in without individual Claude accounts.
Vendor statements contain supplier names, amounts and sometimes bank details. Removing bank details and any personal data from the reconciliation workbook before using either assistant reduces exposure without affecting the match.
··········
VENDOR STATEMENT RECONCILIATION WITH AI: WHAT HOLDS AND WHAT NEEDS JUDGEMENT.
Copilot in Excel and Claude for Excel can both perform the mechanical part of a vendor statement reconciliation: matching references across two tables, computing differences and sorting them into timing items and errors. In the illustrative example, a 10,185.50 gap between the statement and the subledger reduces to one payment in transit and three book corrections, and the adjusted balances agree at 16,130.25.
The judgement calls stay with the AP team. Whether INV-1058 belongs in October depends on the receipt date of the goods, whether CN-207 is valid depends on the dispute it settles, and whether a fuzzy match is correct depends on the underlying documents. The vendors' own documentation draws the same line: both tools are presented as assistants whose output must be reviewed, and Anthropic adds a specific warning about external files that applies to every supplier statement.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org




