Outstanding checks and deposits in transit in Excel

Before you can reconcile a bank account, you need two numbers: the checks the bank hasn't paid yet and the deposits it hasn't recorded yet. Nobody hands you those totals. You find them by comparing your own list of checks and deposits with the bank's list. This guide shows how to make a spreadsheet do that comparison for you, one row at a time, and then carry the totals into the adjusted bank balance.

What you are looking for

An outstanding check is a check you have written and recorded in your books that the bank has not paid by the statement date. A deposit in transit is a deposit you have recorded that has not reached the bank statement yet. Both are timing differences. The bank will catch up next month, so neither needs a journal entry. They only change the bank side of the reconciliation.

The work is a matching exercise. You have two lists for the month:

• Your books: every check written and every deposit made, from the check register or cash account.
• The bank statement: every check the bank paid and every deposit it credited.

Any item on your list that is missing from the bank's list is a reconciling item. The page on bank reconciliation in Excel explains the whole reconciliation, including the book side. This page zooms in on the step before it: deciding which items are outstanding.

The matching idea in one formula

To ask "does this check number appear in the bank's list?", count how many times it appears there. COUNTIF(range, value) returns that count. A count of 1 or more means the bank paid the check. A count of 0 means it is still outstanding. Wrap the count in IF to turn it into a label:

=IF(COUNTIF(bank list, this check)>0,"Cleared","Outstanding")

Once every row has a label, a conditional sum adds up the amounts on the rows labeled Outstanding. That is the whole method: label each row, then total by label. The same two steps work for deposits.

Worked example: Pine Street Dental, November 2026

Pine Street Dental wrote ten checks in November, numbered 3308 to 3317. The bank statement lists the checks it paid. The sheet puts the book list on the left and the bank list on the right.

Notice two things before writing anything. The bank's list is in a different order from yours, because the bank pays checks when they are presented, not in number order. And the bank paid check 3305, which is not on November's list at all.

Step 1: label each check

In C4, count how many times check 3308 appears in the bank's list, and label the result:

=IF(COUNTIF($E$4:$E$12,A4)>0,"Cleared","Outstanding")

Check 3308 is in E10, so the count is 1 and C4 shows Cleared. Fill the formula down to C13. The bank list is locked with dollar signs so every row searches the same range, while A4 stays relative and becomes A5, A6 and so on. The range runs a little past the last entry; blank cells never match a check number, so they do no harm. See absolute cell references in Excel if the dollar signs are new to you.

The result: checks 3308, 3309, 3310, 3311, 3312 and 3317 are Cleared. Checks 3313, 3314, 3315 and 3316 are Outstanding.

Step 2: total the outstanding checks

In I1, add the amounts in column B only where column C says Outstanding:

=SUMIF(C4:C13,"Outstanding",B4:B13)

That is $253.43 + $2,883.59 + $750.19 + $3,256.60 = $7,143.81. For comparison, all ten checks total $17,900.89 and the six cleared ones total $10,757.08. Those two figures are useful when you check your work.

What about check 3305?

Check 3305 was written before November and outstanding at the end of last month. It is not on November's book list, so the formula never looks it up and it doesn't affect this month's total. That is correct; tick it off last month's outstanding list instead.

Deposits in transit: choosing what to match on

Deposits need one extra thought. They rarely have a number, and the dates won't line up. Harbor Bakery recorded eight deposits in May 2026, totaling $50,726.61. The bank credits each one a day or two after the book date: the deposit Harbor recorded on 5/3/2026 shows on the statement on 5/5/2026, and so on.

Matching on the date fails here. What does match is the amount, and in this exercise every deposit amount is different, so the amount works as the unique key. With the book amounts in B4:B11 and the bank's amounts in F4:F10, C4 is:

=IF(COUNTIF($F$4:$F$10,B4)>0,"Cleared","In transit")

