How to reconcile two bank statements in Excel

1Put both lists in the same layout

On both sheets, use the same three columns, starting in row 2 with headings in row 1:

Use one amount column with a sign: money in is positive, money out is negative. If the bank export has separate Debit and Credit columns (say D and E), build the amount in a new column:

=E2-D2

Empty cells count as 0, so this gives a positive number for credits and a negative one for debits.

2Make sure dates and amounts are real numbers

Exports often bring dates and amounts in as text, and text won't match a number. Check with =ISNUMBER(A2) and =ISNUMBER(C2): both should be TRUE.

Convert text numbers with Convert to Number or Data > Text to Columns. Then round amounts to cents so tiny differences don't stop a match:

=ROUND(C2,2)

If the bank's dates include a time, strip it with =INT(A2) so they match plain dates.

3Flag bank transactions that aren't in your records

On the Bank sheet, in D2, count how many rows in Books have the same date and the same amount:

=IF(COUNTIFS(Books!$A:$A,A2,Books!$C:$C,C2)>0,"Matched","Not in books")

Fill it down. Rows marked Not in books are usually bank fees, interest, direct debits or card payments you haven't recorded yet.

Match on date and amount together, not amount alone. Two $45.00 payments on different days are different transactions.

4Flag your records that aren't on the statement

On the Books sheet, in D2, do the same check in the other direction:

=IF(COUNTIFS(Bank!$A:$A,A2,Bank!$C:$C,C2)>0,"Matched","Not in bank")

Rows marked Not in bank are usually cheques that haven't cleared, deposits in transit, or entries with a typo.

5Handle repeated transactions with an occurrence number

COUNTIFS only asks "is there at least one?". If the bank shows two $45.00 payments on the same day and your records show one, both bank rows say Matched. To pair them one to one, number each repeat.

On both sheets, in E2, count how many times this date and amount has appeared so far:

=COUNTIFS($A$2:A2,A2,$C$2:C2,C2)

The first $45.00 on that day gets 1, the second gets 2. Build a key in F2 that combines date, amount and occurrence:

=A2&"|"&C2&"|"&E2

Now match on the key. On the Bank sheet:

=IF(COUNTIF(Books!$F:$F,F2)>0,"Matched","Not in books")

The second $45.00 on the bank side has no partner with occurrence 2, so it's flagged.

6Amounts with opposite signs

Some systems record a payment as a positive number in a "Paid" column, while the bank shows it as negative. Either fix the sign in step 1, or compare against the negative amount:

=IF(COUNTIFS(Books!$A:$A,A2,Books!$C:$C,-C2)>0,"Matched","Not in books")

7Allow a few days between the two dates

Card payments and cheques often post a day or more after you record them. To accept a match within 3 days either side:

=IF(COUNTIFS(Books!$C:$C,C2,Books!$A:$A,">="&A2-3,Books!$A:$A,"<="&A2+3)>0,"Matched","Not in books")

Use this as a second pass on the rows that are still unmatched. A wide window can pair a transaction with the wrong one, so check those rows by eye.

8Review and total what's left

Filter column D on each sheet to show only the unmatched rows. Add up each side:

=SUMIFS(C:C,D:D,"Not in books")

The two balances should then agree: the bank's closing balance, plus your items that are not in the bank yet, minus the bank items that are not in your books, equals your book balance. If it doesn't, look for typos in amounts, transposed digits or a missing row.