Free bank reconciliation template (Excel), with a worked example

A bank reconciliation proves that your ledger's cash account and the bank statement agree for the same month, once deposits in transit, outstanding checks and unbooked bank fees are accounted for. This template is built for exactly that, one bank account per sheet. For a credit card, general ledger, balance sheet or clearing account, use the account reconciliation template for GL and balance sheet accounts instead.

Download the template: bank-reconciliation-template.xlsx: a simple two-sided reconciliation with the totals, adjusted balances and difference already wired up as formulas, plus a second sheet for ticking statement lines off against your ledger. No macros, no sign-up, opens in Excel, Google Sheets and LibreOffice.

What the bank reconciliation template does

A reconciliation has two columns that must meet in the middle. The template implements exactly that, with yellow cells for inputs and blue cells for formulas:

The last row subtracts one adjusted balance from the other. When that cell reads 0.00 the account is reconciled; the status cell says so. Everything the book side adds or subtracts is also a journal entry you still owe your ledger: that's the point of listing them rather than just forcing the number.

How to use it, step by step

1. Enter the two ending balances: the statement's closing balance on the bank side, your ledger's balance for the same date on the book side.
2. Tick off the statement against the ledger. Every line on the statement should match a line in your books. The second sheet exists for this: paste the statement's transactions in, mark each one Y when you find it in the ledger, and the sheet counts and totals what's left.
3. Statement lines with no ledger match go on the book side: fees, interest, returned items, a direct debit you forgot. They are real, the bank already processed them, and your books are wrong until they're entered.
4. Ledger lines with no statement match go on the bank side: checks you wrote that haven't been presented, a deposit made after the cutoff. They are timing differences, not errors, and should clear on the next statement. One that doesn't clear for months is a question, not a timing difference.
5. Read the difference cell. Zero means done. Anything else means step 2 missed something: see below for how to find it fast.

When it won't go to zero

The difference itself usually tells you where to look:

• Equals one transaction amount: that transaction is on one side only. Missing entry or a duplicate.
• Divisible by 9: a transposition. 1,254 entered as 1,245 produces a difference of 9; transposed hundreds produce 90 or 900.
• Exactly twice an amount: the item was posted on the wrong side: a deposit entered as a payment, or vice versa. Credit-card statements are the classic source, because their sign conventions are inverted.
• An odd amount that matches nothing: usually two or more small errors stacked. Go back to the tick-off sheet and work through the unmatched lines in date order.
• The same difference every month: the opening balance was wrong. Reconcile the earliest period you can and carry forward.

A worked example

The same month, filled in. The statement closes at $12,480.55; the ledger says $12,876.15. Neither is wrong yet: they just haven't seen the same things.

On the first pass the difference cell read 9.00, not zero. Nine divides by nine: a transposition. Check #1045 had been keyed into the outstanding list as 1,284.30. Corrected, the sheet reads Reconciled, and the three book-side lines (interest, the service charge, the returned check) become the month's adjusting entries. Save the file with the statement, and next month's starts from a clean opening balance. Reconciling monthly, against each statement as it arrives, is what keeps the list this short.

Get the bank statement into Excel first

The tick-off in step 2 is where reconciliations go slow, because the bank side is a PDF and the book side is a ledger, and you end up reading one screen and typing into another. Converting the statement into rows first turns that into a sort-and-match job. Our bank statement converter does the conversion and then runs the same arithmetic this template does, it checks that the extracted rows actually add from the printed opening balance to the printed closing balance, so the numbers you paste into the second sheet have already been reconciled against the statement before you compare them to the books. For a QuickBooks account, the same rows can go in as a .qbo bank feed instead of a paste.

A few conventions worth keeping

• Reconcile every statement, in order, even the boring ones: a skipped month hides errors in the next.
• Save each finished reconciliation with the statement it belongs to. A year later, "why is this check still outstanding" is answerable only if the working papers exist.
• Book the adjustments the same day you find them. A reconciliation that balances on paper but not in the ledger hasn't happened yet.
• If you handle several clients, keep one file per client per account: write-up shops that reconcile dozens of accounts a month live or die by that filing discipline.

Reconciling inside QuickBooks Online rather than in a spreadsheet? The same logic applies on its reconcile screen: see how to reconcile a bank statement in QuickBooks Online, including what to do when the difference won't reach zero.