To do a bank reconciliation in Excel, put the bank statement lines and the ledger lines on two sheets, give each line a match key, and let formulas look up every statement line in the ledger. Lines with no partner are your reconciling items. XLOOKUP, COUNTIFS and SUMIFS do the matching, so you tick nothing by hand.
Getting the bank statement and the ledger into one Excel workbook
Both lists go into one workbook, each on its own sheet, with identical column order. Formulas can only compare what sits in clean, consistent columns, so the layout is the first job. Name the sheets Bank and Ledger, and keep one transaction per row with no subtotals or blank rows between them.
The columns each sheet needs
On the Bank sheet, use these columns:
- A, Date. The posting date, as a real Excel date and not text.
- B, Description. The text the bank prints.
- C, Amount. One signed column: deposits positive, withdrawals negative.
- D, Key. A helper column the next section builds.
- E, Ledger ref. The formula result that names the matching ledger line.
On the Ledger sheet, use the same pattern: Date, Reference (a check number or entry number), Amount, Key, and a final column that counts how many bank lines claim the row.
The single signed amount column matters. Statements often print debits and credits in two columns, and a ledger often does the same. Combine them into one column before you match, with a formula such as =Credit - Debit, so that a deposit and a withdrawal can never be confused for each other.
Clean the data before you compare
Most failed matches come from the data, not the formula. Check these before you write one:
- Dates are real dates. If a date is left-aligned, it is probably text. Convert it, or every date comparison will quietly return nothing.
- Amounts are numbers. Text such as "1,200.00 " with a trailing space will not match 1200.
- Signs agree. The bank shows a check as a negative number and your ledger may show it as a positive credit. Flip one so both sheets mean the same thing.
- Both lists cover the same account and the same period. Add a few days of the following month to the ledger if you expect checks to clear late.
Prove the statement before you match it
Add one check cell on the Bank sheet: the opening balance plus the sum of the Amount column should equal the closing balance printed on the statement. If it does not, the data you pasted is incomplete or wrong, and matching will only hide that. Fix the import first. If the lines came from a PDF, this is where copied tables often lose a row or split a long description over two rows.
Which Excel formula matches bank transactions best?
No single formula wins. XLOOKUP is best for finding the ledger line that matches a statement line, COUNTIFS is best for checking whether a match exists and whether it is ambiguous, and SUMIFS is best for totalling the items left over. Use all three, each for its own job.
The sample data
The formulas below run against eight example statement lines and eight example ledger lines for a March statement. The values are made up for illustration.
| Bank row | Date | Description | Amount |
|---|---|---|---|
| 2 | 2026-03-02 | Deposit | 2,500.00 |
| 3 | 2026-03-03 | Check 1041 | -412.80 |
| 4 | 2026-03-05 | Card purchase | -48.00 |
| 5 | 2026-03-09 | Card purchase | -48.00 |
| 6 | 2026-03-12 | Check 1042 | -1,200.00 |
| 7 | 2026-03-15 | Deposit | 900.00 |
| 8 | 2026-03-20 | Monthly fee | -15.00 |
| 9 | 2026-03-28 | Check 1044 | -75.25 |
| Ledger row | Date | Reference | Amount |
|---|---|---|---|
| 2 | 2026-03-02 | DEP-0302 | 2,500.00 |
| 3 | 2026-03-02 | 1041 | -412.80 |
| 4 | 2026-03-05 | CARD-0305 | -48.00 |
| 5 | 2026-03-08 | CARD-0308 | -48.00 |
| 6 | 2026-03-10 | 1042 | -1,200.00 |
| 7 | 2026-03-14 | DEP-0314 | 900.00 |
| 8 | 2026-03-26 | 1043 | -300.00 |
| 9 | 2026-03-28 | 1044 | -75.25 |
The bank fee has no ledger entry, and check 1043 has not cleared. Those two lines are the answer the formulas should find.
The formulas
| Purpose | Formula (in Bank row 2, copied down) |
|---|---|
| Occurrence key | =TEXT(C2,"0.00")&"|"&COUNTIF($C$2:C2,C2) |
| Find the ledger reference | =XLOOKUP(D2,Ledger!$D:$D,Ledger!$B:$B,"No match") |
| Is there a match in the date window? | =COUNTIFS(Ledger!$C:$C,C2,Ledger!$A:$A,">="&A2-7,Ledger!$A:$A,"<="&A2+1) |
| Does the ledger claim this row? (Ledger row 2) | =COUNTIF(Bank!$D:$D,D2) |
| Total of unmatched ledger items | =SUMIFS(Ledger!$C:$C,Ledger!$E:$E,0) |
| Total of unmatched bank items | =SUMIFS(Bank!$C:$C,Bank!$E:$E,"No match") |
The same key formula goes in column D of the Ledger sheet. In the pipe-separated key, the first part is the amount and the second part is how many times that amount has appeared so far in the list. The two 48.00 card purchases become -48.00|1 and -48.00|2 on both sheets, so each statement line pairs with its own ledger line.
What each function is good at
- XLOOKUP returns a value from the row that matches, and lets you set a default such as "No match" when nothing is found. It looks in any direction and defaults to an exact match. It exists only in newer versions of Excel, so check your version first.
- COUNTIFS counts rows that meet several conditions at once. A count of 0 means no partner, 1 means one, and anything above 1 means the match is ambiguous.
- SUMIFS adds amounts that meet conditions. It is the right tool for totalling the unmatched items that go on the reconciliation statement.
If you only need to know "does this exact amount appear anywhere", COUNTIF is enough. For the full layout of the statement these totals feed, see the post on the bank reconciliation statement format.
Matching on amount and a date window in Excel
An amount-and-date-window match accepts a ledger line when the amount is identical and its date falls within a few days of the bank's posting date. The window is needed because a check you wrote on the 10th may clear on the 12th, and a match on exact dates would miss it.
Choosing the window
Ledger dates usually come before bank dates, because you record a payment when you write it and the bank posts it when it clears. The window in the COUNTIFS formula reflects that: it looks back seven days from the bank's posting date and forward one day. Adjust it to your own pattern. Card purchases may need a window of only a few days. Checks mailed to distant payees may need two weeks.
Do not widen the window to make the matches work. A wide window turns a real difference into a false match. If a payment appears 40 days late, you want to see it, not have the formula hide it.
Reading the counts
Run the date-window COUNTIFS against the sample data and you get these results:
| Bank row | Description | Window count | Reading |
|---|---|---|---|
| 2 | Deposit 2,500.00 | 1 | One partner |
| 3 | Check 1041 | 1 | One partner |
| 4 | Card purchase 03-05 | 1 | One partner |
| 5 | Card purchase 03-09 | 2 | Ambiguous: both 48.00 lines fall in the window |
| 6 | Check 1042 | 1 | One partner |
| 7 | Deposit 900.00 | 1 | One partner |
| 8 | Monthly fee | 0 | No partner: needs a book entry |
| 9 | Check 1044 | 1 | One partner |
Row 5 is the lesson. The count says two ledger lines qualify, so a date window alone cannot decide which one belongs to this statement line. That is when the occurrence key from the previous section does the pairing, in order of appearance.
A date window does not prove the match
Even a count of 1 only says that something with that amount sits nearby. Two unrelated 500.00 items a few days apart can fool it. Treat the window as a filter and read the description or reference too. If your payees repeat the same amounts each month, as with rent or a fixed subscription, also consider whether a duplicate charge is hiding in the data. The rules for catching that case are covered in the same payee, same amount, close dates duplicate transaction rules.
Why VLOOKUP bank matching fails on repeated amounts
VLOOKUP returns the first row in the lookup range that matches, and bank data is full of identical amounts. When two statement lines both show 48.00, both lookups find the same first ledger line, so one ledger line is claimed twice and another is never claimed.
The failure on the sample data
Look up the amount alone and the two card purchases behave like this:
| Bank row | Lookup value | Ledger line returned | Correct line |
|---|---|---|---|
| 4 | -48.00 | CARD-0305 | CARD-0305 |
| 5 | -48.00 | CARD-0305 | CARD-0308 |
Row 5 shows a match, so nothing in your sheet looks wrong. Yet ledger line CARD-0308 is never claimed, and CARD-0305 is claimed twice. The unmatched total is off by 48.00 in a way that looks like a real difference. Nothing in the VLOOKUP result alerts you.
Other VLOOKUP limits
- It searches only to the right. The value you look up must sit in the first column of the range, so returning a reference to its left needs a rearranged sheet.
- Column numbers break. VLOOKUP takes a column index number. Insert a column in the ledger sheet and the formula silently returns the wrong field.
- Approximate match is the default. Leaving off the last argument, or entering TRUE, makes VLOOKUP assume a sorted list and can return a wrong amount without an error. Always use FALSE for exact matching.
- It cannot count. It never tells you that a second candidate exists.
The fix
Add the occurrence key described earlier and look up the key instead of the amount. Each repeated amount then becomes its own value, and each ledger line can be claimed exactly once. A second safeguard costs one more column: count how many bank lines claim each ledger line, and flag any count other than 1. If you are stuck with an older version of Excel that has no XLOOKUP, INDEX with MATCH on the key gives the same result.
How do you show unmatched items in the Excel bank reconciliation?
Show unmatched items by flagging every line whose match result is empty, then pulling the flagged lines into a list below the reconciliation. The unmatched bank lines are the book entries you still need to make, and the unmatched ledger lines are the timing items, such as outstanding checks.
Flag the lines
On the Bank sheet, column E already returns "No match" when nothing was found. On the Ledger sheet, the claim count in column E returns 0 when no bank line points at the row. With the sample data, the flags are:
- Bank row 8, the 15.00 monthly fee, has no ledger partner.
- Ledger row 8, check 1043 for 300.00, has no bank partner.
Pull them into a list
In versions of Excel that support dynamic arrays, a FILTER formula lists the unmatched ledger rows in one step:
=FILTER(Ledger!A2:C9,Ledger!E2:E9=0,"None")
In older versions, apply an AutoFilter on the flag column and copy the visible rows, or number the flagged rows with a running count and pull them with INDEX.
Build the reconciliation from the lists
The unmatched lists drop straight into the statement. Using the sample figures, and assuming a closing bank balance of 5,000.00 for illustration:
| Line | Example amount |
|---|---|
| Balance per bank | 5,000.00 |
| Less: outstanding check 1043 | -300.00 |
| Adjusted bank balance | 4,700.00 |
| Balance per books | 4,715.00 |
| Less: bank fee not yet recorded | -15.00 |
| Adjusted book balance | 4,700.00 |
The two adjusted balances agree, which is the proof. The 15.00 fee still needs a journal entry, and the check needs no entry because the bank will catch up. If your totals do not agree, the cause is nearly always an unflagged pair, an amount typed differently on each side, or a line outside the statement period.
Finish with the habits that keep a workbook reliable. Lock the formulas after you build them, keep one workbook per account per month, and save the statement PDF beside it. When the sheet is done, hand it to someone else for review; the timing of that handoff is covered in monthly bank reconciliation deadlines. For the layout of the sheet itself, see which columns a bank reconciliation template needs.
If you are reconciling a personal or small business checking account, the manual method alongside a register is described in how to reconcile a checking account against the register.
When Excel bank reconciliation stops scaling
Excel stops scaling when the formulas become harder to trust than the work they replace. That point arrives with a few hundred lines a month on one account, with many accounts, or with many people editing one file. The risk is a silent formula error, not slowness.
Signs you have reached the limit
- Whole-column lookups slow the file. Thousands of XLOOKUP and COUNTIFS formulas over entire columns make the workbook sluggish.
- A pasted value breaks a formula. One overwritten cell in column E means a line shows as matched when nothing was checked.
- Version drift. Three copies of the workbook exist, each with a different window setting.
- Amount-only matching misses split and combined items. A customer who pays two invoices with one deposit has no single ledger line to match.
- The reviewer cannot see why a line matched. A count of 1 is not evidence.
Splits and combined payments
A single deposit that covers three invoices, or one invoice paid in two installments, needs a many-to-one match. Excel can total a group with SUMIFS on a batch number, but you must create that batch number by hand. Past a handful of cases a month, the manual grouping is where mistakes collect.
What to do next
Before you outgrow the workbook, decide what you need from a replacement. The page on transaction matching software explains how rules-based matching decides that two lines agree and where it still needs a person.
PreciseBankFlow is built for firms reconciling many client statements: it checks that each statement foots, matches statement lines to your ledger and exports the exceptions to Excel or PDF for your workpapers. It is opening to firms in small groups through the Get started page, and your review and sign-off remain the control.