Bank reconciliation statement format in Excel — with a worked example

A bank reconciliation statement (BRS) explains why the bank balance in your books doesn't match the balance on the bank statement on the same date. It's one of the first things taught in accounting and one of the last things anyone wants to do at month-end — mostly because the hard part isn't the format, it's finding the differences.

Below: the standard format, a complete example that ties to the rupee, and an Excel setup that finds the unmatched entries for you.

What a BRS is (and isn't)

A BRS is a statement, not an entry. You don't post it; you prepare it on a date (usually month-end) to prove the two balances agree once known differences are accounted for. Anything left unexplained is a problem to investigate — a missing entry, an error, or occasionally something worse.

Why the two balances differ

There are only three families of reasons:

• Timing — you've recorded it, the bank hasn't yet. Cheques issued but not presented; cheques deposited but not yet cleared.
• Bank-first items — the bank has recorded it, you haven't yet. Bank charges, interest, direct NEFT/UPI receipts, NACH/ECS debits, dishonoured cheques.
• Errors — on either side. A wrong amount, a transposition, an entry in the wrong bank ledger.

The BRS format

Start from one balance and adjust your way to the other. Starting from the cash book is the most common approach:

Starting from the bank statement instead? Every sign reverses — add what you'd have subtracted and vice versa. And if the cash book shows an overdraft (a credit balance), treat it as a negative number and the same logic holds.

Worked example

Sharma Enterprises' cash book shows a bank balance of ₹1,42,650 on 31 March 2026. The bank statement for the same date shows ₹1,71,600. Comparing the two line by line turns up six items:

• Cheque 000412 to R K Traders for ₹25,000 and cheque 000415 to Mehta Stationers for ₹13,500, issued on 28 and 30 March, not yet presented — ₹38,500
• A cheque for ₹22,000 from a customer, deposited on 31 March, not yet credited
• Bank charges of ₹590 (₹500 + ₹90 GST) debited by the bank, not recorded in the cash book
• Interest of ₹1,240 credited by the bank, not recorded
• ₹15,000 received from a customer by NEFT, not recorded
• An insurance premium of ₹3,200 paid by NACH, not recorded

₹1,42,650 + ₹54,740 − ₹25,790 = ₹1,71,600, exactly the statement balance. Reconciled.

The adjusted cash book method

The layout above mixes two different kinds of item. The bank-first items — interest, charges, the NEFT receipt, the NACH debit — aren't really reconciling differences; they're entries missing from your books. Most auditors prefer you record those first, then reconcile only the true timing differences:

Same answer, cleaner result: your books are now correct, and the BRS contains only items that will clear by themselves next month. That's also exactly what Tally Prime's reconciliation screen does — see bank reconciliation in Tally Prime.

Building it in Excel

Use three sheets:

• Books — your cash book's bank column: Date, Particulars, Cheque/Ref, Deposit, Withdrawal.
• Bank — the bank statement: Date, Narration, Ref, Debit, Credit, Balance.
• BRS — the format above, with totals pulled from the other two sheets by formula.

The Books sheet is easy — export the ledger from your accounting software. The Bank sheet is where time goes if you're copying from a PDF: dates come across as text, amounts pick up stray commas and spaces, and long narrations split across two rows. Getting a clean version first saves more time than any formula below. BankXL produces the Bank sheet directly from the PDF, with debits and credits already in separate numeric columns.

Formulas that find unmatched entries

Mark each book entry as found or not found in the bank

In the Books sheet, column F, starting at row 2 (remember: book deposits are bank credits, and book withdrawals are bank debits):

=IF(D2>0,
   IF(COUNTIFS(Bank!$E:$E, D2) > 0, "In bank", "Not in bank"),
   IF(COUNTIFS(Bank!$D:$D, E2) > 0, "In bank", "Not in bank"))

Mark each bank line as found or not found in the books

In the Bank sheet, column G:

=IF(D2>0,
   IF(COUNTIFS(Books!$E:$E, D2) > 0, "In books", "Not in books"),
   IF(COUNTIFS(Books!$D:$D, E2) > 0, "In books", "Not in books"))

Pull the totals into the BRS

Cheques issued, not presented   =SUMIFS(Books!E:E, Books!F:F, "Not in bank")
Deposits not yet credited       =SUMIFS(Books!D:D, Books!F:F, "Not in bank")
Credited by bank, not in books  =SUMIFS(Bank!E:E,  Bank!G:G,  "Not in books")
Debited by bank, not in books   =SUMIFS(Bank!D:D,  Bank!G:G,  "Not in books")

Add a check cell

Put the statement's closing balance in one cell and your computed balance in another, then:

=IF(ABS(C20 - C21) < 0.01, "Reconciled", "Difference: " & TEXT(C20 - C21, "#,##0.00"))

Mistakes that stop it tying

• Not carrying forward last month's outstanding items. A cheque issued in February and presented in March is a March bank debit with no March book entry. Start every BRS from the previous one.
• Stale cheques left outstanding forever. Cheques in India are valid for three months. An unpresented cheque older than that should be written back, not carried indefinitely.
• An incomplete bank statement. One missing page or one row lost in copying creates a difference nothing in your books explains. Check that each row's balance equals the previous balance plus credit minus debit — our free checker does this for a whole PDF.
• Signs flipped on overdrafts. An overdraft is a negative balance. Treat it as one and the format works unchanged.
• Fixing errors inside the BRS. If the cash book has a wrong amount, correct the cash book. A BRS that permanently carries "error adjustment" lines is hiding something.

Reconciling many client accounts every month? See how CA firms automate bank reconciliation.