Bank Reconciliation Template in Excel: Match Your Books to the Bank Statement
How to do a bank reconciliation in Excel: why your book balance and bank balance differ, formulas that auto-match transactions, the two-sided reconciliation layout, and a worked example with deposits in transit, outstanding checks, fees and an NSF check.
A bank reconciliation compares the cash balance in your books with the balance on your bank statement for the same date, explains every difference, and records the adjustments that belong in your books, so both sides agree. Done monthly, it catches missing deposits, unrecorded fees, duplicate entries and fraud early. A bank reconciliation template in Excel has three parts: your cash ledger, the bank statement, and a two-sided reconciliation sheet. This guide shows how to set them up and match transactions with formulas.
Why the two balances differ
| Difference | Example | Adjust which side? |
|---|---|---|
| Deposit in transit | Cash deposited on the last day, credited by the bank next month | Add to the bank balance |
| Outstanding check or payment | Check written to a supplier but not yet cashed | Subtract from the bank balance |
| Bank fees and interest | Monthly service fee, interest earned | Record in your books |
| NSF (returned) check | A customer’s check bounced | Subtract in your books and reopen the customer’s balance |
| Errors | Amount typed wrongly, payment recorded twice | Correct the side that made the error |
Only items the bank already knows about and your books do not (fees, interest, NSF checks) or your own errors require journal entries. Timing differences such as deposits in transit simply clear next month.
Set up the workbook
- Book: your cash account entries for the period: date, reference, description, amount (deposits positive, payments negative). Export them from your accounting software or your general ledger template.
- Bank: paste the downloaded statement with date, description, amount and running balance, using the same sign convention.
- Reconciliation: the two-sided summary.
Format both data ranges as Tables so formulas expand automatically, and keep a single signed Amount column rather than separate debit and credit columns; it makes matching much simpler.
Auto-match transactions
On the Book sheet, add a Matched column that looks for the same amount on the bank statement within three days:
=IF(COUNTIFS(Bank[Amount],[@Amount],Bank[Date],">="&[@Date]-3,Bank[Date],"<="&[@Date]+3)>0,"Matched","Unmatched")
Add the mirror formula on the Bank sheet to find items your books do not have yet. If you record check numbers or transaction IDs, match on those instead; they are more precise than amounts. Flag possible false matches where the same amount appears more than once with =COUNTIFS([Amount],[@Amount])>1 and review those rows by eye.
Unmatched book items at month end are usually deposits in transit and outstanding checks. Unmatched bank items are usually fees, interest, returned checks and customer payments you have not recorded.
The reconciliation sheet
| Book side | Bank side |
|---|---|
| Ending book balance | Ending bank statement balance |
| + Interest and deposits the bank recorded, not yet in books | + Deposits in transit |
| − Fees, NSF checks, payments the bank recorded, not yet in books | − Outstanding checks and payments |
| ± Book errors | ± Bank errors (contact the bank) |
| Adjusted book balance | Adjusted bank balance |
The final check: =IF(ABS(AdjustedBook-AdjustedBank)<0.005,"Reconciled","Difference: "&TEXT(AdjustedBook-AdjustedBank,"$#,##0.00")). If a difference remains, go back to the unmatched rows. A difference that is divisible by 9 often points to transposed digits.
Worked example
| Item | Book side | Bank side |
|---|---|---|
| Ending balance, 31 March | $18,240.00 | $16,874.65 |
| Interest earned | +$4.15 | |
| Monthly service fee | −$25.00 | |
| Customer check returned (NSF) | −$350.00 | |
| Deposit in transit, 31 March | +$2,600.00 | |
| Outstanding checks #1184 and #1187 | −$1,605.50 | |
| Adjusted balance | $17,869.15 | $17,869.15 |
The interest, fee and NSF check are recorded in the books. The NSF check also goes back on the customer’s balance in your accounts receivable template. The deposit in transit and the two checks should appear on April’s statement; if a check is still outstanding after several months, contact the payee.
Monthly routine
- Download the statement and confirm its opening balance equals last month’s closing balance.
- Export the cash ledger for the same dates.
- Run the match formulas and work through unmatched items.
- Prepare the reconciliation, post the needed entries and reconcile again.
- Save the finished reconciliation as a PDF with the statement.
Reconcile each bank and credit card account separately, and count the cash box against your petty cash log the same day.
Where it fits
Reconciliation is the step that makes the rest of your bookkeeping trustworthy; see the simple bookkeeping system and the bookkeeping template in Excel. More guides are in Bookkeeping & Income Tracking.
Frequently asked questions
How often should a small business reconcile its bank account?
At least monthly, before closing the month. Businesses with many transactions often reconcile weekly to catch unrecorded receipts sooner.
What if I cannot find the difference?
Check that the opening balances agree, look for duplicates, transposed digits and amounts recorded with the wrong sign. Never post a “plug” entry to force the balance.
Do I reconcile credit cards the same way?
Yes, against the card statement, treating unposted charges and payments as timing differences.
Does this work in Google Sheets?
Yes. COUNTIFS, IF, ABS and TEXT behave the same; restrict sharing because the file contains account activity.
