How to reconcile deposits to your bank statement in Excel
Reconciling deposits means proving that the money your books say you collected actually landed in the bank — every deposit, for the right amount, on the right date. Here's a clean Excel method that works up to a few hundred rows a month.
Step 1 — get both sides into the same shape
Put your expected deposits (from your books/POS/EMR) on one sheet and your bank deposits (from the statement export) on another. Each needs at least a date and an amount, ideally a reference. Before you compare anything, clean both sides: run TRIM/CLEAN on text fields and use Text to Columns to fix any amounts that imported as text. Dirty data fakes more "exceptions" than real errors do.
Step 2 — match on amount (and date)
In a helper column on your books sheet, count how many times each amount appears on the bank side:
=COUNTIF(Bank_Amounts, ROUND(B2,2))
Round both sides to two decimals to kill penny noise. 0 means that deposit never hit the bank (or hit for a different amount). Run the same formula the other direction — on the bank sheet, pointing back at your books — to catch deposits that are in the bank but not in your books. Both directions, every time; that's the half most people skip, and it's why reconciliations don't tie. The full technique is in how to compare two lists in Excel.
Step 3 — make the breaks obvious
Select each helper column and add a Conditional Formatting rule: Highlight Cell Rules → Equal To → 0 → red fill. Now every unmatched deposit lights up on both sheets.
Step 4 — work the exceptions
For each highlighted row, there are only a few explanations: timing (deposited near a month boundary), a combined deposit (several receipts banked as one lump), a fee or adjustment, a keying error, or a genuine missing deposit. Note the cause next to each — that note is your reconciliation.
The wall this method hits
COUNTIF matches one amount to one amount. It can't untangle a lump deposit made of several receipts, and it gets slow and error-prone across multiple entities every month. That specific problem — which receipts make up which deposit, across entities — is exactly what TrueBalance automates with a three-pass matching engine, so the exceptions are the only thing left to review. If that's your month-end, tell us what it looks like.