How to Reconcile a Bank Statement in Excel

By Syed Hassan, creator of SafeStatement · Last updated September 29, 2026

Reconciling means proving that your records and the bank's records tell the same story: every transaction accounted for and every difference explained. Accounting software automates parts of it, but plenty of small businesses, landlords, treasurers of clubs and charities, and households do it perfectly well in Excel. This guide gives you a workflow that works for anything from a personal checking account to a small business's monthly close, with the formulas to paste in.

What you need

• The bank's side: your statement for the period, as a spreadsheet. Convert the PDF with our free converter. It extracts date, description and amount columns and processes the file entirely in your browser.
• Your side: your own records for the same period, such as an invoice log, cash book, checkbook register, or an export from your accounting system.
• Last month's reconciliation, if you have one, for the list of items that were outstanding at the end of last month.

Step 1: Set up two sheets with the same columns

Put the bank's transactions on a sheet named Bank and your records on a sheet named Books. Give both the same layout, with column A as Date, B as Description, and C as Amount (positive for money in, negative for money out). Consistent signs matter. If your books record payments as positive numbers, flip them with a helper column (=-C2) before matching.

Make sure dates are real Excel dates, not text. Select the column and look at the status bar: if Excel shows a Sum or Average, they're numbers. If it only shows Count, they're text and need fixing with Data → Text to Columns → Finish or =DATEVALUE().

Step 2: Check that the statement adds up

Before matching individual items, check that you have the whole statement:

Opening balance + SUM(deposits) + SUM(withdrawals) = Closing balance

On the Bank sheet, put the statement's opening balance in a cell (say F1) and use =F1+SUM(C:C). Compare the result to the closing balance printed on the statement. If they differ, rows are missing or duplicated, usually at page breaks, or a multi-line transaction was split. Fix this first. Matching against incomplete data wastes time.

Step 3: Match transactions with COUNTIFS

On the Bank sheet, add a column D headed Matched and enter in D2:

=COUNTIFS(Books!$C:$C, C2, Books!$A:$A, ">="&A2-5, Books!$A:$A, "<="&A2+5)

This counts entries in your books with the same amount dated within five days of the bank date. The tolerance matters because dates rarely line up exactly: cheques clear days after you write them, card payments settle a day or two late, and deposits made on a Friday post on Monday. Adjust the 5 to suit your account.

Put the mirror formula on the Books sheet:

=COUNTIFS(Bank!$C:$C, C2, Bank!$A:$A, ">="&A2-5, Bank!$A:$A, "<="&A2+5)

Then read the results:

• 1: matched cleanly.
• 0: unmatched. It needs an explanation.
• 2 or more: several possible matches. This is common with repeated amounts like subscriptions. Check these by eye, and watch for duplicates.

Filter each sheet on 0 and you have your discrepancy list. Use Home → Conditional Formatting to shade zeros red so they stand out.

Step 4: Explain every unmatched item

Step 5: The reconciliation summary

Add a third sheet named Summary that proves the two sides agree once timing differences are taken into account:

Use =SUMIFS() on each sheet's Amount column, filtered to unmatched items, to fill these lines automatically. The reconciliation is done when the difference is zero.

Tricks for finding a stubborn difference

• Difference divisible by 9? Probably two digits swapped (for example 54 entered as 45).
• Difference exactly twice an amount? A transaction was entered with the wrong sign, as a deposit instead of a payment or the other way round.
• Difference matches one transaction? It's missing from one side. Search both sheets for that amount with Ctrl+F.
• Difference is a round number? Check cash deposits and transfers between your own accounts first.

Optional: see matches side by side with XLOOKUP

COUNTIFS tells you whether a match exists. To see which book entry matched, add a column E on the Bank sheet with XLOOKUP (Excel 365 and 2021):

=XLOOKUP(1, (Books!$C$2:$C$2000=C2)*(ABS(Books!$A$2:$A$2000-A2)<=5), Books!$B$2:$B$2000, "no match")

This returns the description of the first book entry with the same amount within five days. Reading the bank description next to your own is the fastest way to confirm a match is real and not a coincidence. For example, two unrelated 49.99 payments in the same week will both "match", and this column makes that obvious.

Keep the workbook trustworthy

A reconciliation is only useful if nobody can quietly change it afterwards. Once a month is finished, save a PDF of the Summary sheet next to the statement, and protect the sheet (Review → Protect Sheet) so formulas can't be overwritten by accident. Add a note of who reconciled it and when. That small audit trail is exactly what an accountant, auditor or club committee will want to see.

Making it a monthly routine

The second month goes faster than the first. Keep one workbook per account per year, add a new pair of sheets for each month (Right-click tab → Move or Copy → Create a copy), and paste in the new data. Carry forward last month's outstanding items to the top of the new Books sheet, because they should clear this month. If one is still outstanding after 2–3 months, follow it up.

Converting the new statement takes under a minute, and a typical personal account reconciles in about ten. If you're handling hundreds of transactions a month for a business, dedicated accounting software starts to pay for itself. Our QuickBooks import guide covers moving your history across.