Bank Reconciliation Statement (Excel)
Your cash book says one balance, the bank statement another. The difference is almost always cheques not yet cleared, bank charges or receipts you have not recorded. List those items here and the workbook prepares a proper bank reconciliation statement and tells you whether it balances to the rupee.
Free · no sign-up · opens in Microsoft Excel, Google Sheets, LibreOffice and WPS Office. Files contain no macros.
Preview
Real view of the file with its sample data (yellow cells are where you type).
| Particulars | ₹ | ₹ |
| Balance as per cash book | ₹ 1,54,230.50 | |
| Add: cheques issued but not yet presented | ₹ 18,500.00 | |
| Add: interest or direct deposits credited by bank, not in cash book | ₹ 455.00 | |
| Add: error correction | ₹ 0.00 | |
| Total additions | ₹ 18,955.00 | |
| Less: cheques deposited but not yet credited | ₹ 8,600.00 | |
| Less: bank charges or direct debits, not in cash book | ₹ 600.00 | |
| Less: error correction | ₹ 0.00 | |
| Total deductions | ₹ 9,200.00 | |
| Adjusted balance (should equal the bank statement) | ₹ 1,63,985.50 | |
| Balance as per bank statement | ₹ 1,63,985.50 | |
| Difference (must be 0.00) | ₹ 0.00 |
What's inside
- Settings: bank name, account, month and the two balances (cash book and bank statement)
- Items sheet with a type list: cheques issued but not presented, cheques deposited but not credited, bank charges, interest, direct deposits, direct debits and errors
- Statement sheet in the usual format: balance as per cash book, add, less, balance as per bank, with the difference highlighted until it is zero
- A4 print layout with prepared-by and checked-by lines
How to use it
- On the Settings sheet type the bank name, account number, the month and the closing balance as per your cash book and as per the bank statement.
- Tick off matching entries between your cash book and the bank statement on paper or in your accounting software.
- For every entry that does not match, add a row on the Items sheet: pick its type, date, details and amount.
- Open the Statement sheet. When the difference shows 0.00 in green, the reconciliation is complete.
- If a difference remains, look for amounts entered twice, transposed digits (for example 540 typed as 450) or a missing entry.
- Print or save as PDF for your records.
Tips
- A difference divisible by 9 often means two digits were swapped.
- Pass journal entries in your books for bank charges, interest and direct receipts so they do not appear again next month.
- Cheques issued more than three months ago that are still not presented are stale; check with the payee.
Questions and answers
Do I start from the cash book or the bank balance?
This template starts from the cash book (bank column) balance and works towards the bank statement balance, which is the most common method taught and used in India.
What if my cash book shows an overdraft?
Type the balance as a negative number. The same additions and subtractions apply.
Why does the difference show in red?
Red means the adjusted balance does not yet equal the bank statement balance. Some item is missing or entered with the wrong type.