How to Do Reconciliation in Excel

To do a reconciliation in Excel, put the two lists you’re comparing (usually a bank statement and your own ledger) on separate sheets with the same columns, use COUNTIFS or XLOOKUP to flag every transaction that appears in both, then filter for the ones that don’t match. Those leftovers explain the difference between the two balances. The example below walks through a monthly bank reconciliation, but the same method works for credit cards, vendor statements and inventory counts.

Step 1: Set up the two sheets

Create a sheet called Bank and one called Ledger. Give both the same headings in row 1:

A	B	C	D
Date	Description	Amount	Matched

• Paste or import the bank transactions for the month into the Bank sheet. If your bank only gives you a PDF, see how to convert a bank statement to Excel.
• Export the same month from your accounting software or checkbook into the Ledger sheet.
• Use one sign convention on both sheets: deposits positive, withdrawals negative. If your bank splits them into two columns, combine them with =D2-E2 in a new column.
• Make sure dates and amounts are real numbers, not text. Right-aligned values are numbers. Left-aligned ones usually need Data > Text to Columns > Finish to convert.

Step 2: Flag matching transactions

In D2 on the Bank sheet, enter:

=IF(COUNTIFS(Ledger!C:C,C2,Ledger!A:A,A2)>0,"Matched","Check")

This looks for the same amount on the same date in the ledger. Copy it down. Put the mirror formula in D2 on the Ledger sheet:

=IF(COUNTIFS(Bank!C:C,C2,Bank!A:A,A2)>0,"Matched","Check")

Payments often clear a day or two after you record them, so dates won’t always line up. If you get too many false “Check” results, match on amount only by dropping the date part: =IF(COUNTIF(Ledger!C:C,C2)>0,"Matched","Check").

When the same amount appears more than once

Two $50.00 payments on one side and one on the other will all show “Matched”. To match them one-for-one, add a key column on both sheets in E2:

=C2&"|"&COUNTIF($C$2:C2,C2)

This turns the first $50 into “50|1” and the second into “50|2”. Then match on the key instead: =IF(COUNTIF(Ledger!E:E,E2)>0,"Matched","Check").

Pull in the matching details with XLOOKUP

To see the ledger description next to each bank line, use =XLOOKUP(E2,Ledger!E:E,Ledger!B:B,"Not found"). In older versions use VLOOKUP or INDEX/MATCH. See how to do an XLOOKUP in Excel and how to use VLOOKUP for multiple columns.

Step 3: Highlight and investigate the differences

1. Select the data on each sheet and press Ctrl + Shift + L to add filters.
2. Filter the Matched column to show only Check.
3. Optional: use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains and type “Check” to color those rows.

Every unmatched row falls into one of these groups:

Unmatched item	Where it shows	What to do
Deposit in transit	Ledger only	Add to the bank balance in the reconciliation
Outstanding check or payment	Ledger only	Subtract from the bank balance
Bank fee or interest	Bank only	Record it in your ledger
Recording error (wrong amount)	Both, with different amounts	Correct the ledger entry
Duplicate entry	Ledger, twice	Delete the duplicate

A tip for errors: if the difference between the two balances divides evenly by 9, two digits were probably swapped, such as 54 typed as 45.

Step 4: Build the reconciliation statement

On a third sheet, lay out the summary. Here is a simple format with example figures:

	Amount
Balance per bank statement	12,480.00
Add: deposits in transit	1,250.00
Less: outstanding checks	(930.00)
Adjusted bank balance	12,800.00
Balance per ledger	12,835.00
Less: bank fees not yet recorded	(35.00)
Adjusted book balance	12,800.00
Difference	0.00

Use SUMIFS to pull the totals from the other sheets so they update automatically. For example, deposits in transit are =SUMIFS(Ledger!C:C,Ledger!D:D,"Check",Ledger!C:C,">0") and outstanding payments are =SUMIFS(Ledger!C:C,Ledger!D:D,"Check",Ledger!C:C,"<0"). When the difference is zero, the reconciliation is done.

Tips for monthly reconciliations

• Save the finished file as a template. Next month, clear the data and paste in new transactions.
• Format each sheet as a table with Ctrl + T so formulas extend to new rows automatically.
• Carry forward last month’s unmatched items, such as checks that still haven’t cleared.
• Round amounts with =ROUND(C2,2) if one system stores extra decimal places, or tiny differences will block matches.

For a broader look at comparing lists, see how to compare two sheets in Excel for matches and how to match data from two worksheets.

Frequently asked questions

Is there a reconciliation template in Excel?

Search for “reconciliation” under File > New to see Microsoft’s templates. They’re a good starting point, but the matching formulas above are what make the job quick.

Can Power Query do this?

Yes. Load both lists with Data > From Table/Range, then use Merge Queries with a Left Anti join to return the rows that appear in only one list. It’s worth learning if you reconcile thousands of rows every month.

Why don’t matching amounts match?

Usually one is stored as text, has a trailing space, or has hidden decimals. Check with =ISNUMBER(C2) and fix with =VALUE(TRIM(C2)).

Summary

1. Put the bank and ledger data on two sheets with matching columns.
2. Flag matches with COUNTIFS, using a key column for repeated amounts.
3. Filter the unmatched rows and sort them into timing differences, fees and errors.
4. Build an adjusted balance for each side and confirm the difference is zero.