Seven deposits are found in the bank's list. Only the last one, $8,907.99 recorded on 5/31/2026, is missing, which is typical: a deposit made on the last day of the month often reaches the bank in the next one. The total of deposits in transit in I1 is =SUMIF(C4:C11,"In transit",B4:B11), which gives $8,907.99.

The habit to build is the question itself: which value is the same in both lists and different for every item?

Putting it together: the adjusted bank balance

The lesson's third exercise does both matches on one sheet for Bluebird Veterinary's November 2026 statement. Book checks sit in A:C, book deposits in E:G, the bank's paid checks in I:J and the bank's deposits in L:M. The same two formulas label every row, and three cells finish the job:

The deposit of 11/30/2026 is the one in transit. Checks 4144 to 4147 are outstanding: $297.44 + $1,461.65 + $306.21 + $2,563.36 = $4,628.66. The bank list also includes check 4132, an older check that cleared this month, and as with Pine Street it plays no part. The adjusted bank balance of $41,748.45 is the figure you would then compare with the adjusted book balance.

Common mistakes

These are the mistakes the exercises look for. Each one gives a total you can recognize once you know the cause.

Swapping Cleared and Outstanding

A count above zero means the check was found on the statement, so it cleared. If the labels in the IF are the wrong way round, every paid check is called Outstanding. For Pine Street Dental, I1 would show $10,757.08, the total of the cleared checks, instead of $7,143.81.

Searching your own list

If the COUNTIF points at column A, the book's own check list, every check finds itself. Every row shows Cleared and the outstanding total is $0.00. A result of zero outstanding checks is possible, but it should make you look twice at which range the formula searches.

Matching deposits on the date

Because the bank's dates trail the book's by a day or two, a date match finds almost nothing. For Harbor Bakery, the only date that lines up is 5/5/2026, and only by chance: the bank's 5/5 entry is actually the 5/3 deposit. Seven deposits are wrongly marked In transit and the total comes out at $43,167.48 instead of $8,907.99. Match on the amount.

Totaling every deposit

A plain SUM of the deposit column ignores the labels you just built. Harbor's I1 would show $50,726.61, the whole month's deposits. Only the deposits labeled In transit are reconciling items.

Reversing the adjustments

Deposits in transit are money on its way into the bank, so they are added. Outstanding checks are money on its way out, so they are subtracted. Writing =B17-B18+B19 gives Bluebird an adjusted bank balance of $42,543.23 instead of $41,748.45.

Totaling the cleared items

If B18 and B19 add up the Cleared rows, the reconciliation counts items the bank has already processed. They are inside the statement balance already. For Bluebird, B18 becomes $22,943.24, B19 becomes $9,469.30 and the adjusted balance jumps to $55,619.78.

How to check your work

• Cleared plus outstanding equals the total. For Pine Street, $10,757.08 + $7,143.81 = $17,900.89, the total of all ten checks. If the two parts don't add up to the whole, a row has no label or the wrong one.
• Count the labels. Pine Street's bank list has seven checks, but one of them (3305) is from last month, so six of November's checks should be Cleared.
• Highlight the reconciling items. Conditional formatting that shades every Outstanding or In transit row makes the list easy to review. The exercises grade the values, not the shading, but it is a useful habit.

If you want to see the same reconciliation worked by hand without a spreadsheet, read how to reconcile a bank statement. The book-side items (fees, NSF checks and interest) need journal entries, covered in bank reconciliation journal entries.

Outstanding items and MO-220 practice

This lesson covers the MO-220 objective "Perform routine accounting activities", under bank reconciliations: using functions to identify outstanding checks and deposits in transit, and working out adjusted cash balances. MO-220 task instructions describe the purpose rather than naming the function. You will read something like "show whether each check has cleared the bank", not "use COUNTIF". The exercises are worded the same way, so the skill you practice is choosing the function.

Accounting Exercises is not affiliated with Microsoft or Certiport. These are original MO-220 practice exercises. 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 against new numbers. The MO-220 practice page lists every topic.