Free template
Bank Reconciliation Template
A bank reconciliation template has two columns. The bank side starts at the statement balance and adjusts for deposits in transit and outstanding checks. The book side starts at your recorded balance and adjusts for fees, interest and returned checks. When both reach the same adjusted balance, the account reconciles. Fill it in below or download it for Excel.
No sign-up. What you type stays in your browser.
Bank reconciliation
Adjusted for timing
Bank side
Adjusted for information
Book side
Difference
Enter both balances to start.
Saved in this browser only. Print it, or download the Excel file, to keep a copy with the statement.
How to fill it in
Five steps, once a month
For the full method with two worked examples, read bank reconciliation explained.
- 01
Use one date for both balances
The ending balance printed on the statement, and your book balance on that same day.
- 02
Tick off what matches
Every deposit and payment that appears on both the statement and in your books.
- 03
List what the bank hasn't seen yet
Deposits in transit and outstanding checks go on the bank side.
- 04
List what you haven't recorded yet
Fees, interest and returned checks go on the book side. Each one needs an entry in your books.
- 05
Get the difference to zero
Whatever is left is an error. Find it before you sign off, then file the reconciliation with the statement.
Reconciling items
Where each item goes, and whether it needs an entry
| Item | Goes on | Journal entry? | What to do |
|---|---|---|---|
| Deposit in transit | Bank side, add | No | You recorded it; the bank credits it in a day or two. |
| Outstanding check | Bank side, subtract | No | Written and recorded, not yet cashed. |
| Bank fee or service charge | Book side, subtract | Yes | Record it as a bank charges expense. |
| Interest earned | Book side, add | Yes | Record it as interest income. |
| Returned customer check (NSF) | Book side, subtract | Yes | Reverse the deposit and put the amount back on the customer's balance. |
| Error in the books | Book side, add or subtract | Yes | Correct the original transaction, not with a new one. |
| Bank error | Bank side, add or subtract | No | Ask the bank to correct it. Nothing changes in your books. |
Months behind?
Rather have someone else reconcile them?
Ledgerbrook reconciles every bank and card account in QuickBooks Online each month, and catches up the months you've missed at a fixed price per month.
Frequently asked
Bank reconciliation template FAQ
How do I do a bank reconciliation in Excel?
Put the statement balance at the top of one column and the book balance at the top of a second. Under the bank balance, add deposits in transit and subtract outstanding checks. Under the book balance, add interest and subtract fees and returned checks the bank recorded first. Sum both columns; when the totals match and the difference is zero, the account reconciles. The Excel file on this page has those formulas built in.
What is the simplest format for a bank reconciliation statement?
Two columns that end on the same number. The bank column starts at the statement balance and adjusts for timing. The book column starts at the balance in your books and adjusts for things the bank knew first. Both finish on the adjusted balance, which is the real cash figure for that date.
What is the journal entry for a bank reconciliation?
Only the book side needs entries. Bank fees go to a bank charges expense, interest to interest income, a returned check back to accounts receivable, and an error gets fixed in the original transaction. Deposits in transit and outstanding checks need no entry; they clear on next month's statement. The entries are worked through here.
How often should a bank reconciliation be done?
Every month, for every bank and credit card account, as soon as the statement arrives. A month-old difference takes minutes to trace. A year-old one can take days, because each unexplained item is buried under eleven more statements.
What if my reconciliation doesn't balance?
Look at the size of the difference first. If it matches one transaction, that item is missing from one side. If it divides evenly by 9, two digits were probably swapped. If it is twice an amount, something was entered as a deposit instead of a payment. Our guide to common QuickBooks fixes covers the usual causes.
Is this bank reconciliation template free, and does it work in Google Sheets?
Yes to both. There is no sign-up. Use the online version here, or download the Excel file and open it in Google Sheets with File, Import, Upload. The Excel file has a blank tab and the worked example filled in.