📎 This article includes 1 downloadable practice file ↓
Reconciliation means proving your books and the bank agree, and explaining every difference: cheques not yet cleared, bank charges not yet entered, typing mistakes.
Match on amount and date
In books: =COUNTIFS(Bank[Withdrawal], C2, Bank[Date], A2) > 0
Allow ±3 days:
=COUNTIFS(Bank[Withdrawal], C2, Bank[Date], ">="&A2-3, Bank[Date], "<="&A2+3) > 0Do it both ways: books → bank (entries the bank hasn’t processed) and bank → books (charges, interest, auto-debits you haven’t entered).
Typical differences
| Found | Usually means |
|---|---|
| In books, not in bank | Cheque issued but not presented, or a typing error |
| In bank, not in books | Bank charges, interest, NACH debits, direct deposits |
| Same item, amount differs | Typo: often ±100, ±1000 or swapped digits (difference divisible by 9) |
| Same item, date differs | Posting delay; fine within a few days |
The reconciliation statement
Balance as per books + cheques issued not presented − deposits not credited ± bank-side items = balance as per bank. Build it from the FILTERed unmatched lists.
Practice
Download the workbook below. The Books sheet has one wrong amount, one entry posted two days late and one entry missing; find them. The statement is made up but follows real Indian bank formats (NEFT, NACH, UPI narrations). The Check column turns green when your formula gives the right answer.
📎 Practice files for this article
- 📗Lesson 6 practice workbookStatement + books with 3 planted differences, 5 tasks.⬇ XLSX · 18 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.