Home › Guides › Bank reconciliation in Excel

Bank reconciliation Excel practice: steps, formulas and a worked example

A bank reconciliation proves that the cash balance in your books and the balance on the bank statement describe the same money. This guide walks through the steps, shows how to do a bank reconciliation in Excel with formulas that match items for you, and finishes with a small worked example you can rebuild yourself. Every company and figure here is fictional.

What a bank reconciliation is

The bank statement and the cash account in your general ledger each have a closing balance, and the two are almost never equal. That is normal: the bank and the business record the same transactions at different times. A reconciliation lists every reason for the difference until nothing is left unexplained.

The result is two adjusted balances, one for the bank and one for the books. When they are equal, the account is reconciled. If not, the gap tells you how much is still missing.

The reconciling items, side by side

Each difference belongs to one side. Getting the side right is most of the skill.

Adjust the bank balance for:

• Deposits in transit. Money you recorded that the bank had not credited by the statement date, such as a deposit made on the last afternoon of the month. Add them to the bank balance.
• Outstanding checks. Checks you wrote and recorded that the payee has not cashed yet, so they have not cleared the bank. Subtract them from the bank balance.
• Bank errors. Such as another customer's check charged to your account. Correct it on the bank side and tell the bank.

Adjust the book balance for:

• Bank fees and service charges. You only learn about them from the statement. Subtract them.
• Interest earned. Credited by the bank, not yet in your books. Add it.
• Returned (NSF) checks. A customer's check that bounced. Subtract it and put the amount back into receivables.
• Direct credits and debits. Transfers, card settlements or automatic payments that appear on the statement first.
• Book errors. For example a check for 486.00 recorded as 468.00. Correct the difference.

Rule of thumb: what the bank has not recorded yet goes on the bank side; what the business has not recorded yet goes on the book side. Every book-side item needs a journal entry afterwards; bank-side items do not.

How to do a bank reconciliation in Excel, step by step

1. Put the two lists on separate sheets. Paste the cash ledger on a sheet called Books and the statement lines on a sheet called Bank, with matching columns where you can: date, check number, description, amount.
2. Mark what cleared. Next to each book item, use a formula to look for it on the bank sheet. Items you cannot find are outstanding checks or deposits in transit.
3. Mark what the books are missing. Do the reverse on the bank sheet to find fees, interest, returned checks and transfers.
4. Compare amounts, not just check numbers. A matched check with two different amounts is an error on one side.
5. Total each category with SUMIF so the totals update as you change a status.
6. Build the two adjusted balances and a check cell that tells you whether they agree.

Bank reconciliation Excel formulas that do the matching

These assume check numbers in column B on both sheets, amounts in Books column D and Bank column C, and data in rows 2 to 40. The $ signs keep the lookup range fixed when you fill down.

Find each book item on the statement with COUNTIF

COUNTIF counts how many times the check number appears on the bank sheet. Zero means the check has not cleared. If COUNTIF returns 2, the bank may have paid the same check twice, which is worth a phone call.

The same test with MATCH or XLOOKUP

MATCH with 0 finds an exact match or returns #N/A, which ISNUMBER turns into FALSE. Many people search for a bank reconciliation in Excel with VLOOKUP; XLOOKUP (Excel 2021 and Microsoft 365) does the same job and can also bring back the cleared amount in one step:

In older versions, =IFERROR(VLOOKUP(B2,Bank!$B$2:$C$40,2,FALSE),"Not on statement") gives the same result. Always use FALSE for an exact match.

Flag amount differences

If the cleared amount from the bank is in column F, this shows any difference between the two records:

Any result other than 0 is an error on one side. ROUND stops tiny floating-point remainders from showing up as false differences.

Total each category with SUMIF

Use one SUMIF for outstanding checks, one for deposits in transit and one for each type of bank-only item. Correcting one status then updates the whole reconciliation.

The adjusted-balance check

With the adjusted bank balance in H6 and the adjusted book balance in H12:

This cell answers a reviewer's first question: does it balance, and if not, by how much?

Try one now: the book side starts with a running balance

Before you can reconcile, the book balance itself has to be right. This free task from lesson 3, running balances with debits and credits, asks for the running balance of a bank account in the books. Click into the formula bar, type your formula and press Enter. The grid fills it down to E8 and checks every cell.

Write =E2+D3-C3 and the checker says debit and credit are the wrong way round: a bank account is an asset, so debits increase it. The full lesson continues with a credit card liability and an overdrawn flag: open lesson 3 (free).

Worked example: Juniper Row Florists, October

Juniper Row Florists is a fictional shop. On 31 October its cash ledger shows 14,116.80 and its bank statement shows 14,236.15. Comparing the two lists turns up these items:

