Bank Reconciliation with AI: From 4 Hours to 10 Minutes in Excel (Step by Step)

The Job Every Accountant Hates

Monthly bank reconciliation is the unloved chore of finance work. Pull the bank statement. Pull the GL cash account. Open both side by side. Highlight matches. Investigate mismatches — bank fees the system didn't book, deposits in transit, that wire from the customer that came in under a different name. Group by category. Produce a clean summary. Defend it in close meetings.

Done well, this is three to four hours per entity, every month, forever. Done badly, it's the thing that surfaces in February when the auditor finds two months of unbooked bank charges from last year.

We just ran a real reconciliation through ExcelMaster: a 4,217-row bank statement against a 3,985-row GL extract. Total wall-clock time: ~10 minutes. 23 mismatches caught, every one with a clickable trail back to source rows. This article walks through what the agent actually did, the mismatches it surfaced, and the two judgment calls you should still make yourself.

Why an AI Assistant Can't Do This Job

Bank reconciliation isn't a single formula. It's a multi-file, multi-step workflow that requires:

Reading two workbooks at once (statement + GL).
Understanding that "5/12/2026 wire from Acme Corp $14,250" on the bank side is the same transaction as "AR collection — Acme Corp Inc — $14,250.00" on the GL side, even though the date format, payee text, and decimal precision all differ.
Writing real matching code (fuzzy join on date+amount+payee), not a SUMIF.
Flagging unmatched items by category so a human knows which to investigate first.
Producing a deliverable — a summary tab — that survives leaving your desk.

Microsoft Copilot will help you with #2 and #3 in isolation: "Hey Copilot, write me a fuzzy match between these columns." You take the snippet, paste it, debug it, repeat 8 times. That's still hours of work, just with a slightly faster typist. We covered the assistant-vs-agent split in our Copilot Alternative comparison — bank rec is the workflow where the difference shows up brutally.

The Walkthrough: 10 Minutes, Two Files, 23 Mismatches

Input

Two workbooks, both open in Excel:

BankStatement_May2026.xlsx — 4,217 rows. Columns: Date, Description, Debit, Credit, Balance, Reference.
GL_Cash_May2026.xlsx — 3,985 rows from the GL cash account. Columns: Posting Date, Document #, Description, Amount, Counterparty.

Prompt

"Reconcile the bank statement (BankStatement_May2026.xlsx) against the GL (GL_Cash_May2026.xlsx). Match on date (±2 days), amount (exact), and counterparty (fuzzy). Flag everything unmatched. Group mismatches by category — bank fees, deposits in transit, customer remittance variances, payroll timing, anything else. Produce a Summary tab with totals and a Mismatches tab with the row-level detail. Highlight high-priority items over $1,000."

What the Agent Did, Step by Step

Step 1 — Open both workbooks (≈5 seconds). Multi-workbook reading was added in v0.6.24. The agent reads both files into memory through one supervised Python call, no manual copy-paste.

Step 2 — Inspect column structure (≈20 seconds). Column headers in both files get mapped: Date → Posting Date, Debit/Credit → Amount (with sign convention), Description → Description + Counterparty. The agent notes the bank file uses separate Debit/Credit columns while the GL uses a single signed Amount column, and writes a normalization step accordingly.

Step 3 — Backup before any write (automatic). Per-step backup runs against any workbook the agent will modify.

Step 4 — Write the matching engine (≈3 minutes). The agent writes Python that:

Normalizes dates to ISO and amounts to two decimals.
Builds a primary match key: (date_iso, abs_amount_cents).
Falls back to fuzzy counterparty match using Jaro-Winkler distance ≥ 0.8 when primary fails.
Allows ±2 day date drift on the fallback (covers ACH timing).

