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

Reconciliation doesn't need software — it needs discipline and one well-built sheet. Here's ours: a free Excel template with the arithmetic already wired, so the sheet itself tells you whether the month reconciles.

Download the template (.xlsx, free, no email required)

What's in it

• Opening and closing balance cells — copy both straight from the statement.
• A transaction table with money-out, money-in, and an automatic running balance per row.
• A verdict row: the sheet sums your entries, computes the closing balance they imply, compares it to the statement's declared closing balance, and prints "YES — reconciled" only when the difference is under a penny. No eyeballing.
• Forty transaction rows to start; insert more and the formulas extend.

How to use it

1. Enter the statement's opening and closing balances (cells B5 and B6).
2. Fill the transaction rows — from the statement, not from memory. Money out and money in are separate columns, both entered as positive numbers.
3. Tick each row off against your books in the Matched? column.
4. Read the verdict. If the difference isn't zero, the running-balance column shows you where the chain first drifts from the statement's printed balances — start looking there.

Filling the table without typing

The template's slowest step is keying in the transactions. Skip it: convert the statement PDF with NoRekey and paste the CSV rows straight into the table — dates, descriptions and amounts land in the right columns. Conversions are themselves balance-checked against the statement's declared totals before you download, but still review the rows and compare the worksheet to the books. Matching statement arithmetic does not by itself reconcile the ledger or prove every extracted field.

When the sheet says "No"

The difference amount is your clue. A difference equal to one transaction means a missed or doubled row. A difference that's exactly twice some transaction usually means a sign error — money-in entered as money-out. A difference of a few pence points at rounding on a converted-currency line. And when the difference matches nothing at all, work the running-balance column from the top; the first row where it disagrees with the statement's printed balance is where the story starts.

For monthly close work, pair the template with the month-end reconciliation checklist approach — one month, one sheet, one verdict, then move on.