Do a Bank Reconciliation in Excel Step by Step

A bank reconciliation in Excel checks that your cash book agrees with the bank statement, and explains every difference. You match the two lists with COUNTIF and XLOOKUP, list what appears on only one side, and use SUMIF to bring both balances to the same number. This guide is for bookkeepers, small business owners and accounting candidates, and it builds the whole reconciliation on a small example you can copy.

What is a bank reconciliation?#

A bank reconciliation compares your own record of cash (the cash book) with the bank statement for the same period. The two balances are rarely equal on the day, and the reconciliation explains why.

Differences fall into two groups:

• Timing items are in your books but not yet at the bank: deposits in transit (money you paid in that the bank hasn't shown yet) and outstanding cheques (payments you wrote that the bank hasn't paid out yet). These adjust the bank side.
• Bank-only items are on the statement but not yet in your books: bank fees, interest, direct debits. These adjust the book side.

When both adjusted balances match, the books are reconciled. Reconciliations like this are a core task in the Excel skills for bookkeepers guide.

Set up your two lists#

Put each list on its own sheet, with a shared reference column. Cheque numbers, deposit slip numbers or invoice numbers all work. Use negative numbers for money out and positive numbers for money in.

Book sheet (your cash book), opening balance 5,000:

Book balance: 5,000 + 2,500 − 800 − 450 + 1,200 − 300 = 7,150.

Bank sheet (the statement export), same opening balance:

Bank balance: 5,000 + 2,500 − 800 − 15 = 6,685. The two balances differ by 465, and the next steps explain every bit of it.

Match transactions with COUNTIF#

COUNTIF tells you whether a reference from one list appears in the other. On the Book sheet, add a Cleared column. In D2, type:

=IF(COUNTIF(Bank!$A$2:$A$50,A2)>0,"Yes","No")

Fill it down to D6. COUNTIF counts how many times the reference in A2 appears in the bank list, and IF turns that count into Yes or No. The dollar signs lock the bank range so it doesn't move as you fill. The result: DEP-01 and CHQ-101 are Yes; CHQ-102, DEP-02 and CHQ-103 are No.

This is the same technique as checking whether a value exists in another list. The COUNTIF function guide covers the syntax.

Check amounts with XLOOKUP#

A matching reference doesn't prove the amount is right. XLOOKUP brings the bank amount next to the book amount, so you can compare them. In E2 on the Book sheet:

=XLOOKUP(A2,Bank!$A$2:$A$50,Bank!$C$2:$C$50,"Not in bank")

Then in F2, flag any difference:

=IF(ISNUMBER(E2),C2-E2,"")

Every matched row should show 0 in column F. A non-zero value means the amount was entered differently on one side. XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, use =IFERROR(VLOOKUP(A2,Bank!$A$2:$C$50,3,FALSE),"Not in bank"). The XLOOKUP function guide explains each argument.

Find items that are only in the bank#

Now run the check the other way, to catch fees, interest and direct debits you haven't recorded. On the Bank sheet, in D2:

=IF(COUNTIF(Book!$A$2:$A$50,A2)=0,"Not in book","")

Fill down. Only the FEE row shows Not in book. Checking both directions is the key idea; the guide to comparing two lists shows other ways to do it, including conditional formatting.

Calculate the adjusted balances with SUMIF#

SUMIF adds only the rows that meet a condition, so it can total the uncleared items and the bank-only items in one step each.

Adjusted bank balance = bank balance + every uncleared book item:

=6685+SUMIF(Book!D2:D6,"No",Book!C2:C6)

The uncleared items are −450 + 1,200 − 300 = 450, so the result is 7,135.

Adjusted book balance = book balance + every bank-only item:

=7150+SUMIF(Bank!D2:D4,"Not in book",Bank!C2:C4)

The only bank-only item is the −15 fee, so the result is also 7,135. In a real file, point to the balance cells instead of typing 6685 and 7150.

Finally, check the difference with ROUND:

=ROUND(H2-H3,2)

If H2 and H3 hold the two adjusted balances, the result is 0: reconciled. ROUND matters because money with decimals can leave a tiny difference such as 0.0000000001, which makes =H2=H3 return FALSE. The guide to floating-point rounding errors explains why. After reconciling, record the 15 fee in your cash book.

Common reconciliation problems#

If the difference isn't zero, work through these in order:

1. No shared reference. Bank exports often lack your invoice numbers. Match on amount instead with =COUNTIF(Bank!$C$2:$C$50,C2), then check by date.
2. Repeated amounts. Two payments of 450 both match one bank line of 450. When matching on amount, compare how often a value appears in each list; see finding duplicates in two columns.
3. Sign errors. A payment entered as +450 instead of −450 creates a difference of 900, twice the amount.
4. Swapped digits. If the difference divides evenly by 9, two digits may be swapped, such as 450 typed as 540.
5. Text references. "CHQ-102 " with a trailing space won't match "CHQ-102". Clean the column with TRIM first.
6. Ranges that stop too early. New rows below row 50 are ignored. Use an Excel table so ranges grow.

Practise it#

A bank reconciliation is COUNTIF, XLOOKUP and SUMIF working together. Try the adjusted-balance exercise on this page, then practise COUNTIF in a live spreadsheet, match records in the XLOOKUP lesson and total by condition in the SUMIF lesson. Each answer is checked instantly. The SUMIF function guide is a quick reference, and the finance Excel mock test lets you try similar tasks under time pressure.