Bank Reconciliation in Excel

A bank reconciliation proves that your cash account and your bank statement tell the same story. In a spreadsheet it takes three short formulas, and once the layout is built it works every month. This guide walks through a full example, shows the formulas, and explains the mistakes that make the difference refuse to come out at zero.

What a bank reconciliation does

At the end of each month the bank sends a statement with its record of your account. Your own books also have a cash balance. The two numbers almost never match on the day the statement arrives, and that is normal. They differ because each side has recorded things the other side has not seen yet.

Reconciling means explaining every difference. You adjust each balance for the items it is missing, and you end up with two adjusted balances. When they are equal, you know that cash is right. When they are not, something has been missed, entered twice or entered with the wrong sign.

The work splits into two sides.

• The bank side fixes the bank statement balance for items that are in your books but not yet at the bank.
• The book side fixes your cash balance for items the bank has already recorded but you have not.

The reconciling items and their signs

Almost every reconciliation uses the same short list of items. The hard part is the sign of each one, so it helps to ask one question every time: who has recorded this, and who hasn't?

• Deposits in transit are deposits you have recorded that the bank has not processed yet, often because you made them on the last day of the month. The bank will add them soon, so they increase the bank balance.
• Outstanding checks are checks you have written and recorded that have not cleared the bank. The money will leave the account when they are cashed, so they reduce the bank balance.
• Bank service fees are charges the bank has already taken. Your books don't have them yet, so they reduce the book balance.
• NSF checks (non-sufficient funds) are customer checks you deposited that bounced. You recorded the cash when you deposited it, but the bank took it back, so an NSF check reduces the book balance.
• Interest earned is paid by the bank into the account. Your books don't show it yet, so it increases the book balance.

The two formulas follow directly from that list:

• Adjusted bank balance = bank statement balance + deposits in transit − outstanding checks
• Adjusted book balance = book balance − service fees − NSF checks + interest earned

Worked example: Pine Street Dental, October 2026

Pine Street Dental has received its October bank statement. The statement and the cash account give these figures, laid out in a sheet with labels in column A and amounts in column B.

Before any formulas, the bank says $17,858.08 and the books say $15,929.77. That is a gap of $1,928.31. Your job is to explain it.

Step 1: the adjusted bank balance

Start from the statement balance, add the deposit the bank hasn't recorded, and subtract the checks that haven't cleared. In B6:

=B3+B4-B5

That gives $17,858.08 + $1,479.28 − $3,837.39 = $15,499.97.

Step 2: the adjusted book balance

Start from the book balance, take off the fee and the bounced check, and add the interest. In B12:

=B8-B9-B10+B11

That gives $15,929.77 − $13.61 − $453.45 + $37.26 = $15,499.97.

Step 3: the difference

In B13, subtract one adjusted balance from the other:

=B6-B12

Both sides are $15,499.97, so the difference is zero and the reconciliation is done. Use cell references rather than typed numbers in every formula. If next month's figures go into the same cells, the whole reconciliation recalculates on its own.

Finding outstanding checks and deposits in transit with SUMIFS

In real work, nobody hands you the deposits-in-transit total. You have a check register and a deposit list, and you mark each item as it appears on the statement. The spreadsheet should then total the items that are still waiting.

The December reconciliation for the same practice has a check list in A15:D25 (check number, date written, amount, and "Cleared bank?") and a deposit list in F15:H21 (date, amount, and "On bank statement?"). An item is a reconciling item when its status is No.

To total only the rows that meet a condition, use SUMIFS. Deposits in transit, in B4:

=SUMIFS(G16:G21,H16:H21,"No")

Outstanding checks, in B5:

=SUMIFS(C16:C25,D16:D25,"No")

The first argument is the range to add, and the next pair is the range to test and the value it must equal. For December, two deposits are marked No, so B4 is $5,700.81. Four checks are marked No, so B5 is $5,604.50. With the statement balance of $50,860.90, the adjusted bank balance in B6 is $50,957.21, which matches the adjusted book balance of $50,957.21 and leaves a difference of zero.

The advantage is that the totals follow the lists. If a check clears and you change its status to Yes, B5 drops by that amount and the reconciliation updates itself. SUMIF (without the S) works here too, with its arguments in a different order. Either way, you are telling the sheet which amounts to add, not adding them by hand.

The third exercise in this lesson puts the two parts together: the lists for October at Maple Lane Catering, the SUMIFS totals, both adjusted balances and the difference, all on one sheet.

Common mistakes

These are the mistakes the exercises check for. Each one produces a difference that won't come out at zero, and the size of the difference is often a clue to which one you made.

Reversing deposits in transit and outstanding checks

It is tempting to think "a deposit I haven't seen on the statement should come off." It's the other way round. The bank hasn't recorded either item yet, and you are bringing the bank's figure up to date. A deposit on its way will raise the bank balance; a check on its way out will lower it. If Pine Street Dental's B6 used =B3-B4+B5, it would show $20,216.19 and the difference would be $4,716.22, which is twice the net of the two items.

Adding the bank charges to the book balance

The service fee and the NSF check are money that has already left the account. They make the book balance smaller, not larger. Only interest earned increases it. Adding all three items gives Pine Street Dental an adjusted book balance of $16,434.09 and a difference of −$934.12. Ask yourself whether the company has more or less cash because of each item.

Totaling the checks that have cleared

The list asks "Cleared bank?", and outstanding checks are the ones answering No. Using "Yes" as the criterion adds the checks the bank has already paid. Those are already inside the statement balance, so subtracting them again counts them twice. In the December example, this makes B5 $7,679.46 instead of $5,604.50 and leaves a difference of −$2,074.96.

Totaling every check

A plain SUM of the whole amount column subtracts every check written in the month, cleared or not. For December that is $13,283.96, which drives the difference to −$7,679.46. That number is exactly the total of the cleared checks, which tells you what went wrong. Only the uncleared checks are outstanding.

How to check your work

• The difference must be zero. If it isn't, look at its size. A difference equal to twice one item usually means that item has the wrong sign.
• Every book-side item needs a journal entry. The fee, the NSF check and the interest are not in the books yet. After the reconciliation you record them, and the new cash balance should equal the adjusted book balance. See bank reconciliation journal entries for how to record each one, using the same Pine Street Dental figures.
• Bank-side items need no entry. Deposits in transit and outstanding checks are timing differences. The bank will catch up on its own next month.
• Look at last month. An outstanding check from last month that still hasn't cleared deserves a follow-up. It may be lost or never cashed.

Bank reconciliation and MO-220 practice

Bank reconciliations are part of the MO-220 objective "Perform routine accounting activities", which covers adjusted cash balances and using functions to identify outstanding checks and deposits in transit. MO-220 task instructions describe the purpose rather than naming the function. You will read something like "total the checks that have not cleared", not "use SUMIFS". That is why these exercises are worded the same way: the skill is knowing which function fits the job.

Accounting Exercises is not affiliated with Microsoft or Certiport. The exercises are original MO-220 practice built from the published skill areas. You work in a spreadsheet in your browser, using the same formulas you would write in Microsoft® Excel® workbooks, and each answer is checked by running your formulas.

If you are preparing for the exam or an accounting Excel test, the MO-220 practice page lists every topic. A closely related routine task is the accounts receivable aging report, which uses the same conditional totals.