How to Do a Bank Reconciliation in Excel (Step by Step)

Short answer: Put the bank statement transactions on one tab, your ledger transactions on another, and a two column reconciliation summary on a third. Match rows with MATCH or XLOOKUP, flag what stays unmatched, then add deposits in transit and subtract outstanding checks on the bank side, and post fees, interest, and NSF checks on the book side. When the adjusted bank balance equals the adjusted book balance, you are reconciled. Only the book side items become journal entries.

Reconciling in Excel gets a bad reputation it mostly does not deserve. The spreadsheet is fine. What breaks is the data going into it, because a bank statement arrives as a PDF and somebody retypes it. Gartner surveyed 497 controllership professionals in 2023 and found 18 percent make financial errors at least daily, and 59 percent make several a month. A reconciliation is supposed to catch errors, not manufacture them.

So the first step below is not a formula. It is getting trustworthy rows.

Step 1: Get the statement into rows

You need one transaction per row, with the date, description, check number, amount, and running balance each in their own column. If your bank exports CSV for the period you need, use it. Many do not: the download window is often limited to the last 90 days to 18 months, business accounts frequently export nothing useful, and a closed account exports nothing at all. In those cases the PDF statement is the record.

Rather than retype it, convert the PDF bank statement to Excel and paste the result straight in. The converter keeps the check numbers and the running balance that copy and paste destroys, and the running balance matters more than people expect: it is your independent proof that no row went missing in the middle of a 200 line statement.

Step 2: Build the three tabs

Use three tabs, not one crowded sheet.

• Bank. Every transaction from the statement.
• Books. Every transaction from your ledger for the same period.
• Summary. The two column reconciliation itself.

On both transaction tabs, use columns for date, description, check or reference number, money in, money out, and a cleared flag. Label the amount columns "money in" and "money out" rather than debit and credit. A bank statement labels its columns from the bank's point of view, so your deposit prints as a credit. Copy those headers into a book side sheet and every sign is backwards. This single labeling choice prevents an entire category of reconciliation error.

The full column list and the reconciliation summary layout are laid out on our bank reconciliation template in Excel page, which also has the summary block you can copy.

Step 3: Match the rows with formulas

Match on check number first, because it is unique. Fall back to date plus amount only when there is no reference number.

Flag each book row as matched or unmatched against the bank tab:

=IF(ISNUMBER(MATCH(A2, Bank_Ref, 0)), "Matched", "Unmatched")

Pull the bank amount alongside the book amount so you can see any difference:

=XLOOKUP(A2, Bank_Ref, Bank_Amount, "Not found")

Total only the cleared items:

=SUMIF(StatusColumn, "Cleared", AmountColumn)

Then highlight the unmatched rows so they are impossible to miss. Select the range, choose Conditional Formatting, New Rule, "Use a formula to determine which cells to format", and enter a rule such as =ISNA(MATCH($C2, Bank_Ref, 0)) with a red fill. Whatever lights up red is your list of reconciling items. That list is the entire output of the matching step.

Step 4: Complete the two column summary

This is the part most people get subtly wrong. The reconciliation has two columns that are calculated independently and must land on the same number.

On the bank side, start with the balance printed on the bank statement. Add deposits in transit, which are receipts you have booked that the bank has not posted yet. Subtract outstanding checks, which are checks you wrote and recorded that have not cleared. Adjust for bank errors, which are rare.

On the book side, start with the cash balance in your general ledger. Add interest earned and any notes or receivables the bank collected on your behalf. Subtract bank service charges, monthly fees, and NSF returned checks. Adjust for book errors, such as a transposed amount you entered.

The rule that keeps this straight is to put the item where it is not yet recorded. A deposit sitting in your books but not at the bank goes on the bank side. A fee sitting at the bank but not in your books goes on the book side.

Which reconciling items become journal entries?

Only the book side ones. Service charges, interest earned, NSF checks, notes collected, and book errors all get posted to the ledger, because your books genuinely do not know about them yet. Deposits in transit and outstanding checks never get a journal entry. They are timing differences that correct themselves the moment the bank posts them. Journalizing them double counts cash, and it is the most common mistake on a hand built reconciliation.

Why doesn't my bank reconciliation balance?

Work the difference itself, because the number usually tells you what happened.

If the difference divides evenly by 9, suspect a transposition error, where two adjacent digits got reversed. If 1,593 was typed as 1,539, the difference is 54, and 54 divided by 9 is 6. This is a real property of transpositions and a genuinely useful diagnostic, though it is an indicator rather than proof: some unrelated differences are also divisible by 9.

If the difference is exactly twice an amount you can find on the statement, a deposit was recorded as a withdrawal or vice versa. Halve the difference and go looking for that number.

If the difference is a small round number, it is almost always a bank fee or an NSF charge that never made it into the books.

Beyond that, the usual suspects are duplicate entries, often from importing the same CSV twice, and stale checks that have been outstanding for months and should be investigated or voided. If a charge on the statement is one nobody recognizes, pull the supporting document before you code it. Matching a line item back to the receipt behind it is usually faster than arguing about what the merchant descriptor meant.

How often should you do a bank reconciliation?

At least monthly, on the bank statement cycle. Monthly reconciliation is the baseline internal control that auditors expect, and it keeps the investigation small enough to actually do. Accounts with high transaction volume or real fraud exposure get reconciled weekly, sometimes daily, because finding an unauthorized transaction sooner limits the loss and, for business accounts, protects your standing to dispute it.

How long should you keep bank reconciliations?

The IRS does not set a retention period for reconciliations as a document type. It sets a period of limitations on the return they support: generally 3 years, 6 years if you failed to report income exceeding 25 percent of the gross income shown on the return, 7 years for a claim involving worthless securities or a bad debt deduction, and indefinitely if no return was filed. Employment tax records run at least 4 years. Plenty of CPA firms recommend keeping statements and reconciliations for 7 years as a conservative default, which is sound advice rather than a legal requirement. Be wary of pages that state 7 years as an IRS rule.

When Excel stops being the right tool

A spreadsheet reconciliation is perfectly defensible for one or a handful of accounts. It starts to hurt when you are reconciling many accounts every month, when several people touch the same working paper, or when a reviewer needs a sign off trail. At that point look at dedicated bank reconciliation software, keeping in mind that most of it will not read a PDF statement either.

Either way, the cash accounts have to tie out before anything else in the month end close checklist can be trusted, which is exactly why bank reconciliation sits so early in the sequence. Get the statement into rows, match, adjust both columns, and post only the book side. The rest of the close gets easier from there.