Reconcile Bank Statements in Excel and Review Duplicates

What you need

You need the bank statement for the period, with its opening and closing balances, and your own record of the same account for the same dates: a cash book, a ledger export from your accounting software, or a spreadsheet you keep. You also need the list of items left open from last month’s reconciliation, because outstanding checks and deposits in transit often clear in the following month.

This guide covers the spreadsheet method. For the concepts behind it, such as why timing differences happen and which side each adjustment belongs on, read the general guide to reconciling a bank statement first.

A worked example

This example is fictional. The statement for March shows an opening balance of 5,000.00, credits of 3,200.00, debits of 2,450.00 and a closing balance of 5,750.00. The check passes: 5,000.00 plus 3,200.00 minus 2,450.00 is 5,750.00.

The ledger shows 5,365.00 on 31 March. Matching leaves four unmatched items. A customer deposit of 600.00 was recorded in the ledger on 31 March but reached the bank in April, so it is a deposit in transit. A check for 1,020.00 was written in March and not yet cashed, so it is outstanding. The bank charged a 40.00 fee and paid 5.00 of interest, and neither is in the ledger yet.

Bank side: 5,750.00 plus 600.00 minus 1,020.00 gives 5,330.00. Book side: 5,365.00 plus 5.00 minus 40.00 gives 5,330.00. The difference is 0.00. Record the fee and the interest in the ledger, and carry the deposit and the check forward to April’s reconciliation.

Find, highlight and count duplicate candidates without deleting payments

Use a review copy with A Date, B Description, C Signed amount and D Source ID. In E2 enter =IF(A2="","",COUNTIFS($A$2:$A$500,A2,$C$2:$C$500,C2)) and fill down to row 500 or the actual last row. E greater than 1 counts rows sharing a date and amount, including the current row; it does not count proven duplicates. Increase the bounds if the file is longer.

To highlight the candidate count, select E2:E500, choose Home, Conditional Formatting, Highlight Cells Rules, Greater Than, and enter 1. Choose a visible fill. Filter E to values greater than 1 for a review queue. With E populated by the formula above, =COUNT(E2:E500)-COUNTIF(E2:E500,1) counts candidate rows and ignores blanks. A pair contributes two rows, not one confirmed extra payment.

• Fictional October 8 payments: -100 with source ID PAY-A and -100 with source ID PAY-B. E returns 2 for each, but the two receipts and separate statement references prove both are genuine. Keep both.
• Overlapping exports: an October 31 transaction with reference PAY-C appears again in a November download whose selected range begins October 30. Confirm the account, reference, amount, status and original statement row before excluding the second imported copy. Do not delete November’s actual activity merely because October contains the same amount.
• Confirmed accidental import: retain the raw files, record which source ID was imported twice and exclude only its repeated import occurrence in a working copy. Data, Remove Duplicates is appropriate only after the chosen comparison fields have been shown to identify the same transaction; preserve the review log and reconcile counts and totals afterward.

A match candidate is not a one-to-one match

COUNTIFS against the ledger counts candidates; it does not reserve a ledger row after use. XLOOKUP on amount returns only the first occurrence. Two bank payments of $100 can both point to one ledger payment if you treat these formulas as final matches. Record an explicit bank-row-to-ledger-row reference for each accepted match and investigate any reused reference. A date mismatch can also reflect posting timing rather than an error.

When it will not balance

Work through the likely causes in this order, because the early ones are the quickest to rule out.

• The statement itself does not add up: a missing page, a row read with the wrong sign, or a wrapped description split into two rows. Recheck the running balance against the printed closing balance.
• A sign is reversed: a difference equal to twice one transaction usually means a debit was entered as a credit, or the other way round.
• Two digits are swapped: a difference that divides evenly by 9 often points to a transposition, such as 54.00 entered as 45.00.
• A duplicate: a COUNTIFS result of 2 or more on either sheet. Excel’s Remove Duplicates tool deletes rows permanently, so use it only on a copy, and only after you confirm the rows really are duplicates and not two genuine payments of the same amount on the same day.
• A date mismatch: the ledger date and the bank date differ by a day or two, so COUNTIFS on date and amount returns 0. Match on amount alone for those rows and check the dates by eye.
• An item carried from last month: an outstanding check from a previous period that has now cleared, or one that is still open.

A monthly checklist

Keep the workbook from month to month and run the same checks each time.

• The statement covers the full period with every page present.
• Opening balance matches last month’s closing balance.
• Opening balance plus the sum of signed amounts equals the printed closing balance.
• Every bank row has a Matched result, and every 0 is explained.
• Every ledger row the bank has not processed is listed as in transit or outstanding.
• Bank-only items such as fees and interest are recorded in the ledger.
• The difference on the Summary sheet is 0.00.
• Open items are copied to next month’s workbook.