It runs the match across all 4,217 × 3,985 rows in a few seconds (the supervised Python doesn't have the row caps that Office.js does), and emits a tagged result for every row: matched, unmatched_bank_only, unmatched_gl_only.

Step 5 — Categorize the mismatches (≈2 minutes). A second Python pass walks the unmatched rows and assigns categories using a rule set the agent generates from the data itself:

Description contains "FEE" / "SERVICE CHARGE" / "WIRE FEE" → Bank Fee
Bank-only credit with description "DEPOSIT" → Deposit in Transit
GL-only entry with counterparty matching open AR customer → Customer Remittance — Variance
Description contains "ADP" / "PAYROLL" → Payroll Timing
Everything else → Manual Investigation

Step 6 — Produce deliverables (≈3 minutes). Two new tabs:

Summary — totals by category, count of items, $ exposure, with the largest mismatch in each category called out by name. Conditional format on items > $1,000 (red) and > $5,000 (red bold).
Mismatches — row-level detail. For every unmatched row: source file, source row #, date, description, amount, suggested category, and a hyperlink that jumps directly to that cell in the source workbook.

Step 7 — Validation (≈1 minute). Three checks:

Sum of (matched_total + unmatched_bank_total) = bank statement total.
Sum of (matched_total + unmatched_gl_total) = GL extract total.
Difference between the two totals = the reconciling item — should equal the closing book vs. bank balance gap. The agent prints this number prominently.

Total: ~10 minutes, every step in the timeline panel with a one-click revert button.

The 23 Mismatches: What You'd Have Missed in Excel Alone

The reconciliation surfaced 23 unmatched items. The breakdown was instructive:

Category	Count	$ Exposure	Action
Bank Fee (unbooked)	8	$1,247	Book to expense, every month
Deposit in Transit	5	$31,420	Normal — clears next period
Customer Remittance — Variance	4	$842	Investigate: short pays
Payroll Timing	3	$57,200	Normal — accrual reverses
Manual Investigation	3	$12,675	Bring to close meeting

The eight unbooked bank fees totaled $1,247. Caught manually? Maybe — if the analyst was patient enough to scroll. This month. Twelve months of $1,200 of unbooked fees compounding into Q4 is the kind of finding that lands on an audit memo.

The three "Manual Investigation" items totaling $12,675 are exactly the items a senior accountant should review. The agent's job is to surface them in 10 minutes; the senior accountant's job is to spend 20 of the saved 230 minutes deciding what to do with them.

The Audit Trail

Two questions every controller needs to answer when this reconciliation lands on their desk:

"Where did this $1,247 of bank fees come from?" Click the cell on the Summary tab → it's a SUMIFS pointing at the Mismatches tab → click the row on Mismatches → the hyperlink jumps to the exact bank statement row. Three clicks, zero ambiguity.
"Re-run after I reclassified these two items." The timeline panel lets you revert just step 5 (categorization), edit the rule set, and re-run from there. Steps 1–4 (the matching itself) don't have to recompute.

Where You Should Still Hand-Review

Two judgment calls the agent does not make for you. Don't skip them:

1. Anything in the "Manual Investigation" bucket. By construction, this is the category the rule set couldn't classify. These are real exceptions — ACH reversals, suspect duplicates, intercompany items routed wrong — and they need a human. The agent's value is surfacing them in the right place; the resolution is yours.

2. Whether to auto-book the bank fees. The agent flags them; whether to write a journal entry from this reconciliation is a process decision. Some teams want the AI to draft the JE; others insist on accountant approval. Pick your policy and apply it consistently — the agent will follow either path if you tell it to.

Beyond Bank Rec: The Same Pattern, Other Workflows

Bank reconciliation is the gateway. Once your team is comfortable with the agent's accuracy and audit trail on this workflow, the same prompt pattern works for:

AR aging vs. customer payments — fuzzy match on customer name + amount, surface short pays and disputed invoices.
AP vs. vendor statements — same logic, opposite direction. Catch the duplicate invoice that got booked twice.
GL-to-sub-ledger reconciliation — match individual ledger entries against the rolled-up GL balance. The 1,200-row inventory sub-ledger doesn't tie to GL inventory? The agent finds it.
Intercompany eliminations — match entity-A's payable to entity-B's receivable, surface the mismatched ones.

Each of these is the same shape: two messy datasets, fuzzy matching, categorize the unmatched, produce a defensible summary. Once you've done one, the rest are 5-minute prompt edits.

The Pricing Bit (Briefly)

The math: if monthly bank rec saves 3 hours per entity, and your senior staff costs $80/hour fully loaded, you save $240 per entity per month. ExcelMaster Pro is $14.90/month. Three entities pay for the tool 50× over. We've laid out the full pricing comparison against Microsoft Copilot in the Copilot Alternative article; bank rec is one of the workflows where the ROI math is least controversial. See pricing for plans.

Try It on Your Own Workbook

Download the free trial. Two-minute install, no card.
Open last month's bank statement and the GL cash extract.
Paste the prompt from "The Walkthrough" section — replace the file names with yours.
Watch the matching tab build in real time. If the matching threshold feels too loose or too tight, tell the agent to adjust and re-run; only the affected steps recompute.

Excel 2016+ for Windows. Standalone install. Multi-workbook reading is built in.