How to Reconcile a Bank Statement in Excel

Reconciliation means proving that your records and the bank's records tell the same story — every transaction accounted for, every difference explained. Accounting software automates parts of it, but plenty of small businesses, landlords, and households do it perfectly well in Excel. Here's a workflow that scales from a personal checking account to a small business's monthly close.

Step 1: Get both sides into the spreadsheet

You need two lists: the bank's version (from your statement) and yours (from your invoice log, cash book, or accounting export). For the bank side, convert your PDF statement with our free converter — it extracts date, description, and amount columns, and processes the statement entirely in your browser. Put the bank's transactions on one sheet ("Bank") and your records on another ("Books"), each with date, description, and amount columns.

Step 2: The three totals check

Before matching individual items, check the frame: opening balance plus total deposits minus total withdrawals should equal the closing balance on the statement. In Excel, =SUM() your amount column and compare against the statement's printed totals. If the extracted transactions don't reproduce the statement's own arithmetic, find the missing or duplicated rows now — matching against incomplete data wastes everyone's time.

Step 3: Match transactions with formulas

The workhorse is COUNTIFS. On the Bank sheet, add a "Matched" column: =COUNTIFS(Books!C:C, C2, Books!A:A, ">="&A2-3, Books!A:A, "<="&A2+3) — this counts Books entries with the same amount within three days of the bank date (dates rarely align exactly, since checks and card payments settle late). Put the mirror formula on the Books sheet. Anything showing zero is unmatched; anything showing 2+ is a potential duplicate. Filter each sheet to the zeros and you have your discrepancy list in seconds.

Step 4: Explain every unmatched item

Unmatched bank items are usually fees, interest, direct debits you forgot to record, or — worth taking seriously — transactions you don't recognize. Unmatched book items are usually timing: checks written but not yet cashed, deposits in transit. Each one gets recorded, corrected, or investigated. The reconciliation is done when both filtered lists are empty or every remaining item has a written explanation.

Making it monthly

The second month is faster than the first: keep the workbook, add a new pair of sheets per month, and copy the formulas. Converting the new statement takes under a minute, and a typical personal account reconciles in ten. If you're doing this for a business with hundreds of monthly transactions, that's the point where dedicated software earns its subscription — see our QuickBooks import guide for moving your workflow there.