Skip to content
Bank and Credit Card Reconciliations

Reconcile two Excel tables with the Microsoft Financial Reconciliation agent

View source

What the source describes

Microsoft Learn explains that the Financial Reconciliation agent in the Finance Agent add-in for Excel reconciles two data sets formatted as tables on one or two worksheets. The agent suggests the tables and uses AI to propose reconciliation vectors (mapping and monetary keys) that you can keep, edit or remove before selecting Reconcile data. The resulting report groups transactions as matched, potentially matched or unmatched and includes a generative AI summary, with a Troubleshoot transactions feature for the unmatched items.

Result or outcome

According to the source, the agent reduces manual effort by suggesting reconciliation rules and producing a categorized report with an AI-written summary of discrepancies.

How to run it

  1. Format the bank or subledger data and the general ledger data as Excel tables on one or two worksheets in the same workbook.
  2. On the Home tab, open the Finance Agent add-in, select Financial Reconciliation, and confirm or fix the tables it suggests.
  3. Review the suggested mapping and monetary keys and choose Keep, Regenerate or remove them; the prompt on this page can help you pick keys and tolerances.
  4. Add reference columns if helpful, then select Reconcile data to get a report of matched, potentially matched and unmatched transactions with an AI summary.
  5. Use Troubleshoot transactions on unmatched and potentially matched rows, fix the source data, and select Re-reconcile data after changing any keys.

Try it yourself

Copyable prompt

I have two Excel tables to reconcile: [Table 1 name] from [bank or subledger] and [Table 2 name] from the general ledger for [period]. Columns in Table 1: [list]. Columns in Table 2: [list]. Recommend which column pair should be the mapping key, which should be the monetary key, and whether partial text matching or an amount tolerance of [amount] is appropriate. Then write Excel formulas (XLOOKUP or SUMIFS) to flag each row as matched, potential match, or unmatched, and a summary block showing counts and total differences. Explain any risks of false matches with these keys.

Replace the [bracketed] placeholders. Remove client names, account numbers, and personal data before pasting into any AI tool.

What to review

Do not accept the suggested keys without checking them, since a loose mapping key, partial match or tolerance can put the wrong transactions in the matched group. Trace a sample of matched rows back to the source tables using the aggregation and reconciliation IDs. Treat the AI summary and next-step suggestions as a starting point, and confirm the add-in is available in your Microsoft setup.

Related free download: Bank Reconciliation Cheat Sheet (Printable cheat sheet (PDF))

Source: Microsoft Learn (product documentation)

Not professional advice. This is general information, not accounting, tax, legal or financial advice. Check AI output yourself and consult a licensed professional for your situation. See our Terms of Use.

This entry summarizes a public source. Features, plans, and availability change, so confirm details with the vendor. Tool names are trademarks of their respective owners.

Related workflows

  • Reconciliations

    Build a Formatted Reconciliation Workbook from CSVs with Claude

    Anthropic's help article says Claude can execute code to create and work with files directly in a conversation, producing Excel spreadsheets, PowerPoint presentations, Word documents and PDFs that you can download or save to Google Drive. It says code execution and file creation are available on Free, Pro, Max, Team and Enterprise plans, on the web, Claude Desktop and Claude Mobile. Its examples include building financial models in Excel with working formulas and performing advanced analyses on uploaded data, which is what a reconciliation workbook built from bank and ledger exports needs.

    Claude, Microsoft Excel

    Claude Help Center
  • Reporting and Analysis

    Find Trends and Outliers in Ledger Exports with Copilot in Excel

    Microsoft Support explains that Copilot in Excel lets users ask questions in their own words to surface summaries, trends and outliers, returning results as charts, PivotTables or summaries. It can also add formula columns and rows (for example profit margin or percent of total), suggest single-cell formulas, build lookups such as XLOOKUP, and explain existing formulas. Microsoft advises being specific about which columns to analyze and to review and verify anything Copilot creates.

    Microsoft Copilot, Microsoft Excel

    Microsoft Support
  • Accounts Receivable

    Calculate Receivable Days Overdue with Copilot in Excel

    Microsoft Support explains that Copilot in Excel can add columns that calculate new values from your existing data, such as durations, and explain how each formula works. A short YouTube tutorial applies this to receivables: Copilot suggests a days-overdue formula, the formula is then edited to count from today when the paid-in-full date is blank and to handle partial payments, and the new column is sorted from largest to smallest to focus on the longest overdue accounts.

    Microsoft 365 Copilot, Microsoft Excel

    Microsoft Support (Get data insights with Copilot in Excel)
  • Reconciliations

    Auto-reconcile bank statement lines in Xero with JAX

    Xero says its automatic bank reconciliation is powered by JAX, which tries to reconcile new statement lines using four methods: Rule (your bank rules), Match (an existing Xero document), Memory (how you reconciled similar items) and Prediction (how other Xero users reconciled similar items). JAX only auto-reconciles when highly confident; otherwise it suggests a match and leaves the final decision to you, and you can challenge or reject its matches.

    Xero, JAX

    Xero US (product page)