How to reconcile a bank statement in Excel

Start from a clean CSV

Export your statement as MT940 or CAMT.053 and convert it to CSV with MT2CAMT. You get real dates, numeric amounts and one consistent layout — no re-typing.

The three-way check

1) Sum the debit and credit columns and confirm they match your bank's opening and closing balances (MT2CAMT already runs this balance check before you download).

2) Use SUMIFS on description/counterparty against your own records to flag unmatched transactions.

3) Use a COUNTIF on your reference column to spot duplicates — MT2CAMT warns about identical value date + amount + reference groups during conversion.