How to Reconcile a Bank Statement in Excel (With Formulas)

4 min read · Updated October 29, 2026 · By the StatementCheap team

Reconciling a bank statement means proving two things. First, that the transactions you have add up: the opening balance plus every deposit and withdrawal equals the closing balance. Second, that your books agree with the bank once you account for timing differences. Excel handles both well with a few formulas. This guide builds the sheet step by step, using one small example you can copy.

Part 1: Check the statement itself

Do this first, especially if the transactions came from a PDF. If a row was missed or a digit misread during conversion, everything after it will be off.

Set up the columns

Put the transactions on a sheet named Statement with these columns:

• A: Date
• B: Description
• C: Amount (money in positive, money out negative)
• D: Statement balance (the running balance printed on the statement, if there is one)
• E: Calculated balance
• F: Difference

Row 1 holds headers. Row 2 holds the opening balance: type "Opening balance" in B2 and the amount in D2. Transactions start in row 3.

The formulas

• E2: =D2 starts the calculated balance at the opening balance.
• E3: =E2+C3 adds each transaction to the previous balance. Fill it down.
• F3: =ROUND(D3-E3,2) compares your balance with the bank's. Fill it down. ROUND stops tiny floating-point differences from showing up as errors.

Example

Add a summary block

Next to the data, in columns H and I, build a small check:

Compare I4 and I5 with the summary box on the first page of the statement. Most banks print total deposits and total withdrawals there. If those match and I7 is zero, the transaction list is complete.

Find the first broken row

If I7 isn't zero, you want the first row where the balance stops matching. This formula returns its date and works in any recent Excel version:

=IFERROR(INDEX(A3:A500,MATCH(TRUE,INDEX(F3:F500<>0,0),0)),"No breaks")

In Microsoft 365 you can use XLOOKUP instead:

=XLOOKUP(TRUE,F3:F500<>0,A3:A500,"No breaks")

For a visual check, select F3:F500, add a conditional formatting rule with the formula =F3<>0, and give it a red fill.

Read the size of the difference

• It equals one transaction. A row is missing or duplicated. Look for a page break near the first broken row.
• It's twice a transaction. A sign is flipped: a deposit entered as a withdrawal, or the reverse.
• It's divisible by 9. Often a transposition, such as 84.37 typed as 48.37. Test it with =MOD(ROUND(ABS(I7)*100,0),9)=0.
• It's a small round number. Often a fee or interest line that was skipped because it was printed in a separate section.

To spot duplicates, add a column with =COUNTIFS(A:A,A3,C:C,C3)>1. TRUE means another row has the same date and amount. That isn't always wrong (two coffees on one day), but it's worth a look.

Part 2: Reconcile the bank to your books

The statement and your records will rarely match on the last day of the month. Checks you wrote may not have cleared yet. A deposit made on the 30th may not post until the 1st. The bank may have charged a fee you haven't recorded. Reconciliation explains each difference.

Match transactions

Put your own records on a sheet named Books with the same layout: Date in A, Description in B, Amount in C, using the same sign convention. In D2, add:

=COUNTIFS(Statement!$C:$C,C2,Statement!$A:$A,">="&A2,Statement!$A:$A,"<="&A2+10)>0

This looks for the same amount on the statement dated from your book date up to 10 days later, since checks and deposits usually clear after you record them. Fill it down. FALSE means the item hasn't cleared: it's an outstanding check or a deposit in transit. Run the same idea in the other direction (from Statement to Books) to find items the bank recorded that you haven't, such as fees and interest.

The reconciliation

Say your books show 3,868.53 at September 30. You find a 600.00 deposit made on the 30th that isn't on the statement, check 1045 for 215.00 that hasn't cleared, and the 12.00 service fee that isn't in your books yet.

The two adjusted balances match, so the account is reconciled. In a sheet, the check is one more cell: =ROUND(AdjustedBank-AdjustedBook,2), which should be 0. Then record the 12.00 fee in your books so it doesn't show up again next month.

Tips that save time

• Reconcile every month. One month of differences is easy to find. Twelve months is not.
• Keep one sign convention everywhere: money out negative.
• Carry outstanding items forward. Last month's uncleared checks should clear this month. If one is months old, follow up.
• Lock the finished month. Copy the reconciled sheet and save it with the statement PDF.

Skip the data entry

Part 1 is the step people skip, and it's the one that catches conversion errors. StatementCheap runs it automatically. Every PDF statement you convert is checked against its own opening and closing balances, and rows where the running balance breaks are flagged, before you download Excel, CSV, QBO, Xero CSV or OFX. Part 2, matching to your books, is still yours to do in Excel or your accounting software. You get 5 free pages a day with a free account: just an email, no card. Convert a statement, or read how to convert a bank statement PDF to Excel and our page for bookkeepers.