Reconciliation is one of the most time-consuming tasks in controlling: open items list against bank statement, trial balance against account ledger, old against new price list, intercompany balances between two entities. Usually the job is to find the few dozen lines that don't match among thousands. AI assistants such as Microsoft Copilot, ChatGPT, Claude or Gemini are a big help here – if you use them correctly.
Typical reconciliations in controlling
- Open items against incoming payments: which invoices are paid but not cleared?
- Intercompany reconciliation: receivables of one entity against payables of the other.
- Master data: customer or item lists from two systems that need to be merged.
- Price lists and conditions: what changed between two versions?
- Plan against actual at account level: which accounts are missing from the plan?
Three ways to use AI for reconciliation
| Approach | How it works | Good for | Watch out |
|---|---|---|---|
| 1. AI builds the solution | You describe the columns, the AI writes formulas (XLOOKUP, COUNTIFS) or Power Query steps. The data stays with you. | confidential data, recurring reconciliations | check formulas with samples |
| 2. AI reconciles directly | You upload both files to an approved AI service, which lists the differences. | one-off reconciliations, messy structures | data protection, check control totals |
| 3. AI inside Excel | An assistant integrated into Excel (e.g. Copilot in Microsoft 365) works directly in the sheet. | quick analyses in the familiar tool | only with a suitable licence |
Approach 1 in detail: AI writes the formulas
For most controlling tasks this is the best approach: the AI only sees the structure, not the data. You get a reusable solution you understand and can check yourself.
=IF(COUNTIFS(Bank[Reference],"*"&A2&"*")=0,"open",IF(SUMIFS(Bank[Amount],Bank[Reference],"*"&A2&"*")=C2,"paid","amount differs"))| A | B | C | D | |
|---|---|---|---|---|
| 1 | InvoiceNo | Customer | Amount | Status |
| 2 | R-2026-0411 | Miller Ltd | 4,820.00 | paid |
| 3 | R-2026-0412 | Hughes & Co | 1,250.00 | amount differs |
| 4 | R-2026-0415 | Stone plc | 9,300.00 | open |
For reconciliations that come back every month, ask for the Power Query steps instead of a formula (“Merge queries” with an anti join shows what is in list A but not in list B). Once set up, the reconciliation runs with a click on “Refresh”.
Approach 2 in detail: AI reconciles directly
When structures are messy – different spellings, typos in customer names, invoice numbers sometimes with and sometimes without prefix – AI plays to its strength: it recognises “Miller Ltd” and “Miller Limited” as probably the same.
How to prepare the data
- Export only the columns needed for the reconciliation.
- Remove or pseudonymise names, bank details and other personal data if they aren't needed.
- Consistent formats: dates as dates, amounts as numbers without currency symbols.
- With very large files, test on a sample first.
Common mistakes
- Accepting results unchecked – AI can miss rows or match them wrongly.
- Uploading confidential data to private AI accounts.
- Asking again every month instead of having a reusable solution built once.
Conclusion
AI takes the tedious search for the needle in the haystack out of reconciliation. The safest approach is to have the AI build the formula or Power Query solution while the data stays with you. For one-off, messy reconciliations direct matching helps – always with control totals and a critical review of uncertain cases.
- Get started with Copilot in Excel – Microsoft's guide to the AI assistant inside Excel
- Power Query documentation – for recurring reconciliations without uploading data
Frequently asked questions
May I upload company data to an AI tool?
That depends on your company's policies, data protection law and the contract with the AI provider. Many companies only allow approved business versions in which data is not used for training.
How do I check whether the AI reconciled correctly?
With control totals (row counts and amount totals per category), samples and a manual review of all uncertain matches.
Which is better: direct reconciliation by AI or AI-written formulas?
For recurring and confidential reconciliations it is better to have formulas or Power Query steps built. For one-off unstructured lists direct matching can be faster.