Free Excel template · Excel for bookkeeping
Free bank reconciliation template
For bookkeepers and owners who reconcile a bank account each month and want the working on one page. Fill in the statement balance and the book balance, list the items that are on one side but not the other, and the template works out both adjusted balances, shows the difference, and points you to transposed digits when the difference divides by 9.
Preview and try it
This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Bank reconciliation | |||
| 2 | Account | Business checking (ending 4417) | ||
| 3 | Statement date | Oct 31, 2026 | ||
| 4 | ||||
| 5 | BANK SIDE | Amount | ||
| 6 | Ending balance per bank statement | 18642.37 | ||
| 7 | ||||
| 8 | Deposits in transit (in the books, not yet on the statement) | Date | Reference | |
| 9 | Card processor payout | Oct 31, 2026 | Payout 10/31 | 2315 |
| 10 | Customer check deposit | Oct 30, 2026 | Deposit slip 388 | 640 |
| 11 | ||||
| 12 | Total deposits in transit | 2955 | ||
| 13 | ||||
| 14 | Outstanding checks and payments (in the books, not yet cleared) | Date | Check / ref | |
| 15 | Office lease, November | Oct 28, 2026 | Check 1051 | 1200 |
| 16 | Freelance designer | Oct 30, 2026 | Check 1053 | 385.5 |
| 17 | Equipment repair | Jul 15, 2026 | Check 1038 | 220 |
| 18 | ||||
| 19 | Total outstanding | 1805.5 | ||
| 20 | Adjusted bank balance | 19791.87 |
How the formulas work
Adjusted bank balance
Start from the statement balance, add deposits you have recorded that the bank had not processed by the statement date, and subtract checks and payments that had not cleared. What is left is what the bank balance will be once everything catches up.
Adjusted book balance
Start from the ledger balance, add bank credits you have not booked yet (interest, direct deposits), subtract bank charges you have not booked (fees, returned checks), and correct any keying errors. Each item on this side is also a journal entry you still need to post.
The difference
When both sides are adjusted, they should match to the cent. ROUND to 2 decimals matters: adding numbers with cents can leave tiny floating-point leftovers like 0.0000000001, which would make an exact comparison fail even though the account reconciles.
A status line that helps you look
If the account does not reconcile, the status shows the gap. When the gap in cents divides by 9, it adds a hint: swapped digits (214.27 for 212.47) always leave a multiple of 9 cents. Other common causes are an item entered twice, a deposit recorded in the wrong month, or a sign the wrong way round.
Stale outstanding checks
Counts outstanding checks dated more than 90 days before the statement date. A check that never clears was often lost or never cashed: contact the payee, and follow your firm's policy (and any local unclaimed-property rules) before voiding it. Change 90 to the age your policy uses.
Make it yours
- Reconcile every month, not at year end. Twelve small reconciliations are much easier than one big one, and errors are easier to trace while everyone still remembers the transactions.
- Every item in the Book side section needs a journal entry. Every item in the Bank side section should clear on next month's statement; if it does not, investigate.
- Insert extra rows inside each list (right-click a row inside it > Insert) so the SUM ranges grow to include them.
- Keep the statement PDF and this sheet together. A reviewer should be able to tick every number on this page back to the statement or the ledger.
FAQ
What is a deposit in transit?
Money you have recorded in your books that the bank had not added to the account by the statement date, such as a payout or check deposited on the last day of the month. It usually shows up on the next statement within a few days.
Why does my bank reconciliation not balance?
Check, in order: unbooked bank fees and interest, items recorded twice, deposits or checks recorded in the wrong month, amounts entered with the sign reversed (that leaves a difference of twice the amount), and transposed digits (the difference divides by 9). Also confirm that the opening balance in the books matches last month's reconciled balance.
Does this reconciliation template work in Google Sheets?
Yes. Upload the .xlsx to Google Drive and open it with Google Sheets. SUM, ROUND, IF, TEXT, MOD, ABS and COUNTIFS all work the same way there.
Functions used
SUMAdds numbersROUNDRounds to digitsIFPicks a result from a testTEXTNumber to formatted textABSAbsolute valueMODRemainder after divisionCOUNTIFSCounts with several conditions
Related guides
- How to subtract in Excel
- ROUND vs ROUNDUP vs ROUNDDOWN in Excel
- How to combine text with numbers and dates in Excel
- How to compare two columns in Excel
More free templates
- Accounts payable tracker templateFor bookkeepers and office managers who need to see, on one page, what the business owes and what has to be paid this week.Free .xlsx
- Expense report templateFor employees who need to claim back business costs, and for the managers and bookkeepers who check those claims.Free .xlsx
- Purchase order templateFor small businesses, office managers and buyers who send purchase orders to suppliers and want to track what actually arrives.Free .xlsx
- Accounts receivable aging report templateFor owners, bookkeepers and credit controllers who invoice customers on terms and need to see who owes what and for how long.Free .xlsx
Updated