Reconciling a bank statement against a ledger in Google Sheets™
A bank statement and a cash book record the same payments, but rarely the same way. The bank writes its own reference, the ledger has yours. A payment posted on the last day of the month can land in the statement on the first day of the next. And whichever system books credits as negatives, the other one usually does not. None of that means the books are wrong — it means a straight row-by-row read in Google Sheets™ will never line up.
1. Work out why a row would legitimately not match
Before matching anything, separate the three reasons a bank line and a ledger line can disagree without either being an error:
- Timing. A cheque or a BACS payment can sit in the ledger for days before it clears the bank. It is real, and it will match once the date window is wide enough.
- Sign convention. Some ledgers book money in as positive and money out as negative; the bank statement usually does the opposite for the same transaction, or the two systems disagree on which way a refund or a credit note goes.
- References written differently. The ledger might record
CHQ 1042where the bank shows only104200in its narrative, or a payment reference truncated to whatever the banking rail allows.
2. Match on a key first, not on amount and date alone
Where a usable reference exists on both sides — a cheque number, a payment reference, an invoice number quoted in the narrative — clean and compare that first, the same way as any other two-table reconciliation:
=REGEXREPLACE(REGEXREPLACE(LOWER(TRIM(TO_TEXT(B2))), "[^a-z0-9]", ""), "(^|[^0-9])0+([0-9])", "$1$2")
That lower-cases, strips punctuation and spaces, and drops leading zeros, so CHQ-1042
and chq 1042 become the same key. Look each row up on the other side with
XLOOKUP, exactly as for any other reconciliation. Whatever is left over after
that pass is where a bank reconciliation gets harder than most.
3. Fall back to amount and date for what is left
A bank narrative frequently carries no usable reference at all: a card payment, a direct debit, bank charges. For those, the only realistic key is the amount and roughly the date. That is weaker than a reference match — two unrelated payments for the same amount on the same day will look identical — so treat anything matched this way as a lead to check, not a settled fact.
4. Sign conventions have to be agreed, not assumed
Decide, for each side, which sign means money in and which means money out, and make the two conventions meet before comparing. If the ledger books a credit note as a positive adjustment and the bank shows the resulting refund as a negative, comparing the raw numbers will show a difference twice the size of the real one, in the wrong direction.
5. Widen the date window before assuming a break
A payment dated the 31st in the ledger and the 2nd at the bank is a timing difference, not a missing transaction. Before treating anything as unmatched, allow a few days either way and see whether it reappears. Anything still unmatched after that is a real candidate for investigation, not an artefact of month-end timing.
The shortcut
Two-File Reconciliation is a Google Sheets™ add-on built for exactly this: a ledger against a bank export, matched on a key with the reference clean-up above built in. Each compared column has its own rule — exact, number, date or similar text — and a numeric column can be given a tolerance and, where one system books credits negative and the other does not, a sign-flip option, so the two conventions are reconciled rather than compared raw. A date column can be matched on the same day, the same month, or within a stated number of days either way, which is what timing differences need. It writes what agrees, what differs, and what is on one side only as new tabs, and never touches your source tabs. Free up to 1,000 rows a side.
Install: in Google Sheets™, Extensions › Add-ons › Get add-ons, search for “Two-File Reconciliation”. The guide walks through a first run. Using Excel? The Excel add-in does the same.
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Microsoft and Excel are trademarks of the Microsoft group of companies. Two-File Reconciliation is not affiliated with or endorsed by Google or Microsoft.