Your bank statement says one number. Your books say another. A bank reconciliation explains the difference, line by line, until the two agree. It’s the single most useful monthly check a small business can do: it catches missed transactions, bank fees you never recorded, bounced customer checks, duplicate entries and, occasionally, fraud.
This page gives you a free monthly bank reconciliation template for Excel and Google Sheets, a free online version you can use right here, and a plain-English walkthrough of how to reconcile in five steps.
Free, no sign-up. Works in Excel, Google Sheets and LibreOffice.
Free online bank reconciliation calculator
Enter your statement balance, your book balance and the items that differ, or click Load example. The tool builds a two-sided reconciliation, tells you whether you’re reconciled, and lists the adjusting entries you need to record. You can print it, save it as a PDF, download it as Excel, Word or CSV, or send a link to your accountant.
3. Your bank reconciliation
Adjusting entries to record in your books
Outstanding items to carry forward
Prepared by: ____________________ Reviewed by: ____________________ Date: __________
Made with the free bank reconciliation tool at bookkeepingsheets.com
What is a bank reconciliation?
A bank reconciliation compares the ending balance on your bank statement with the cash balance in your own records (your spreadsheet, QuickBooks, Xero or whatever you use) for the same date, and accounts for every difference between them.
The two balances almost never match on their own, and that’s normal. Differences come from two places:
- Timing differences: things you’ve recorded that the bank hasn’t processed yet. A check you mailed on the 29th, or a deposit you made after the bank’s cut-off, shows up in your books this month and on the statement next month.
- Things you haven’t recorded yet: items the bank knows about that you don’t, such as service charges, wire fees, interest, automatic payments, ACH deposits and customer checks that bounced.
Errors, on either side, are the third category. They’re less common, but finding them is the whole point.
What’s inside the template
The workbook has four sheets:
- Start Here: enter your business name, the bank account, the statement date, the ending balance per bank and the ending balance per books. A result box shows the adjusted balances, the difference and a green Reconciled or red Difference to investigate status.
- Items: one row per reconciling item, with a Status (Open or Cleared), date, type, check number, description and amount. The Type dropdown decides whether the item goes on the bank side or the book side, and whether it adds or subtracts. There’s room for 150 items.
- Reconciliation: the printable report. Bank side on top, book side below, each with a subtotal for every kind of item and the number of items behind it, plus prepared-by and reviewed-by lines.
- Lists: the 11 item types with a one-line explanation of each. Read it once, and you’ll know where any item belongs.
Inputs are blue text on a yellow background; everything else is a formula. The template comes filled with an example so you can see how it works. Delete the example rows on the Items sheet before you start.
How to reconcile a bank account in 5 steps
Step 1: Get both balances for the same date
Take the ending balance from your bank statement, then the balance of your cash (checking) account in your books on the statement’s closing date. Enter both on the Start Here sheet. If your statement closes on the 28th, use the 28th in your books too, not the end of the month.
Step 2: Tick off what matches
Go down the statement and mark every deposit and withdrawal that also appears in your books for the same amount. Most transactions match. What’s left on either side is your list of reconciling items.
Step 3: List the bank-side items
These are items in your books that aren’t on the statement yet:
- Deposits in transit are added to the bank balance.
- Outstanding checks (and other payments that haven’t cleared) are subtracted from the bank balance.
- Bank errors, such as a deposit credited to the wrong account, are added or subtracted. Report them to your bank as well.
Step 4: List the book-side items
These are items on the statement that aren’t in your books:
- Interest earned and deposits not in your books (an ACH payment from a customer, a Stripe or Square payout) are added to the book balance.
- Bank fees, NSF or returned checks and payments not in your books (automatic debits) are subtracted from the book balance.
- Book errors, such as a check recorded as $245.00 that actually cleared for $254.00, are added or subtracted by the difference.
Step 5: Compare, then record the adjustments
When the adjusted bank balance equals the adjusted book balance, you’re reconciled. Then record every book-side item in your books, because those are real transactions you missed. Bank-side items need no entry; they’ll clear on next month’s statement. Save the reconciliation with the statement for your records.
Example: a reconciliation that balances
Here’s the example built into the template and the online tool. The statement shows $12,480.25 and the books show $13,005.88, a difference of $525.63. After the reconciling items, both sides land on the same number.
| Bank side | Amount |
|---|---|
| Ending balance per bank statement | $12,480.25 |
| Add: deposits in transit (2) | $2,765.60 |
| Less: outstanding checks #1187, #1189, #1190 | ($2,152.75) |
| Adjusted bank balance | $13,093.10 |
| Book side | Amount |
|---|---|
| Ending balance per books | $13,005.88 |
| Add: interest earned | $3.42 |
| Add: Stripe payout not recorded | $612.80 |
| Less: service charge and wire fee | ($40.00) |
| Less: returned customer check (NSF) | ($480.00) |
| Less: check #1183 recorded $9.00 too low | ($9.00) |
| Adjusted book balance | $13,093.10 |
Difference: $0.00. Reconciled. The five book-side items add up to a net $87.22 that now needs to be recorded in the books. Note that check #1176 from the prior month is marked Cleared, so it stays on the list for reference but doesn’t count.
The formulas behind the template
The template relies on two ideas: every item type has a side and a sign, and only open items count.
- Side looks up the type on the Lists sheet:
=INDEX(TypeSide, MATCH(C5, TypeList, 0)). - Effect turns a positive amount into a signed one and ignores cleared items:
=IF(OR(C5="", A5="Cleared"), 0, F5*INDEX(TypeSign, MATCH(C5, TypeList, 0))). - Each line of the report is a SUMIFS on that signed column, for example deposits in transit:
=SUMIFS(ItSigned, ItType, "Deposit in transit"). - Difference is
=ROUND(AdjBank-AdjBook, 2). Rounding avoids a “difference” of $0.0000001 caused by floating-point math.
Because amounts are always entered as positive numbers, you never have to remember whether an outstanding check is “minus” or a fee is “negative”. The type does that for you. All functions are INDEX/MATCH and SUMIFS, so the file works the same in Google Sheets and older versions of Excel.
Common bank reconciliation mistakes
- Using balances from different dates. The book balance must be as of the statement’s closing date, not today.
- Putting an item on the wrong side. A bank fee goes on the book side, because your books are the ones missing it. Ask “who doesn’t know about this yet?”
- Forgetting to record the book-side items. The reconciliation balances on paper, but next month you start from the same wrong number.
- Forcing it with a plug. A “miscellaneous adjustment” that makes the difference zero hides the problem. Track down the real cause; a difference divisible by 9 often points to transposed digits (like $245 vs. $254).
- Letting outstanding checks sit forever. A check that hasn’t cleared in a few months may be lost. Under the Uniform Commercial Code, banks generally aren’t obligated to pay a check presented more than six months after its date, so contact the payee and void and reissue if needed. Unclaimed amounts can eventually fall under your state’s unclaimed property rules.
- Reconciling only at year-end. Twelve months of differences take a weekend to untangle. One month takes 20 minutes.
Bookkeeping Kit Pro 2026 links your P&L, invoices, bills, bank rec, mileage and quarterly tax in one Excel workbook (works in Google Sheets). The free templates on this site stay free.
Related calculators and templates
- Free Small Business Bookkeeping Spreadsheet
- How to Do Bookkeeping in Excel
- Intercompany Reconciliation Template for Multi-Entity Businesses
- Free Accounts Receivable Aging Report Template
Watch: the 30-second version
▶ New 30-second money-math videos every week: subscribe to Bookkeeping Sheets on YouTube.
FAQ
How often should a small business reconcile its bank account?
Every month, as soon as the statement arrives. Businesses with a lot of card or cash activity often reconcile weekly. Do the same for every business bank account and credit card.
Does a bank reconciliation have to balance to the penny?
Yes. A cash account should reconcile exactly. Even a small unexplained difference can be two larger errors that partly cancel each other out.
What’s the difference between a deposit in transit and a deposit not in books?
A deposit in transit is in your books but not yet on the bank statement, so it’s added to the bank balance. A deposit not in books is on the statement but you haven’t recorded it, so it’s added to your book balance and you record it.
Do I need a reconciliation if I use QuickBooks or Xero?
Yes. Bank feeds import transactions, but they don’t guarantee your books are right. Duplicates, missed transactions and miscategorized transfers all slip through. Accounting software has its own reconcile screen; this template is useful when you keep books in a spreadsheet or want a clean, printable report for a file or a reviewer.
Does the template work in Google Sheets?
Yes. Upload the .xlsx file to Google Drive and open it with Google Sheets. The dropdowns, formulas and color highlights all carry over.
Related templates
Reconciliation is step four of our guide to bookkeeping in Excel. If you need a place to record transactions in the first place, start with the small business bookkeeping spreadsheet. Bounced customer checks belong on your accounts receivable aging report, outstanding vendor checks on your accounts payable tracker, and once your cash is right, a 13-week cash flow forecast shows what’s coming next.
This guide and template are for general record-keeping and information. They aren’t tax, legal or accounting advice. For questions about your own situation, talk to a qualified accountant or tax professional.