Bank reconciliation means proving that every transaction in your books also appears on the bank statement, and that the ending balances match. Done in Excel, it is one of the highest-leverage controls a small business can run — and it is far faster when you stop typing statement data by hand. This guide walks through the full workflow, from PDF to a clean, matched spreadsheet.
Step 1: Get the statement into rows, not screenshots
Reconciliation lives or dies on data quality. If you retype the statement, you are auditing your own typos. Instead, download the statement PDF from online banking and convert it to Excel so every transaction lands in its own row with a date, description, and amount. Because the converter runs balance verification, you know the extracted rows add up to the statement's closing balance before you start matching.
Step 2: Structure two tables side by side
Put your book records (ledger export, or the register you maintain) on one sheet and the converted statement on another. Give both the same four columns:
- Date — normalize both sides to the same format (YYYY-MM-DD avoids the classic 03/04 vs 04/03 mix-up).
- Description — keep the raw bank memo on the statement side; matching by amount and date is more reliable than by text.
- Amount — one signed column (money out negative, money in positive). If your books use separate Debit/Credit columns, add a helper column that computes the signed value.
- Match key — a helper column like =TEXT(Date,"YYYY-MM-DD")&"|"&TEXT(ABS(Amount),"0.00") that makes matching mechanical.
Step 3: Match transactions with formulas
With match keys in place, reconciliation becomes a lookup problem:
- COUNTIF check — on the books sheet: =COUNTIF(Statement!$D:$D, D2). Zero means the transaction is missing from the statement; one or more means it matched.
- Flag matched rows — filter both sheets for a count of zero. Those rows are your reconciliation items, not the hundreds that matched.
- Mind timing differences — deposits in transit and outstanding checks are legitimate zeros. Date-shift them a few days and they usually match the next statement.
- Duplicates — a COUNTIF of 2+ on either side means the same key appears twice, usually a duplicate entry or a split transaction. Investigate these first.
Step 4: Clear the differences
Everything unmatched falls into a small set of buckets: bank fees or interest you have not recorded, checks still outstanding, deposits in transit, and genuine errors. Record the fees and interest in your books, list the timing items on a short reconciliation summary, and chase the errors. When unmatched items net to zero and your adjusted book balance equals the statement closing balance, you are done.
Keep it monthly
Reconciling monthly keeps the unmatched list short enough to clear in minutes. Waiting a quarter turns a 20-minute task into an afternoon. Convert your statement as soon as it posts, run the match, and move on — your future self (and your accountant) will thank you.