How to Reconcile a Bank Statement in Excel (With Formulas)

Updated 3 November 2026

To reconcile a bank statement in Excel, put the statement’s transactions on one sheet and your ledger (cash book or accounting export) on another, prove the statement adds up, then match the two line by line with formulas. Whatever doesn’t match becomes your list of reconciling items. This guide gives you a working formula for each step.

Step 1: Get clean statement data into Excel

A reconciliation is only as good as its data. If your statement is a PDF, convert it with our free bank statement converter and export to Excel. The examples below assume a sheet called Bank laid out the way the converter exports it:

Headers sit in row 1, transactions start in row 2, sorted oldest first. Check that dates are real dates (=ISNUMBER(A2) returns TRUE) and that amounts are numbers rather than text; our guide on converting a bank statement PDF to Excel covers that clean-up.

Finally, type the statement’s opening balance into J1 and its closing balance into J2.

Step 2: Prove the statement adds up

Before comparing anything with your books, make sure the statement data is complete.

Running balance

In G2, build a running balance from the opening balance and fill it down:

=$J$1+SUM($E$2:E2)

The last value should equal the closing balance in J2.

Row-by-row check

A running total tells you something is wrong, but the error carries down every row below it. To pinpoint the exact row, check each line against the previous printed balance. In H2 (the first transaction):

=ROUND($J$1+E2-F2,2)

In H3, filled down:

=ROUND(F2+E3-F3,2)

Zero means the row reconciles; any other value is the size of the discrepancy on that row. ROUND stops tiny floating-point differences from showing up as false alarms.

If your bank prints a balance on only some rows (say, once per day), compare the running total wherever a balance exists instead:

=IF(F2="","",ROUND(G2-F2,2))

This mirrors the balance check the converter runs before export, so if you fixed its flagged rows, this column should already show zeros.

Totals against the statement summary

Put these in spare cells outside column E. The closing balance check should return 0, and the totals should agree with the statement’s summary box if it has one.

Step 3: Summarise by month with SUMIFS

For statements covering several months, a monthly summary shows where a problem sits. On a Summary sheet, put the first day of each month in column A (A2 = 1 Jan 2026, A3 = 1 Feb 2026, and so on). Then use these three formulas in B2, C2 and D2 and fill them down.

Money in (B2):

=SUMIFS(Bank!$E:$E,Bank!$A:$A,">="&A2,Bank!$A:$A,"<="&EOMONTH(A2,0),Bank!$E:$E,">0")

Money out (C2):

=-SUMIFS(Bank!$E:$E,Bank!$A:$A,">="&A2,Bank!$A:$A,"<="&EOMONTH(A2,0),Bank!$E:$E,"<0")

Net movement (D2):

=B2-C2

Build the same summary from your ledger and put the two side by side. The month where the totals differ is the month to investigate.

Step 4: Match statement lines against your ledger

Suppose your ledger export is on a sheet called Ledger, with Date in column A, Description in B and a signed Amount in C, using the same convention as the bank (money into the account positive).

Mismatched sign conventions are the most common reason nothing matches. If your ledger shows payments as positive numbers in a separate column, create a signed Amount column first.

Count matches with COUNTIFS

In Bank!I2, filled down:

=COUNTIFS(Ledger!$A:$A,A2,Ledger!$C:$C,E2)

• 0: not in your ledger yet (bank fees and interest are typical).
• 1: matched.
• 2 or more: a possible duplicate in the ledger, or several identical payments on one day; check these by hand.

Payments often clear a few days after you record them. To allow for that, match on amount within three days either side:

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

Pull the matching ledger entry with XLOOKUP

To see which ledger entry matched, create a key combining date and amount. In Bank!K2 (and the equivalent in Ledger!D2, using C2 instead of E2):

=TEXT(A2,"yyyy-mm-dd")&"|"&TEXT(E2,"0.00")

Then in Bank!L2:

=XLOOKUP(K2,Ledger!$D:$D,Ledger!$B:$B,"Not in ledger")

Without XLOOKUP, INDEX and MATCH do the same job:

=IFERROR(INDEX(Ledger!$B:$B,MATCH(K2,Ledger!$D:$D,0)),"Not in ledger")

Format codes inside TEXT can differ in non-English versions of Excel, so adjust yyyy-mm-dd if the key comes out wrong.

Check the other direction

Matching only from the bank side misses items in your books that haven’t reached the bank yet. In Ledger!E2, filled down:

=COUNTIFS(Bank!$A:$A,A2,Bank!$E:$E,C2)

Ledger entries showing 0 are usually outstanding payments or deposits in transit.

Step 5: Flag the differences

Conditional formatting makes problems jump out. Select your Bank data (for example A2:L500), add a new rule that uses a formula, and enter:

=$I2=0

Pick a fill colour. Add a second rule for the row-by-row balance check:

=AND($H2<>"",$H2<>0)

The option is usually under Home > Conditional Formatting > New Rule, though labels can vary by version. Filtering column I for 0 then gives you a list of unmatched bank items.

Step 6: Prepare the reconciliation summary

Bring it together in a simple layout:

When the two adjusted balances agree, you’re reconciled. Record the missing bank items in your books and carry the outstanding ones forward to next month.

Key takeaways

• Start with clean data: real dates, numeric amounts, oldest first, one row per transaction.
• A row-by-row check such as =ROUND(F2+E3-F3,2) shows exactly where a statement stops adding up.
• SUMIFS by month narrows down where bank and ledger totals diverge.
• COUNTIFS and XLOOKUP (or INDEX and MATCH) match lines in both directions; anything unmatched is a reconciling item.
• Use ROUND and one consistent sign convention to avoid false mismatches.

Importing into accounting software rather than reconciling in Excel? See our guides for QuickBooks and Xero.

Frequently asked questions

Convert a bank statement →