Why shouldn't I match on amount alone?

Two different transactions with the same amount, such as two 50.00 payments, hide each other: COUNTIF finds one match for both. Add a date window with COUNTIFS, and use an occurrence key (amount plus a running count) to pair repeated amounts in order.

Should I use XLOOKUP or VLOOKUP?

XLOOKUP (Microsoft 365 and Excel 2021 or later) can test amount and date together and return a default like "Not in books" when nothing matches. VLOOKUP only looks up one value and returns the first hit. On older versions, use INDEX and MATCH with the same two-part test as an array formula.

My ledger shows deposits as debits. Is that a problem?

Only until you flip it. In a general ledger, money into a bank account is a debit, which is the opposite of how a bank statement shows it. Build one signed column as Debit minus Credit on the books side so deposits are positive on both sheets.

How do I know the differences I found explain the whole gap?

If the opening balances agree, statement closing balance minus book balance should equal the bank items not in the books minus the book items not on the bank. If there's still a leftover, look for transposed digits (divisible by 9), a reversed sign (twice an amount) or a duplicate that matched twice.

How should I handle checks that clear weeks later?

Don't widen the date window for everything, because monthly charges would then match last month's entry. Match checks by check number instead, and leave checks that haven't cleared as outstanding.

Is there a faster way than building the spreadsheet every month?

Utilly's bank reconciliation checker takes the statement (PDF, CSV, Excel, OFX, QFX or QBO) and a books export, matches by amount within a date window and checks by number, and lists missing items, amount differences, reversed signs and duplicates. The summary and first 3 items of each kind are free; the full report is 1 credit.

How to Compare a Bank Statement to the General Ledger in Excel (Formulas + a Faster Way)

Q: How do I compare a bank statement to the general ledger in Excel?

Export the statement period from the bank and the same bank account from your books, put each on its own sheet with Date, Description and Amount, make dates real dates and amounts signed numbers (money in positive on both), then use COUNTIFS on amount plus a date window of a few days to mark each row as matched or not. Sum the unmatched rows on each side to explain the difference.

You have a bank statement on one side, the general ledger's cash account on the other, and a difference you need to explain. Excel can do the matching if both lists are clean and you match on more than the amount. Here's a method that works in any recent version of Excel, the formulas to paste, the traps that make a match look right when it isn't, and a faster way when you'd rather not build it every month.

On this page

Export both sides for the same period
Normalize dates, amounts and signs
Match on date and amount
Flag duplicates and repeated amounts
Sum the differences to explain the gap
Common pitfalls
A faster way
FAQ

1. Export both sides for the same period

Bank side: download the statement period as CSV or Excel from online banking. If you only have the PDF statement, convert it first (the bank statement PDF to Excel converter checks that the rows add up to the closing balance, so you know nothing was dropped).
Books side: export only the bank account you're reconciling, for the statement's dates. In QuickBooks Online, run an Account QuickReport or Transaction Detail by Account for that account and export to Excel. In QuickBooks Desktop, a Custom Transaction Detail report filtered to the account. In Xero, the Account Transactions report for the bank account. A whole general ledger export mixes in every other account, and those rows will all look like differences.
Write down four numbers: the statement's opening and closing balances, and the books' balance for the account on the same two dates. If the opening balances already disagree, the problem started in an earlier month: see reconciliation beginning balance is off first.

Paste the bank rows into a sheet named Bank and the books rows into a sheet named Books, each with three columns: A Date, B Description, C Amount. Delete subtotal, header and balance-forward rows.

2. Normalize dates, amounts and signs

Most failed matches are formatting, not bookkeeping. Fix these before writing a single lookup.

Problem	How to spot it	Fix
Dates stored as text	`=ISNUMBER(A2)` returns FALSE, or dates sit on the left of the cell	Data → Text to Columns → Date (pick MDY or DMY to match the file), or `=DATEVALUE(A2)`
Day and month swapped	Dates after the 12th fail or land in the wrong month	Re-import with the right date order; don't fix rows one by one
Amounts stored as text	`=ISNUMBER(C2)` is FALSE; values like `$1,234.50` or `(312.88)`	Strip the symbols with the formula below the table, then Paste Special → Values
Debit and Credit in two columns	Two money columns, one of them blank on each row	Make one signed column: bank `=Credit-Debit` (money in positive)
Opposite sign conventions	Deposits are positive on the statement but negative in the books, or the other way round	In a ledger, money into the bank is a debit. Use `=Debit-Credit` on the books side so deposits are positive on both
Floating-point leftovers	Two amounts that look equal don't match	Wrap amounts in `=ROUND(C2,2)`

Turning text amounts like $1,234.50 and (312.88) into numbers:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"$",""),",",""),"(","-"),")",""))

When you're done, both sheets should have real dates in A, a description in B, and a rounded signed amount in C, with money in positive and money out negative on both.

3. Match on date and amount

Matching on amount alone is where most spreadsheet reconciliations go wrong: two different $50.00 payments hide each other. Match on the amount and a date window, because a card payment can post a day or two after you recorded it.

COUNTIFS with a date window (works in every version)

On the Bank sheet, in D2, count book entries with the same amount within 3 days:

=COUNTIFS(Books!$C:$C,C2,Books!$A:$A,">="&A2-3,Books!$A:$A,"<="&A2+3)

On the Books sheet, in D2, the mirror image:

=COUNTIFS(Bank!$C:$C,C2,Bank!$A:$A,">="&A2-3,Bank!$A:$A,"<="&A2+3)

