How to do a bank reconciliation in Excel

What does it mean to reconcile a bank statement?

Your books record a transaction when it happens: you write a check, you invoice and receive a payment, you pay a supplier. The bank records it when the money actually moves. At any month end the two balances differ, and most of the difference is just timing.

Reconciling means explaining every cent of that difference. What's left after timing differences should be nothing — if it isn't, there is an error, a missed entry or a transaction you didn't make. That's why accountants reconcile every bank and credit card account before closing a month or preparing a tax return.

Bank reconciliation statement example

A small business closes March. The statement shows a closing balance of 12,480.00; the books show 12,866.50. Here is how the difference is explained:

Timing differences adjust the bank side; items the bank recorded but you didn't adjust the book side. Only the book-side items need journal entries.

Bank reconciliation Excel template: the layout

You don't need a downloaded template — three sheets and about ten formulas do the job, and you'll understand every cell. Set it up once and copy the file each month.

Dates must be real date cells and amounts real numbers. If a pasted column is left-aligned and SUM returns 0, it's text — convert it before matching.

Bank reconciliation Excel formulas

The formulas below follow the layout above: dates in column A, amounts in column D on Books and column C on Statement, the Match flag in column E. Name the key cells Opening, Closing, AdjustedBank and AdjustedBook (Formulas → Define Name) so the formulas stay readable.

• Statement self-check: =ROUND(Opening + SUM(Statement!C:C) - Closing, 2) must return 0. If it doesn't, a transaction is missing or mistyped and nothing below will reconcile.
• Match flag in Books: =IF(COUNTIFS(Statement!C:C, D2, Statement!A:A, ">="&A2-3, Statement!A:A, "<="&A2+14)>0, "Matched", "Open") finds the same amount on the statement within a date window.
• Match flag in Statement: the same formula pointed the other way, so bank-only items show as Open.
• Deposits in transit: =SUMIFS(Books!D:D, Books!E:E, "Open", Books!D:D, ">0")
• Outstanding checks and payments: =SUMIFS(Books!D:D, Books!E:E, "Open", Books!D:D, "<0") — a negative number.
• Adjusted bank balance: =Closing + deposits in transit + outstanding checks (the checks total is already negative).
• Difference: =ROUND(AdjustedBank - AdjustedBook, 2), with conditional formatting that turns the cell red when it isn't 0.

COUNTIFS can't tell two payments of the same amount apart. When an amount appears more than once in a month, check those rows by hand or compare the count on each side.

What causes differences between the bank and the books?

Almost every difference falls into one of these groups. The first two are timing differences that clear by themselves next month; the rest need an entry in your books or a correction.

• Deposits in transit: money you recorded that the bank credits a day or two later.
• Outstanding checks: checks you wrote that haven't been cashed yet.
• Bank fees, interest and charges that you only learn about from the statement.
• Direct debits, card payments and transfers that went through without an entry in your books.
• Returned (NSF) checks from customers, which the bank reverses.
• Errors: a transposed number, an entry recorded twice or with the wrong sign.

When a difference won't go away, its size is a clue. A difference divisible by 9 often means transposed digits (54.00 entered as 45.00). A difference equal to twice a transaction usually means a sign error. A difference equal to one transaction means it's missing on one side or recorded twice.

A statement balance check is not a reconciliation — you need both

A statement balance check confirms the transaction list is complete: opening balance plus money in, minus money out, equals the closing balance, and the running balance agrees on every row. A bank reconciliation compares that complete list with your books. If the first step is wrong — a line skipped at a page break when copying from a PDF — you'll chase a difference that isn't in your books at all.

Bank Statement Categorizer does the first step automatically. Every statement is checked against its own balances, and rows that don't add up are flagged with their page number, so you know the Statement sheet is complete before you start. The reconciliation against your books is still yours (or your accountant's) to do.

Reconciling several months or accounts at once

Catch-up work — a client who hasn't reconciled all year, or a new set of books — is easier when every month is in one workbook. Convert all the statements together and export them as one Excel file, optionally limited to a date range, then reconcile month by month and carry each month's outstanding items into the next.

If the transactions also need categories for the books, sort that out at the same time: categorize the bank transactions before you post the bank-only items, and keep a consistent list of business expense categories. Bookkeepers doing this for many clients will find the workflow in bank statement conversion for accountants.

Questions