How to Reconcile Bank Statements in Excel: A Step-by-Step Monthly Process
How to Reconcile Bank Statements in Excel: A Step-by-Step Monthly Process
Bank reconciliation answers one question: does what the bank says happened match what your books say happened? When the answer is yes, you can trust both. When it is no, something is wrong — a fee you never recorded, a check that has not cleared, a duplicate entry, or in the worst case a fraudulent charge — and reconciliation is how you find it.
Accounting software automates most of this, but plenty of situations call for doing it in Excel: side businesses kept in spreadsheets, accounts your software does not connect to, cleanup work on months that were never reconciled, or just wanting to understand what the software is doing before you trust it. This is the full process, with formulas that work in current Excel (XLOOKUP requires Excel 2021 or Microsoft 365; alternatives noted where it matters).
Step 0: Get Both Sides into Excel
You need two transaction lists: the bank's version and yours.
The bank side comes from your statement. If your bank offers CSV download for the exact statement period, use it. If all you have is the PDF — common for older months, and for banks whose CSV exports do not line up with statement periods — convert the PDF. BankPDFTool.com turns statement PDFs from Chase, Wells Fargo, Bank of America, and most other US banks into Excel or CSV; the free tier handles one page per day without an account, or five pages per day with a free account. Before using converted data, make sure it is clean — consistent dates, bare signed amounts — per our CSV formatting guide.
The book side is your own record: the check register, the ledger tab of your bookkeeping spreadsheet, or an export from your accounting system for the same account and period.
Standardize both lists to the same convention before comparing anything: deposits positive, withdrawals negative, dates as real Excel dates. Half of all "reconciliation problems" are actually sign or date-format mismatches between the two lists.
Step 1: Set Up the Reconciliation Worksheet
Use one workbook with three sheets:
- Bank — the statement transactions. Columns: Date (A), Description (B), Amount (C), and two working columns you will fill in: Match (D) and Key (E).
- Books — your records, same column layout.
- Recon — the summary sheet where the tie-out lives.
On the Recon sheet, set up this skeleton (values on the right are examples):
| Cell | Label | Content |
|---|---|---|
| B2 | Statement beginning balance | typed from the statement |
| B3 | Total deposits per statement | =SUMIF(Bank!C:C,">0") |
| B4 | Total withdrawals per statement | =SUMIF(Bank!C:C,"<0") |
| B5 | Computed ending balance | =B2+B3+B4 |
| B6 | Statement ending balance | typed from the statement |
| B7 | Data check | =ROUND(B5-B6,2) |
B7 must be zero before you go any further. This is the beginning-balance + deposits − withdrawals = ending-balance tie-out (B4 is already negative, hence the plus sign). If it is not zero, your Bank sheet does not faithfully represent the statement — a missing row, a duplicated row, or a sign error — and no amount of matching will fix that. Find it now: compare your deposit and withdrawal totals against the totals printed in the statement's summary box to learn which side is off, then compare row counts against the statement's transaction count.
Step 2: Build Match Keys
Matching on amount alone fails the moment you have two $50.00 transactions in a month, and you will. Matching on date alone fails constantly. A practical key combines both. In E2 on each sheet, filled down:
=TEXT(A2,"yyyy-mm-dd")&"|"&TEXT(C2,"0.00")
This produces keys like 2026-07-14|-83.20. It deliberately excludes the description, because the bank's description ("POS DEBIT 4417 STAPLES #1042") will never equal yours ("Office supplies"). Date plus amount is specific enough to match most transactions automatically and loose enough that description wording does not matter.
Step 3: Match with COUNTIFS and XLOOKUP
In the Match column (D2) of the Bank sheet, count how many book entries share the key:
=COUNTIFS(Books!E:E, E2)
And on the Books sheet, mirror it: =COUNTIFS(Bank!E:E, E2). Fill both down. Now read the results:
- 1 — matched. Ignore these; they are the boring majority.
- 0 — unmatched. These rows are the entire remaining reconciliation. A zero on the Bank sheet means the bank has something your books do not (usually a fee, interest, or a forgotten transaction). A zero on the Books sheet means your books have something the bank has not processed (usually an outstanding check or a deposit in transit).
- 2 or more — either genuine duplicates on one side, or two legitimately identical transactions (two $12.99 subscriptions on the same day). Inspect these by hand; the count being equal on both sides usually means they are fine.
To pull the matching book description next to each bank row for easier review, add in F2 of the Bank sheet:
=XLOOKUP(E2, Books!E:E, Books!B:B, "NO MATCH")
On Excel versions without XLOOKUP, the equivalent is =IFERROR(INDEX(Books!B:B, MATCH(E2, Books!E:E, 0)), "NO MATCH").
If exact date matching leaves too many orphans because your books use the transaction date and the bank posts a day or two later, loosen the match: drop the date from the key and instead flag amount matches within a date window using
=COUNTIFS(Books!C:C, C2, Books!A:A, ">="&A2-3, Books!A:A, "<="&A2+3)
which counts book entries with the same amount dated within three days either side. Use the strict key first, then rerun only the leftovers with the windowed version, so you do not create false matches on common amounts.
Step 4: Classify the Unmatched Rows
Filter each sheet to Match = 0. Everything you see falls into one of a few buckets.
Timing differences (normal, not errors)
- Outstanding checks. You wrote and recorded the check; the payee has not deposited it. It sits in your books but not on the statement. It stays on your outstanding list until it clears in a future month.
- Deposits in transit. Recorded in your books on the 30th, credited by the bank on the 1st. Same idea, other direction.
These are not fixed; they are carried. List them on the Recon sheet, because they are exactly what explains the difference between the bank's ending balance and your book balance.
Book errors and omissions (fix in your books)
- Bank fees, interest, and service charges you never entered. The most common unmatched bank row there is. Record them.
- Duplicates in your books — an expense entered when the bill arrived and again when it was paid. Delete one.
- Transposed digits. You recorded $541.20; the bank shows $514.20. A useful property: a transposition error is always divisible by 9. If one unmatched bank row and one unmatched book row differ by an amount divisible by 9 ($27.00 here), check for swapped digits before anything else.
Bank-side anomalies (query the bank)
Rarely, the unmatched item is a charge you do not recognize at all. That is the fraud-detection function of reconciliation doing its job. Investigate before assuming your books are wrong.
Step 5: The Final Tie-Out
The reconciliation is done when this holds. On the Recon sheet:
| Cell | Label | Content |
|---|---|---|
| B10 | Statement ending balance | =B6 |
| B11 | + Deposits in transit | sum of that list |
| B12 | − Outstanding checks | sum of that list, entered as negative |
| B13 | Adjusted bank balance | =B10+B11+B12 |
| B14 | Book balance (after recording fees/fixes) | from your Books sheet |
| B15 | Difference | =ROUND(B13-B14,2) |
B15 = 0 means you are reconciled. Save the workbook as your reconciliation record for the month.
When It Still Does Not Balance
A stubborn difference in B15 almost always yields to one of these checks, roughly in order of likelihood:
- The difference equals one transaction exactly. Search both sheets for that amount:
=COUNTIF(C:C, -B15)and=COUNTIF(C:C, B15). A hit means a missed or unrecorded transaction. - The difference is exactly double a transaction. A sign error: something entered as a deposit that was a withdrawal, or vice versa. Search for half the difference.
- The difference is divisible by 9. Transposed digits somewhere.
=MOD(B15*100,9)=0tests it (working in cents avoids floating-point noise). - Duplicates. Highlight them: select the Amount column, Conditional Formatting > Highlight Cells > Duplicate Values, or filter the Match column for counts of 2+.
- Last month was never actually reconciled. If the beginning balance in B2 does not match last month's reconciled ending balance, this month cannot balance no matter what you do. Fix the prior month first.
- Stale outstanding checks. A check outstanding for several months may have been lost or double-recorded. Chase it down rather than carrying it forever.
Two Excel-specific gotchas worth knowing. First, rounding: always wrap difference checks in ROUND(...,2), because floating-point arithmetic can leave a difference of 0.0000000001 that displays as zero but fails an exact test. Second, text-formatted numbers: an amount stored as text sums as zero silently. =SUMPRODUCT(--ISTEXT(C2:C200)) counts text cells in your amount range; it should return 0.
Making Next Month Faster
The first reconciliation in this format takes an hour or two, mostly on setup and on cleaning up a backlog of unrecorded fees. After that, the monthly routine is: convert or download the new statement, paste it into the Bank sheet, paste your ledger into Books, and the formulas do the matching in seconds. The manual work shrinks to reviewing the unmatched rows, which for a typical small-business account is a handful of items.
Two habits keep it that way. Carry outstanding items forward explicitly — last month's outstanding checks should be the first thing you look for on this month's statement. And reconcile every month; a three-month gap does not triple the work, it compounds it, because errors interact.
If the bottleneck in your process is getting statement PDFs into Excel in the first place, that part is solvable in under a minute per statement — see our guide to converting bank statements to Excel, or upload a statement at bankpdftool.com and paste the result straight into your Bank sheet.
