Bank Reconciliation in Excel: Format, Steps and Common Errors
A bank reconciliation is not a matching exercise — it is a working paper that explains why two balances differ. Here is the format, the sequence, and the five difference types that account for nearly every gap you will find.
Short answer
Most people treat this as a search for the row that does not match. It is more useful to treat it as an explanation: you already know the two balances disagree by some amount, and your job is to account for that amount item by item until nothing is left unexplained.
The two files you need
| Side | Where it comes from | Watch out for |
|---|---|---|
| Bank statement | Net banking export as CSV or XLS for the period | Header rows above the table, a summary row at the bottom, and amounts split across separate debit and credit columns |
| Cash book / bank ledger | Tally, your ERP, or the bank ledger in your accounting software | Manual entries typed in the wrong sign, and contra entries that never hit the bank |
Sort both sides by date before you begin. A reconciliation is a period document, so a statement running from 1 September to 30 September has to be reconciled against a ledger extracted for exactly the same dates.
A BRS format that works for a reviewer
There is no statutory format for an internal bank reconciliation. What matters is that somebody else can follow it twelve months later. Five columns and two blocks do that.
| Date | Particulars | Debit (₹) | Credit (₹) | Status |
|---|---|---|---|---|
| 01-09-2025 | Opening balance as per bank statement | — | 4,18,206 | Cleared |
| 03-09-2025 | Chq 004512 — Shree Balaji Enterprises | 1,18,000 | — | Cleared |
| 05-09-2025 | NEFT receipt — Rupee Traders | — | 2,36,000 | Cleared |
| 09-09-2025 | Chq 004517 — Annual maintenance charges | 23,600 | — | Not presented |
| 18-09-2025 | Bank charges for the month | 590 | — | In statement only |
| 20-09-2025 | Interest credited on savings sweep | — | 1,284 | In statement only |
| 28-09-2025 | Chq 004521 issued, presented next month | 82,050 | — | Not presented |
The bottom of the sheet, in plain lines rather than formulas, states the three figures that matter: the balance per the bank statement, the balance per your books, and the total of the reconciling items. Those three lines are what a reviewer reads first.
Step by step
Extract both sides for identical dates
Clean both exports into one shape
Match on amount and date, not on narration
Set a date tolerance and an amount tolerance
Categorise everything that is left
Total both sides and explain the gap
The five difference types that explain almost every gap
| Difference | What it looks like | Who needs to act |
|---|---|---|
| Cheque issued but not presented | In your books as a payment, absent from the statement | Payee — or you, if it is beyond validity |
| Deposit in transit | In your books as a receipt, absent from the statement | Nobody — it clears next working day usually |
| Bank charges and interest | In the statement, absent from your books | You — raise a journal entry |
| Direct debits and standing instructions | In the statement, absent from your books | You — book it against the right expense head |
| Error | An amount or a sign that matches no transaction on the other side | You or the bank, depending on where it is |
The sign trap
The cut-off rule nobody writes down
A bank reconciliation is only as good as its cut-off. Money that moved on the 30th but appears in the statement on the 1st is a timing difference, not an error — and if you have not fixed a cut-off rule, you will re-investigate the same rows next month.
- Statement cut-off: the last date in the downloaded statement. Note it on the working paper.
- Book cut-off: the last date in the ledger extract. It must match.
- Instruments issued before cut-off but presented after: outstanding cheques. They stay in the reconciliation.
- Instruments issued after cut-off: not part of this reconciliation at all, even if they are in your books.
Ageing the outstanding cheques
An outstanding cheque list is only useful if it is aged. Add a column showing how many days each instrument has been outstanding since it was issued, and sort by that column rather than by amount.
| Age of the outstanding cheque | What it usually means | Action |
|---|---|---|
| Under 30 days | Normal — the payee has not banked it yet | Leave it in the reconciliation |
| 30 to 90 days | The payee may have misplaced it | Confirm with the payee |
| Over 90 days | Beyond the validity period in India | Revalidate or reverse in the books |
Reversing a stale outstanding cheque is the step most manual reconciliations skip, and it is the step that stops the same item appearing on the sheet month after month.
Common errors
- Reconciling different periods. A statement for September against a ledger to 31 August will always differ, and the difference is not real.
- Matching on narration text. Bank narrations are inconsistent by nature. Match on amount, date and instrument number.
- Deleting the reconciling items. The point of the working paper is to carry items forward until they clear. Deleting an unresolved item hides a control failure.
- Not separating debit and credit. Totalling a mixed column produces a net figure that reconciles by coincidence and misleads the moment it does not.
- Using conditional-formatting rules across two workbooks. A formatting rule cannot reference another workbook, so a bank statement in one file and your ledger in another cannot be highlighted against each other. It has to be a formula.
When Excel becomes the bottleneck
For a single account and a single month, Excel is entirely adequate. The difficulty arrives with volume: several accounts, twelve months, and statement exports running to thousands of rows. At that point COUNTIF and XLOOKUP repeated down the sheet become the slowest part of the task, and the reconciliation lives inside formulas that only their author can safely edit.
Before that point, keep the file small: reconcile one account and one month per sheet, match on a documented key with a stated tolerance, and keep the working paper as a saved output rather than a live formula sheet. If you would rather not maintain that at all, a purpose-built reconciliation tool is the alternative — the same rules, applied by the software rather than by you.
Related reading
If the matching itself is what is failing, the complete reconciliation guide covers match keys and normalisation in detail. For the vendor side of the ledger, see vendor reconciliation in Excel.
If you would rather not build this by hand
Run the bank reconciliation against the CSV itself
Piloteq Automate reads your bank statement export and your bank ledger directly, matches on the rules you set — exact, tolerance, date tolerance, fuzzy or last-digit — and writes a reason next to every record. Matched, unmatched, duplicates and amount differences come back as separate result sets.
- ✓Six match passes in a fixed priority order
- ✓Amount tolerance and a separate date tolerance
- ✓Duplicate detection for repeated instrument numbers
- ✓Debit and credit totals kept separate per side
Frequently asked questions
What is the format of a bank reconciliation statement in Excel?+
A workable BRS has five columns: Date, Particulars (instrument number and description), Debit, Credit and Status. Two blocks sit one below the other — the first block lists everything that has cleared in both the bank statement and the cash book, the second lists the reconciling items grouped into cheques issued but not presented, deposits in transit, bank charges and interest, direct debits and errors. The bottom line shows the balance as per the bank statement, the balance as per the cash book, and the items that explain the gap between them.
Why does my bank reconciliation not match even after matching every transaction?+
The usual causes are a transaction dated just after the cut-off, a cheque that was issued before month end but presented in the next month, bank charges or interest credited that are in the statement but not yet recorded in the books, and a standalone entry such as an ECS or auto-debit that nobody has booked. Totalling both sides separately and listing the unexplained gap explicitly is faster than hunting for a single missing row.
How do I reconcile a bank statement that has thousands of rows?+
Split the work by month first and reconcile each month separately, because a BRS is a period document. Then match on the combination of amount and date rather than on narration text, which is never consistent. If the statement has more than a few thousand rows, formulas such as COUNTIF and XLOOKUP repeated down the sheet will slow the file noticeably — the two-file matching techniques covered in our missing-values guide apply, but consider whether a purpose-built tool is a better fit once the file regularly crosses that size.
What are outstanding cheques and how long should they stay in the reconciliation?+
Outstanding cheques are instruments you have issued and recorded in your books but which the payee has not yet presented to the bank. They stay on the reconciliation until they clear. Age them, though: a cheque outstanding for more than three months is beyond its validity period in India and needs a decision — either the payee presents it after revalidation or you reverse it in the books. A reconciliation that carries the same outstanding cheque for a year is hiding a real problem.
Should I use the bank's Excel export or a PDF statement?+
Always use the Excel or CSV export if the bank provides one. A PDF has to be retyped or extracted, and every retyped figure is an opportunity for a transposition error that will cost you far more time to find than the export would have cost to obtain. Most Indian banks let you download a CSV or XLS statement for any date range from net banking.
Can I do bank reconciliation without Excel?+
Yes. Any system that can read two .csv or .xlsx files can do it — what matters is that you can state the match rules, decide an amount and date tolerance, and get back a result set per category with the reason recorded. Whether that is Excel, a Power Query workbook, or a dedicated desktop tool is a separate question from whether the reconciliation logic is sound.
Related guides
Excel Reconciliation: The Complete Guide
Match keys, normalisation, tolerance and group matching — the method behind every reconciliation.
Vendor Reconciliation in Excel
The same approach applied to a vendor ledger and a vendor's own statement.
Invoice Matching When Amounts Do Not Tie
Tolerance matching and partial payments, useful for ECS and auto-debit entries.