0: no match. On the Bank sheet, it's on the statement but not in the books. On the Books sheet, it's in the books but not on the statement.
1: one candidate. Usually a match.
2 or more: several candidates with the same amount. Check these by hand (see the next step).

XLOOKUP to pull the matching row

To see which book entry matched, in Microsoft 365 or Excel 2021 and later, put this in E2 on the Bank sheet. It returns the description of the first book entry with the same amount inside the window:

=XLOOKUP(1,(Books!$C$2:$C$2000=C2)*(ABS(Books!$A$2:$A$2000-A2)<=3),Books!$B$2:$B$2000,"Not in books")

On older versions, use INDEX and MATCH with the same test, entered as an array formula (Ctrl+Shift+Enter):

=IFERROR(INDEX(Books!$B$2:$B$2000,MATCH(1,(Books!$C$2:$C$2000=C2)*(ABS(Books!$A$2:$A$2000-A2)<=3),0)),"Not in books")

Checks are the exception to the date window: they can clear weeks after you write them. Match checks by check number instead of date, or widen the window for rows whose description contains the check number.

4. Flag duplicates and repeated amounts

A match count of 2 or more is either a real duplicate or two genuine transactions that happen to share an amount (rent, subscriptions, payroll). To tell them apart:

Flag same-day, same-amount repeats on one side, in F2: =COUNTIFS($A:$A,A2,$C:$C,C2)>1. TRUE on the Books sheet but not on the Bank sheet is the classic sign of an entry that was keyed by hand and added from the bank feed or an import. Our guide to duplicate transactions after an import covers how to remove them safely.
Pair repeated amounts in order with an occurrence key. In G2 on both sheets: =C2&"|"&COUNTIF(C$2:C2,C2). The first 50.00 becomes 50|1, the second 50|2. Then =COUNTIF(Books!$G:$G,G2) on the Bank sheet tells you whether the books have the same number of 50.00 entries as the statement. Sort both sheets by date first so the pairs line up.
Look for reversed signs among the leftovers: =COUNTIFS(Books!$C:$C,-C2,Books!$A:$A,">="&A2-3,Books!$A:$A,"<="&A2+3). A hit means a payment was entered as a deposit, or a refund as a payment.

5. Sum the differences to explain the gap

If the opening balances agree, the whole difference between the two closing balances is made of the unmatched rows. Add them up:

Bank items not in books:   =SUMIFS(Bank!$C:$C,Bank!$D:$D,0)
Book items not on bank:    =SUMIFS(Books!$C:$C,Books!$D:$D,0)
Explained difference:      = (bank items not in books) - (book items not on bank)

The explained difference should equal statement closing balance minus book balance on the same date. If it does, every gap is accounted for: record the missing bank items (fees, interest, unentered payments), leave genuine outstanding checks and deposits in transit uncleared, and fix or delete the rest.

If it doesn't, the leftover usually tells you what to look for:

Divisible by 9: transposed digits (189.34 keyed as 198.34). The two rows won't match on amount, so they're both in your unmatched lists.
Exactly twice one amount: a reversed sign your COUNTIFS counted as unmatched on both sides.
Equal to a matched row: a duplicate that matched twice. That's why the occurrence key in step 4 matters.

Our guide to finding a bank reconciliation discrepancy walks through the seven usual causes in order.

Common pitfalls

Matching on amount only. COUNTIF without a date ignores the second of two identical amounts. Use COUNTIFS with a window, plus the occurrence key for repeats.
A window that's too wide. A 30-day window makes monthly charges match last month's entry. Keep it at a few days and handle checks separately.
Different periods. A books export that runs to today includes transactions after the statement's ending date. They aren't errors; filter them out.
Whole-ledger exports. Rows from other accounts look like book entries missing from the bank. Export the single bank account.
Bank-side balance rows. "Beginning balance" and "Ending balance" lines in a bank download get counted as transactions. Delete them.
Pending transactions. A download taken before month-end can include pending items that later changed or dropped off. Use the posted statement period.
Fixing the spreadsheet instead of the books. The spreadsheet is a worksheet. Make corrections in QuickBooks or Xero, then reconcile there.

A faster way: let the checker do the matching

Utilly's bank reconciliation checker does every step above for you. Upload the bank statement (PDF, CSV, Excel, OFX, QFX or QBO) and the books export from QuickBooks, Xero or any register or ledger CSV. It finds the date, description and amount (or debit and credit) columns, tries your books' signs both ways, matches exact amounts within a date window you choose (3 days by default, checks by number within 60 days), then lists what's on the statement but not in the books, what's in the books but not on the statement, amounts that differ (with a note when the difference divides by 9), reversed signs, possible duplicates and entries dated outside the statement period, and shows how they add up to the whole difference. The summary and the first 3 items of each kind are free with no sign-up; the full list with CSV and PDF reports is 1 credit. Files are processed in memory and deleted after 1 hour.

Compare statement to ledger

The sample reconciles a fictional bank statement against a fictional QuickBooks export with a missing bank fee, an outstanding check, a doubled entry, a transposed amount and a reversed sign. Or start from the bank reconciliation checker page.

FAQ

Related guides

Related tools

Bank reconciliation checker: compare a bank statement to your books and list missing, duplicate and mismatched transactions.
Receipt to CSV: turn PDF or photo receipts and invoices into CSV, Excel, or QuickBooks and Xero bill imports.

How to compare a bank statement to the general ledger in Excel (and a faster way)