Bank Reconciliation in Excel With AI: Find Differences Fast

Tally has its own bank reconciliation feature, and you should keep using it. But when the closing balances do not agree and there are hundreds of entries, finding the differences is slow. Excel, with formulas written by AI, can match the two lists in minutes and show you only the entries that need attention.

Step 1: Get both lists into Excel

Bank statement: download it from net banking as Excel or CSV.
Books: export the bank ledger from Tally to Excel using the export option of the ledger report.

Put them on two sheets, Bank and Books, each with simple columns:

Date	Description / Narration	Ref (cheque no. or UTR)	Debit	Credit

Clean them first (lesson 4 of AI for Excel): real dates, numbers without commas or text, no blank or total rows.

Replace names and account numbers with dummy values before pasting any rows into an AI chat.

Step 2: Decide how entries should match

Ask the AI to help you choose matching rules:

I am reconciling a bank statement with the bank ledger from Tally in Excel.
Sheet Bank and sheet Books both have: A Date, B Narration, C Ref, D Debit,
E Credit. Bank debits correspond to book credits and vice versa.
Suggest matching rules in order of reliability, for example by reference
number first, then by exact amount within 3 days. Then give me the Excel
365 formulas to mark each Bank row as Matched or Unmatched.

Typical rules:

Reference match: same cheque number or UTR, and same amount.
Amount and date match: same amount, and dates within a few days (cheques and transfers clear later than they are booked).
Everything else is unmatched and needs a human.

A typical formula for rule 2 in the Bank sheet:

=IF(COUNTIFS(Books!$E:$E, D2, Books!$A:$A, ">="&A2-3, Books!$A:$A, "<="&A2+3)>0, "Matched", "Unmatched")

This checks whether the books have a credit of the same amount within three days either side of the bank debit. Ask the AI to explain any formula you do not understand before using it.

Step 3: Watch out for false matches

Two entries of ₹10,000 in the same week can match the wrong way round. Ask:

How do I flag amounts that appear more than once in the same week, so I
can check those matches by hand?

Matching the same book entry to two bank entries is the most common error in spreadsheet reconciliations. Count matches in both directions: every Bank row and every Books row should be used once.

Step 4: Explain the unmatched items

Filter Unmatched on both sheets. Typical reasons:

Unmatched in Bank only	Unmatched in Books only
Bank charges, interest, GST on charges not booked yet	Cheques issued but not yet presented
Direct deposits from customers not recorded	Cheques deposited but not yet cleared
Auto-debits (EMI, standing instructions)	Entries booked twice or with wrong amount

You can paste the unmatched list (with dummy names) and ask the AI to group it by likely reason. Then you check each one against the statement and vouchers.

Step 5: Record and prove

Pass missing entries in Tally (bank charges, interest, direct receipts) after checking them.
Use Tally's bank reconciliation to enter clearing dates.
Prepare the reconciliation statement: balance as per books, plus and minus each item, equals balance as per bank. Ask the AI for a clean Excel layout, but the numbers must come from your workings.

Practice task

Take one month's bank statement and bank ledger (or create a dummy set of 30 entries with a few deliberate differences). Use AI-written formulas to mark matches by reference and by amount-and-date, flag repeated amounts, and list the unmatched items with reasons.

Key takeaways

Keep Tally's reconciliation; use Excel with AI to find differences quickly.
Match by reference first, then by amount within a date window.
Flag repeated amounts and check that each entry is used only once.
AI can group unmatched items by likely reason; you verify every one.