Free bank reconciliation template (Excel), and how to use it

Download the template: bank-reconciliation-template.xlsx — a 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 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.

The step everyone skips: get the bank side into a spreadsheet

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.