Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
Small businesses prepare a BRS in Excel without a fixed layout, so totals are typed by hand and differences are hidden rather than explained.
Reconcile from cash book balance to passbook balance using four blocks and prove it with a check cell that must equal zero.
One sheet per bank account per month with columns for date, particulars, cheque or UTR, amount, block (add or deduct) and status (open or matched), SUMIF totals and a check cell.
A one-page BRS that a reviewer can read in a minute, with every open item listed and the difference proven to be zero.
A workable BRS in Excel has four blocks: balance as per cash book, items to add, items to deduct and balance as per passbook. Below them sits one check cell that compares your calculated passbook balance with the real figure on the statement. It must read zero. For a single bank account, one sheet a month is enough.
This article is about the statement layout. For the rules on which items go in which block, see BRS add or deduct rules.
What does the layout look like?
Set up two areas on the sheet: a list of open items, and a summary.
Open items list (rows 10 onwards):
| Column | Heading | Example entry |
|---|---|---|
| A | Date | 27-03-2026 |
| B | Particulars | Cheque to Mehta Supplies |
| C | Cheque no. or UTR | 004781 |
| D | Amount (₹) | 42,000 |
| E | Block | Add |
| F | Status | Open |
Summary (rows 1 to 7):
| Row | Label | Formula idea |
|---|---|---|
| 1 | Balance as per cash book | typed from the cash book |
| 2 | Add: items making bank higher | SUMIFS of column D where E = “Add” and F = “Open” |
| 3 | Less: items making bank lower | SUMIFS of column D where E = “Deduct” and F = “Open” |
| 4 | Balance as per passbook (calculated) | row 1 + row 2 − row 3 |
| 5 | Balance as per passbook (actual) | typed from the bank statement |
| 6 | Check | row 4 − row 5, must be 0 |
Which formulas does a beginner actually need?
Only two. SUM for simple totals and SUMIF (or SUMIFS for two conditions) to total the open items in each block. For example, the add block can be written as =SUMIFS(D:D,E:E,“Add”,F:F,“Open”).
Using a Status column means you never delete rows. When an item clears next month, you change its status to Matched and it drops out of the totals automatically. Lookups and narration parsing are useful later; the narration-parsing guide covers them.
A worked example
On 31 March your cash book shows ₹4,72,500 and the passbook shows ₹4,91,060. Six items explain the gap.
| Item | Block | Amount (₹) |
|---|---|---|
| Cheque issued to supplier, not yet presented | Add | 42,000 |
| NEFT from a customer credited by bank, not in cash book | Add | 12,500 |
| Interest credited by bank, not in cash book | Add | 1,860 |
| Cheque deposited, not yet credited | Deduct | 35,000 |
| NACH insurance premium debited, not in cash book | Deduct | 1,500 |
| Bank charges debited, not in cash book | Deduct | 1,300 |
Summary:
| Label | ₹ |
|---|---|
| Balance as per cash book | 4,72,500 |
| Add | 56,360 |
| Less | 37,800 |
| Balance as per passbook (calculated) | 4,91,060 |
| Balance as per passbook (actual) | 4,91,060 |
| Check | 0 |
Check the arithmetic: 42,000 + 12,500 + 1,860 = 56,360. 35,000 + 1,500 + 1,300 = 37,800. 4,72,500 + 56,360 − 37,800 = 4,91,060.
Four of these items (the NEFT credit, interest and the two bank debits) also need entries in the cash book after the BRS. The two cheques simply wait to clear.
Where does a spreadsheet BRS stop being safe?
Excel works well for one or two accounts with a careful owner. The risks grow when:
- Formulas get overwritten. Someone types a number over the check cell and it shows zero for ever. Lock formula cells.
- Copies travel by email. “BRS_March_final_v3.xlsx” and nobody knows which is final.
- There is no audit trail. You cannot see who changed an item from Open to Matched.
- Nothing locks after sign-off. A reviewed month can be changed quietly. Protect the sheet and save a PDF copy at sign-off.
How long is a cheque valid in India?
Under the RBI circular of 4 November 2011, cheques, drafts, pay orders and banker’s cheques are valid for three months from the date on the instrument, down from six months. The rule applies to instruments dated on or after 1 April 2012. A cheque dated 20 March can be presented until 20 June. After that the bank will not pay it, so an unpresented cheque past that date is stale and should be reversed in the books, not carried forward.
One more line that often lands in the wrong block: a bank charge of ₹590 is usually ₹500 of charges plus ₹90 GST at 18%. Put the full ₹590 in the “debited by bank, not in cash book” block, then book it as two lines, charges and GST input, in the cash book.
What to read next
- Next step: Excel, Python or reconciliation software: when to switch
- Bank narration parsing with Excel formulas
- Bank reconciliation software for Indian businesses
When the open-items list runs to hundreds of rows, TransactIG matches bank statement lines to your books, keeps unmatched items in an exception queue and gives audit-ready exports.