Free bank reconciliation template for Excel (with a worked example)
A bank reconciliation proves that the cash balance in your books agrees with the bank, and explains every dollar of difference. This free Excel template sets out the standard two-sided format, totals each list for you, and tells you whether you're reconciled. If you're not, it suggests where to look. Below: what's in the file, how the format works, a worked example that balances to the cent, and the journal entries to post afterwards.
Download the free bank reconciliation template (.xlsx)
Three sheets: a blank Reconciliation, a filled-in Example that balances, and How to use. It uses only SUM, SUMIF, ROUND, IF, MOD, ABS and TEXT, so it works in Excel, Google Sheets (File, Import), LibreOffice and Numbers. No sign-up, no email, no macros.
1. What's in the template
- Header: account, statement end date, prepared by and reviewed by, so the sheet works as a monthly workpaper.
- Bank side: the statement's ending balance, plus deposits in transit, minus outstanding checks and payments, plus or minus bank errors, which gives the adjusted bank balance.
- Book side: the general ledger balance, plus interest and credits not yet recorded, minus fees, NSF checks and charges not yet recorded, plus or minus book errors, which gives the adjusted book balance.
- Difference and status: adjusted bank minus adjusted book. It reads Reconciled at 0.00. Otherwise a Where to look line names the amount to search for, half of it (a reversed sign), and whether the difference divides by 9 (transposed digits).
- Five lists that feed the summary: deposits in transit (15 rows), outstanding checks and payments (20), bank errors (5), items on the statement but not in your books (15), and book errors (5). Yellow cells are inputs. Everything else is a formula.
Copy the sheet for each month (right-click the tab, Move or Copy, Create a copy) so every reconciliation stays on file.
2. The bank reconciliation format
The standard format has two sides that should land on the same number. Each reconciling item goes on exactly one side, depending on who hasn't recorded it yet.
| Item | Goes on | Book entry? |
|---|---|---|
| Deposit in transit (in your books, not on the statement yet) | Bank side, add | No, it clears next month |
| Outstanding check or payment (not cleared yet) | Bank side, subtract | No, it clears when cashed |
| Bank error (wrong amount, or another customer's item) | Bank side, add or subtract | No, the bank corrects it |
| Interest earned, or a credit the bank collected | Book side, add | Yes |
| Bank fee, or an automatic payment not yet entered | Book side, subtract | Yes |
| NSF (returned) customer check | Book side, subtract | Yes |
| Book error (recorded for the wrong amount) | Book side, add or subtract | Yes, a correcting entry |
The rule of thumb: if the bank hasn't recorded it, it goes on the bank side. If you haven't recorded it, it goes on the book side. Only book-side items change your ledger.
3. Worked example
The template's Example sheet (a fictional business) reconciles an operating checking account for the month ended September 30, 2026. Before any adjustments, the statement shows 18,642.37 and the books show 17,513.61, a gap of 1,128.76. Three bank-side items and four book-side items explain it.
Bank side
| Ending balance per bank statement | 18,642.37 |
| Add: deposit in transit, customer deposit made September 30 | 2,315.00 |
| Less: outstanding check 2041 | (1,180.50) |
| Less: outstanding check 2044 (October rent, mailed September 28) | (2,400.00) |
| Less: online bill payment sent September 29, not cleared | (286.14) |
| Adjusted bank balance | 17,090.73 |
Book side
| Ending balance per books | 17,513.61 |
| Add: interest earned, on the statement | 3.12 |
| Less: monthly service fee | (15.00) |
| Less: customer check returned NSF | (420.00) |
| Add: check 2037 recorded as 154.80, cleared for 145.80 | 9.00 |
| Adjusted book balance | 17,090.73 |
Both sides land on 17,090.73, so the difference is 0.00. The arithmetic: 18,642.37 + 2,315.00 − 1,180.50 − 2,400.00 − 286.14 = 17,090.73, and 17,513.61 + 3.12 − 15.00 − 420.00 + 9.00 = 17,090.73.
Notice the book error. 154.80 and 145.80 use the same digits, and the 9.00 difference divides by 9. That's the classic sign of transposed digits, and the template points it out when a difference like that is left over.
4. How to fill it in, step by step
- Gather both sides for the same date. Get the bank statement and run a report of the same bank account from your books up to the statement end date (in QuickBooks Online an Account QuickReport, in Xero the Account Transactions report). If the statement is only a PDF, the bank statement PDF to Excel converter gives you rows you can sort and tick off.
- Enter the two ending balances in the yellow cells at the top of the Bank side and the Book side.
- Tick off what's on both. Match checks by number first, then other amounts with dates a few days apart. Our guide to comparing a bank statement to the general ledger in Excel has COUNTIFS and XLOOKUP formulas for this step.
- List what's left in your books on the bank side: deposits in transit and outstanding checks and payments. Bring forward last month's outstanding items that still haven't cleared.
- List what's left on the statement on the book side: interest, bank fees, returned checks and automatic payments you haven't entered.
- Put amounts that differ under bank errors or book errors, with the sign that corrects them.
- Read the status. At 0.00 you're done: post the book-side entries below and file the sheet. If not, see when the difference isn't 0.00.
Reconcile oldest first. Each month's starting point is the previous month's reconciliation, so work forward in date order. If last month never reconciled, this month won't either. See reconciliation beginning balance is off.
5. Journal entries after you reconcile
Every book-side item needs an entry in your books, dated in the statement period, or next month starts out of balance. In the worked example:
| Item | Entry | Amount |
|---|---|---|
| Interest earned | Debit Checking, credit Interest income | 3.12 |
| Monthly service fee | Debit Bank service charges, credit Checking | 15.00 |
| Returned NSF customer check | Debit Accounts receivable (the customer), credit Checking | 420.00 |
| Check 2037 recorded 9.00 too high | Debit Checking, credit the expense the check was coded to | 9.00 |
In QuickBooks or Xero you'd normally enter these as ordinary transactions (a deposit for the interest, an expense or spend money for the fee, and so on) or edit the mistyped check, rather than a raw journal entry. Either way the ledger balance moves to the adjusted book balance. Bank-side items need nothing: they clear on next month's statement, where you tick them off.
6. When the difference isn't 0.00
The template's Where to look line uses the size of the difference, which usually points straight at the cause:
- The difference equals one transaction: it's missing from one of the lists, or ticked on one side only.
- The difference is twice a transaction: that item has the wrong sign, for example a payment entered as a deposit.
- The difference divides by 9: look for transposed digits, such as 154.80 entered as 145.80.
- It's an outstanding item from months ago: an old check that will never clear may need to be voided, and a stale deposit in transit is a red flag worth investigating.
Our guide on how to find a bank reconciliation discrepancy walks through all 7 causes in order. If you already imported the statement, check for duplicate transactions after an import too.
7. Getting the lists faster
Filling in the template is quick. Building the lists is the slow part: ticking off a few hundred lines by hand to find the dozen that don't match.
Let the checker build the lists for you
Utilly's bank reconciliation checker takes the bank statement (PDF, CSV, Excel, OFX, QFX or QBO) and an export of the same account from QuickBooks, Xero or any register or ledger CSV, and matches them by amount within a date window (checks by number). It lists what's in your books but not on the statement (outstanding checks and deposits in transit: your bank-side lists), what's on the statement but not in your books (fees and interest: your book-side list), amounts that differ, reversed signs and possible duplicates, and shows how they add up to the difference. The summary and the first 3 items of each kind are free with no sign-up; the full list with CSV and PDF reports is 1 credit. Files are processed in memory and deleted after 1 hour.
Working from a QuickBooks report? The QuickBooks reconciliation discrepancy finder is the same checker with QuickBooks export notes. Comparing against a full general ledger export? Use compare a bank statement to the general ledger.
Method: the two-sided format (adjusted bank balance and adjusted book balance) is the standard bank reconciliation layout used in accounting textbooks and public-sector guidance. The example business, names and amounts are fictional. Template built and checked October 21, 2026: the Example sheet recalculates to a 0.00 difference in LibreOffice.
FAQ
What is a bank reconciliation template?
A spreadsheet that lays out the standard reconciliation format: the bank statement's ending balance adjusted for deposits in transit, outstanding checks and bank errors on one side, and your books' balance adjusted for interest, fees, NSF checks and book errors on the other. When both adjusted balances agree, the account is reconciled.
Is the template free?
Yes. Download the .xlsx with no sign-up or email address. It has a blank sheet, a worked example and instructions, and you can change and share it.
Does it work in Google Sheets?
Yes. It uses only SUM, SUMIF, ROUND, IF, MOD, ABS and TEXT. In Google Sheets choose File, Import and upload the .xlsx. It also opens in LibreOffice and Numbers.
Which side do deposits in transit and outstanding checks go on?
The bank side. They're already in your books but the bank hasn't processed them yet: add deposits in transit to the statement balance and subtract outstanding checks and payments.
Which reconciling items need a journal entry?
Only book-side items: interest, bank fees, NSF (returned) checks, automatic payments you hadn't recorded, and corrections of book errors. Bank-side items clear on next month's statement and need no entry.
What if the difference isn't zero?
Use its size. A difference equal to one transaction means it's missing from a list; twice a transaction means a reversed sign; a difference divisible by 9 suggests transposed digits. The template's Where to look line shows these for you.
Do I still need a template if I use QuickBooks or Xero?
Both have reconciliation screens built in, so you don't need one to tick off transactions. A template is still useful as a monthly workpaper for review, for accounts or periods kept outside the software, and for catch-up work where you're proving a balance before importing.
Related guides
- How to import bank transactions into QuickBooks Desktop after bank feeds stopped (2026)
- How to convert a credit card statement PDF to Excel
- QBO vs IIF vs QIF: which file QuickBooks and Quicken actually need
- Catch-up bookkeeping: getting 12 months of bank statements into QuickBooks or Xero and reconciled
- Xero bank statement import errors (and how to fix them)
- How to upload a PDF bank statement to QuickBooks Online (and check nothing was missed)
- QuickBooks Desktop IIF import errors (File not imported, Review now, and how to fix them)
- QuickBooks .QBO (Web Connect) import errors: OL-222, OL-221, “unable to verify the financial institution” and how to fix them
- QuickBooks reconciliation not matching: beginning balance is off, or the difference isn't 0.00
- Duplicate transactions in QuickBooks after an import: find them, remove them, and stop them coming back
- How to import bills into QuickBooks Online from a CSV or Excel file
- Xero bill import CSV template explained: every column, tax setting and error
- How to find a bank reconciliation discrepancy: 7 causes and a 15-minute method
- How to compare a bank statement to the general ledger in Excel (and a faster way)
- Receipt OCR without a subscription: turn receipts into a CSV for QuickBooks or Xero
Related tools
- Bank reconciliation checker: compare a bank statement to your books and list missing, duplicate and mismatched transactions.
- Receipt to CSV: turn PDF or photo receipts and invoices into CSV, Excel, or QuickBooks and Xero bill imports.