• The deposit of 1,950.00 made on 31 October is not on the statement: a deposit in transit.
• Checks 1042 (860.00), 1045 (1,215.40) and 1047 (342.75) have not cleared: outstanding checks totalling 2,418.15.
• The statement shows a monthly service fee of 25.00 and interest of 4.20.
• A customer's check for 310.00 came back unpaid (NSF).
• Check 1039 cleared the bank at 486.00 but was entered in the books as 468.00, so the books are 18.00 too high.

In Excel, the bank side is =H1+H2-H3: statement balance, plus the SUMIF of deposits in transit, minus the SUMIF of outstanding checks. Afterwards the shop records journal entries for the four book-side items. Nothing is booked for the bank-side items.

Notice the 18.00 error. The difference between 486 and 468 is divisible by 9, which is typical of two transposed digits. When a reconciliation is out by an amount that divides evenly by 9, look for swapped digits first.

Common mistakes

• Putting an item on the wrong side. Bank fees go on the book side, not the bank side. Ask who has not recorded it yet.
• Wrong signs. Outstanding checks are subtracted from the bank balance; deposits in transit are added. Reversing either one doubles the difference instead of removing it. If you are out by exactly twice an item, that item probably has the wrong sign.
• Unlocked lookup ranges. COUNTIF(Bank!B2:B40,B2) without $ slides down as you fill, and items near the bottom are wrongly reported as outstanding.
• Approximate matches. VLOOKUP without FALSE can match the wrong check.
• Text versus numbers. Check numbers imported as text ("1042") do not match numbers (1042). Convert one side with VALUE or TEXT before matching.
• Forcing the balance. Typing a "plug" figure to make the totals agree hides the real problem. Leave the difference visible until it is explained.
• Forgetting last month. A check still outstanding after several months needs following up.

Where to practice next

The matching formulas above build on a few basics. The free lessons cover them, and the bank reconciliation lesson puts them together:

1. Lesson 1: SUM, AVERAGE and MAX on a cash ledger (free).
2. Lesson 2: absolute references (free). The $ habit every lookup range above depends on.
3. Lesson 3: running balances with debits and credits (free).
4. Lesson 4: SUMIF trial balance (free). The same SUMIF pattern used for the reconciliation totals.
5. Lesson 7: bank reconciliation (part of the $9 unlock). Mark checks as outstanding or cleared with COUNTIF, total them with SUMIF and calculate the adjusted bank balance. The explanation and worked example are free to read; the graded exercises are part of the paid unlock.

Preparing for an interview rather than a month-end close? The guide to the Excel test for an accounting interview has a one-week practice plan.

Practice workbook

When lessons 1 to 4 feel easy, lessons 5 to 14 add lookups, AR aging, bank reconciliation, depreciation, SUMIFS, prepaids, INDEX/MATCH, cleaning exports, loan payments and a timed mock test. Their 36 interactive exercises unlock with one $9 Gumroad purchase, All 14 lessons + the practice workbook (license key), which also includes the Excel practice workbook (.xlsx) with a practice sheet and an answer-key sheet for every lesson.

Unlock lessons 5 to 14 + the workbook, $9

Not ready to pay? The free sample workbook is pay what you want, from $0: 3 lessons (1, 2 and 4), 9 exercises, answer keys with formulas — also a free download on itch.io. What's included.

Questions

Where can I find bank reconciliation practice problems with solutions?

The worked example on this page is one, with the solution shown. Lesson 7 on this site has three graded bank reconciliation exercises, and the practice workbook includes an answer-key sheet with the working formula for each. All data is fictional.

What is the formula for a bank reconciliation?

Adjusted bank balance = statement balance + deposits in transit − outstanding checks (± bank errors). Adjusted book balance = ledger balance + interest and other bank credits − fees, NSF checks and other bank debits (± book errors). The two must be equal.

Which Excel function is best for matching bank and book items?

For a yes/no test, COUNTIF or MATCH is simplest. When you also need the amount from the statement, use XLOOKUP, or VLOOKUP with FALSE in older versions of Excel. Use SUMIF to total each category.

Is the bank reconciliation lesson free?

Lessons 1 to 4 are free. Lesson 7, bank reconciliation, is part of a one-time $9 unlock; its explanation and worked example are free to read, and its interactive exercises unlock with the purchase.

Do I need to sign up?

No. There is no account on this site, and lessons 1 to 4 need nothing at all. Progress is saved only in your browser. If you buy the $9 unlock, Gumroad asks for an email address at checkout and sends the license key there.

Who wrote this guide?

This guide, the lessons and the exercises were created with AI assistance and checked by automated tests that confirm every reference answer passes and listed wrong answers are caught. See About. The site is not affiliated with Microsoft. Excel is a trademark of Microsoft Corporation.

Juniper Row Florists and all other company names, accounts and figures on this page are fictional sample data. This guide was written with AI assistance. It is general practice material, not accounting advice. Not affiliated with or endorsed by Microsoft or any bank.