Bank Reconciliation Excel Template

Free bank reconciliation Excel template — two-column layout (bank rows vs. expected invoice payments), match status dropdown, reconciliation gap calculator, period summary.

Best for

Weekly or monthly reconciliation against a single bank account at low-to-moderate transaction volume (20-100 transactions per period).

When it stops working

Past 100 transactions per period, manual matching becomes error-prone. Multiple-bank or multi-currency reconciliation needs a deterministic algorithm and tolerance for fee deductions, partial payments, and VAT-net edge cases.

Fields in the template

Column	Type	Purpose
Date	Input	Bank statement transaction date.
Bank description	Input	The memo/description line from the bank. Often the only clue to which invoice this deposit covers.
Amount	Input	Bank statement amount. Positive for deposits, negative for withdrawals.
Cleared?	Dropdown	TRUE/FALSE — has the bank actually processed this transaction or is it pending?
Invoice #	Input	Expected invoice payment list. Cross-reference column.
Customer	Input	Counterparty for the expected payment.
Expected	Input	Invoice amount expected to be paid this period.
Status	Dropdown	Draft / Sent / Paid / Overdue. Matches your invoicing tool's state.
Matched?	Dropdown	Matched / Unmatched / Partial — your reconciliation decision per row.
Notes	Input	Reason for partial / unmatched. Used in the next period's review.

Bank reconciliation is the process of comparing bank statement transactions against your accounting records and explaining any difference. This template gives you a two-column workspace: bank statement rows on the left (columns A-D), expected invoice payments on the right (columns F-I), and a per-row match status in column J.

The reconciliation gap at the bottom shows the difference between total bank deposits and total expected payments. If the gap is zero, your books and the bank statement agree. If not, the Notes column tells you where the discrepancies came from.

How to use

Download the template. Open in Excel, Google Sheets, or LibreOffice.
Paste your bank statement rows into columns A-D (Date, Description, Amount, Cleared?).
Paste expected invoice payments into columns F-I (Invoice #, Customer, Expected, Status).
Walk each row. Set “Matched?” to Matched / Unmatched / Partial.
The reconciliation summary at the bottom computes the gap. Investigate any non-zero result.

What goes in the gap

A non-zero reconciliation gap usually comes from one of these:

Outstanding deposits — you booked the payment, the bank hasn’t credited it yet
Bank fees — wire fees, FX fees, account fees not yet recorded in your books
Partial payments — client paid less than expected; remaining balance still owed
Unknown deposits — bank credit with no obvious source; could be a duplicate, refund, or invoice you forgot to send
Errors — typos in either source

The template forces you to label each — Matched / Unmatched / Partial — so the gap traces back to specific rows.

When the template stops scaling

Two volume thresholds:

Past 50 bank transactions per period: counterparty name matching becomes the bottleneck. Bank descriptions are short (often truncated at 80 chars), abbreviated, or in capitals — “ACME CORP” vs. “Acme Corporation Inc.” manually requires reading every line.
Past 100 transactions: amount collisions multiply. Three deposits of $1,500 in one month, three open invoices at $1,500 — manual matching is error-prone.

At those volumes, deterministic algorithmic matching is worth automating. checkunpaidinvoices.com does this in 30 seconds from two CSVs: bank statement and invoice list. Fuzzy name matching, fee tolerance (±$0.50 for wire fees), VAT split detection, partial payment splits.

FAQ

What’s the difference between bank reconciliation and ledger reconciliation?

Bank reconciliation matches one bank account against your records. Ledger reconciliation is broader — it ties together the general ledger, sub-ledgers, and source documents. Bank rec is a subset.

Should I reconcile monthly or weekly?

Weekly catches errors before they age out of memory. Monthly is fine if your transaction volume is low (under 30/month) and you have discipline. Quarterly is too slow — missed transactions from 90 days ago are much harder to explain.

Does the template handle multi-currency?

No — keep one currency per file. Multi-currency reconciliation needs FX conversion at the right rate per transaction, which is its own discipline (and breaks Excel formulas if you mix currencies in one column).

Can I use this for AP (accounts payable) reconciliation?

Yes — flip the column labels. Bank withdrawals become “Bank charges” / “Vendor payments”, expected payments become “Open bills”. The structure works either direction.