How to Reconcile a Bank Statement in Excel (With Formulas)
“Reconciling a bank statement” can mean two different jobs, and it helps to keep them separate:
- Checking the statement itself. Does your spreadsheet contain every transaction, with the right amounts, so the running balance matches the bank’s balance on every line? This matters most after you’ve converted a PDF statement into Excel.
- Bank reconciliation. Does the statement agree with your own records (your ledger, cash book or accounting software), and can you explain every difference?
Do the first before the second. If a row was dropped or a digit was misread during conversion, you’ll spend an hour hunting for a “reconciliation difference” that is really a data error. This guide covers both jobs with formulas you can type straight into Excel, plus a worked example.
Before you start: get clean data into Excel
You need one row per transaction, real Excel dates (not text) and real numbers. If you’re starting from a PDF, convert it first. How to convert a PDF bank statement to Excel compares the methods, or you can drop the file onto the pdf2statement converter, which runs in your browser and exports straight to Excel or Google Sheets.
Two quick sanity checks:
=ISNUMBER(A2)should return TRUE for a date cell. If it returns FALSE, the date is text and date maths won’t work.=ISNUMBER(C2)should return TRUE for an amount. Amounts imported as text often have a leading space or a trailing “Dr”/“Cr” stuck to them.
Part 1: Check the statement with a running balance
Set up the columns
Lay the sheet out like this, with headers in row 1 and the opening balance in row 2:
| Column | Contents |
|---|---|
| A | Date |
| B | Description |
| C | Debit (money out) |
| D | Credit (money in) |
| E | Balance (as printed on the statement) |
| F | Calc (your calculated balance) |
| G | Diff (statement balance minus calculated) |
The formulas
- F2:
=E2, which starts the calculation from the statement’s opening balance. - F3:
=F2+D3-C3, the previous calculated balance plus money in minus money out. Fill it down. - G3:
=ROUND(E3-F3,2). Fill it down too.
Every value in column G should be 0. The ROUND matters: Excel’s floating-point arithmetic can turn a perfect match into 0.0000000001, which would otherwise show up as a false alarm.
Highlight problem rows automatically
- Select A3:G500 (or as far as your data goes).
- Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Enter
=ABS($G3)>0.005and pick a red fill.
Any row that doesn’t tie out now lights up.
Worked example
Here’s a short statement in which one amount was misread during conversion. The electricity payment was really 96.45, but it came through as 69.45.
| Row | Date | Description | Debit (C) | Credit (D) | Balance (E) | Calc (F) | Diff (G) |
|---|---|---|---|---|---|---|---|
| 2 | 2026-03-01 | Opening balance | 2,500.00 | 2,500.00 | |||
| 3 | 2026-03-04 | Card payment – grocer | 84.20 | 2,415.80 | 2,415.80 | 0.00 | |
| 4 | 2026-03-06 | Salary – ACME Ltd | 3,200.00 | 5,615.80 | 5,615.80 | 0.00 | |
| 5 | 2026-03-09 | Rent payment | 1,450.00 | 4,165.80 | 4,165.80 | 0.00 | |
| 6 | 2026-03-16 | Electricity direct debit | 69.45 | 4,069.35 | 4,096.35 | -27.00 | |
| 7 | 2026-03-23 | Transfer from savings | 500.00 | 4,569.35 | 4,596.35 | -27.00 | |
| 8 | 2026-03-31 | Monthly account fee | 12.00 | 4,557.35 | 4,584.35 | -27.00 |
How to read it:
- The first non-zero Diff marks the problem row. Rows 3–5 tie out, and row 6 is the first that doesn’t.
- The error carries forward. Because the formula is cumulative, rows 7 and 8 inherit the -27.00. That’s useful, because a Diff that stays the same tells you there’s a single error above it, not several.
- The size of the difference is a clue. 27 is divisible by 9, which is the classic sign of transposed digits (96 vs 69).
Correct C6 to 96.45 and every Diff drops to 0.00.
A variant that flags only the bad row
If you’d rather see only the offending row highlighted, calculate each row from the statement’s previous balance instead of your own: F3: =E2+D3-C3. With this version only row 6 shows a difference. The trade-off is that it needs a printed balance on every row.
Statements without a balance on every line
Many statements, including a lot of UK ones, print a balance only after the last transaction of each day. Keep the cumulative formula in column F and compare only where a balance exists:
G3: =IF(E3="","",ROUND(E3-F3,2))
The calculation runs through the blank rows, and the check fires wherever the bank prints a figure.
Credit card statements and signed amounts
- Credit cards: the balance is what you owe, so purchases increase it and payments reduce it. Flip the signs: F3:
=F2+C3-D3. The credit card statement to Excel page covers the layout differences. - One signed Amount column (negative for money out) in column C: F3:
=F2+C3.
Check the opening, closing and totals
Most statements have a summary box. Type the statement’s closing balance into J2, then:
- Closing check:
=ROUND(E2+SUM(D3:D8)-SUM(C3:C8)-J2,2)should return 0. - Total debits:
=SUM(C3:C8)should equal the statement’s total withdrawals. - Total credits:
=SUM(D3:D8)should equal the statement’s total deposits.
In the example, the closing check returns 27.00 and total debits come to 1,615.65 instead of 1,642.65. That’s the same error, confirmed from a different angle. When you’re stitching together several months, also check that each statement’s opening balance equals the previous month’s closing balance. A gap there means a missing statement or page.
pdf2statement does this automatically: every row is checked against the statement’s running balance, the opening and closing balances are verified, and rows that don’t tie out are highlighted in the preview before you export. The formulas above are still worth knowing for files from other sources, or after you’ve edited data by hand.
Part 2: Reconcile the bank statement against your books
Now that the statement data is trustworthy, compare it with your records.
Set up two sheets
- Bank: A = Date, B = Description, C = Amount (signed).
- Ledger: A = Date, B = Payee / memo, C = Amount (signed). Export this from your accounting software or cash book.
Use the same sign convention on both (money in positive, money out negative) and round amounts to 2 decimals with =ROUND(x,2) if they come from calculations. Matching on unrounded numbers is a common source of phantom mismatches.
Find matches with COUNTIFS
In Bank!D2, count ledger entries with the same amount dated within 3 days either side:
=COUNTIFS(Ledger!$C:$C,C2,Ledger!$A:$A,">="&A2-3,Ledger!$A:$A,"<="&A2+3)
- 0: on the statement but not in your books. Typically a bank fee, interest, a direct debit you forgot, or a returned payment.
- 1: a clean match.
- 2 or more: ambiguous, for example several identical amounts in the same week. Check these by hand, or use the occurrence key below.
Put the mirror formula in Ledger!D2 (swap Ledger! for Bank!). Ledger rows that return 0 are your outstanding items. For cheques, widen the window, because a cheque can take weeks to be presented. Matching on cheque number is more reliable if both sides record it.
Pull the matching entry with XLOOKUP
To see which ledger entry matched, in Microsoft 365, Excel 2021 or later:
=XLOOKUP(1,(Ledger!$C$2:$C$500=C2)*(ABS(Ledger!$A$2:$A$500-A2)<=3),Ledger!$B$2:$B$500,"No match")
The two conditions multiply to 1 only where both are true, and XLOOKUP returns the first such row. In older Excel versions, use =INDEX(Ledger!$B$2:$B$500,MATCH(1,(Ledger!$C$2:$C$500=C2)*(ABS(Ledger!$A$2:$A$500-A2)<=3),0)), confirmed with Ctrl+Shift+Enter.
Handle duplicates with an occurrence key
Three 4.50 coffees in one week will each match all three ledger entries. Sort both sheets by date, then build a key that numbers each repeat of an amount:
- Bank!E2:
=C2&"|"&COUNTIF($C$2:C2,C2), filled down. This gives keys like-4.5|1,-4.5|2,-4.5|3. - Ledger!E2: the same formula on the ledger sheet.
- Bank!F2:
=IF(COUNTIF(Ledger!$E:$E,E2)>0,"Matched","Unmatched")
Each bank entry now pairs with exactly one ledger entry.
One-to-many matches
Sometimes one bank line covers several ledger entries: a card processor payout, a deposit of several cheques, or one payment that settles multiple invoices. Add a Group column to the ledger (for example DEP-0316), put the same label next to the bank line, and compare:
=SUMIFS(Ledger!$C:$C,Ledger!$F:$F,G2)
Here G2 on the Bank sheet holds the group label and Ledger column F holds the matching labels. If the sum equals the bank amount, the whole group is matched.
Classify what’s left
Every unmatched item should fall into one of these buckets:
- Deposits in transit: recorded in your books, not yet on the statement.
- Outstanding cheques/checks: written and recorded, not yet cleared.
- Bank-only items: fees, interest, direct debits and returned items. Record these in your books.
- Errors: on either side.
If a difference remains, its size often tells you why:
| Difference pattern | Likely cause |
|---|---|
| Divisible by 9 | Transposed digits (96.45 vs 69.45) |
| Exactly twice one transaction | Entry posted with the wrong sign |
| Exactly one transaction | Missing or duplicated entry |
| 0.01 or similar | Rounding in a calculated amount |
Build the reconciliation statement
Continuing the example: at 31 March the books show 4,999.35. A 750.00 customer cheque was banked on 31 March but hasn’t reached the statement yet, cheque #1043 for 320.00 hasn’t been presented, and the 12.00 account fee hasn’t been recorded in the books.
| Bank side | Amount | Book side | Amount |
|---|---|---|---|
| Balance per bank statement | 4,557.35 | Balance per books | 4,999.35 |
| Add: deposits in transit | 750.00 | Add: interest not yet recorded | 0.00 |
| Less: outstanding cheques (#1043) | (320.00) | Less: bank fee not yet recorded | (12.00) |
| Adjusted bank balance | 4,987.35 | Adjusted book balance | 4,987.35 |
The two adjusted balances agree, so the account is reconciled. Add one final cell, adjusted bank balance minus adjusted book balance, and make sure it reads 0.00. Then post the book-side adjustments (here the 12.00 fee) in your accounting system.
Where Excel fits alongside accounting software
QuickBooks and Xero have their own reconciliation screens, and once transactions are imported it’s usually quicker to reconcile there. See importing a bank statement into QuickBooks and importing a bank statement into Xero. Excel earns its place in catch-up bookkeeping covering many months, in audit and loan preparation, for accounts your software doesn’t cover, and whenever you need to show your working.
Whichever bank you use, whether that’s Chase, HDFC Bank or any other bank, the workflow is the same: convert, prove the running balance, match against the books, and explain what’s left.