Skip to content
Controllingblog

Home❧AI in Controlling❧Reconciling Excel lists with AI

Reconciling Excel lists with AI

Two lists that should match – and then don't. AI finds the differences in minutes. How to do it safely and traceably.

AI in Controlling9 min readUpdated 14 Nov 2026
Illustration: robot assistant with spreadsheet, document and database Match

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 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.

I have two Excel tables: - "OI" with columns InvoiceNo, Customer, Amount, Due - "Bank" with columns Date, Reference, Amount The reference usually contains the invoice number, sometimes with extra text. Write me an Excel formula (Microsoft 365) for a new column in "OI" that checks whether there is a payment with a matching invoice number and whether the amount matches. Result: "paid", "amount differs" or "open". Explain it step by step.
fx=IF(COUNTIFS(Bank[Reference],"*"&A2&"*")=0,"open",IF(SUMIFS(Bank[Amount],Bank[Reference],"*"&A2&"*")=C2,"paid","amount differs"))
ABCD
1InvoiceNoCustomerAmountStatus
2R-2026-0411Miller Ltd4,820.00paid
3R-2026-0412Hughes & Co1,250.00amount differs
4R-2026-0415Stone plc9,300.00open
What the result can look like: every invoice gets a status. The formula came from the AI, the data never left the company.

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.

Reconcile the two attached lists (customer master system A and system B). 1. Find customers that appear in both lists – even with different spelling. Give a reason for uncertain matches. 2. List customers that are only in A or only in B. 3. At the end give me control figures: rows A, rows B, matches, only A, only B. The totals must add up. Create the result as an Excel file with one sheet per category.
The most important checkAlways ask for control totals: number of rows and sums of amounts per category. If matches + only A doesn't equal the row count of A, something is wrong. AI results are suggestions – you check uncertain matches yourself.

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.

Important when using AIBefore uploading, check whether you are allowed to use the data – company policies and data protection law (e.g. the GDPR in the EU) apply here too. Only process personal and confidential data in approved business AI services. Always check AI results against the source data.
Further links

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.