Why this happens
Most duplicates come from overlapping date ranges. You export March on the 31st while that day’s card payments are still pending, then export again on 2 April starting from the 31st, and every transaction that had already cleared on the 31st is now in both files. Stitch the two together and those rows are doubled.
Others come from the process around the file: a statement uploaded to accounting software for a period that was also arriving through a bank feed, a monthly export downloaded twice and merged, or a copy and paste that grabbed one row too many. Some banks also list a pending card payment and then the settled version of the same payment, sometimes with a slightly different description or date.
The difficulty is that real transactions can be identical too. Two £3.50 coffees from the same café on the same day produce two rows with the same date, description and amount, and if the bank doesn’t export a transaction reference, nothing in the file separates them from a genuine duplicate. Any method of removing duplicates has to answer two questions: what counts as the same transaction, and how will you know what was removed?
Fix it by hand
Find them in Excel first
- 1
Add a helper column that joins the fields identifying a transaction, for example =A2&"|"&B2&"|"&D2 for date, description and amount, or just the reference column if your bank exports one.
- 2
Next to it, count how often each key appears with =COUNTIF(E:E,E2). Anything above 1 is a candidate duplicate.
- 3
Filter the count column for values above 1 and check each group against the bank statement. Delete only the rows that are true copies.
- 4
Compare the SUM of the amount column before and after. The difference should equal the total of the rows you deleted.
Or use Remove Duplicates
- 1
Keep a copy of the original sheet, select the data and choose Data > Remove Duplicates.
- 2
Tick only the columns that identify a transaction. Ticking every column removes only rows that match in every cell; ticking too few removes real repeat payments.
- 3
Excel tells you how many rows it removed but not which ones, so compare against the copy you kept.
Google Sheets has the same command under Data > Data cleanup > Remove duplicates, with the same choice of columns. Both compare cell contents exactly, so a copy whose description has an extra trailing space is not treated as a duplicate.
Fix it with SpreadsheetFix
SpreadsheetFix’s Remove Duplicates rule keeps the first copy of each row and removes later ones. By default a row only counts as a duplicate when every cell matches, which is the cautious choice for bank exports: two coffees with different references or different times stay in the file. If your bank exports a reliable reference or transaction ID, you can use the column picker to compare only that column instead.
Before you export, the preview lists every removed row, and the totals check sets out what happened to the money. For each amount column it shows the total before cleaning, the total of the rows each rule removed, and the total after, and checks that the total before, minus what was removed, equals the total after to the cent. If the removed amount doesn’t match a duplicate you can find on the statement, look again before you import.
Remove Duplicates is a free rule for files up to 5,000 rows, and the file is processed in your browser, so the statement never leaves your computer.
No file to hand?
The same cleaner is on the Remove Duplicates page.
Before and after
date,description,reference,amount 2024-05-01,Office supplies,TX-88213,-64.30 2024-05-02,Client payment Acme,TX-88240,1200.00 2024-05-02,Client payment Acme,TX-88240,1200.00 2024-05-03,Coffee,TX-88251,-3.50 2024-05-03,Coffee,TX-88252,-3.50
date,description,reference,amount 2024-05-01,Office supplies,TX-88213,-64.30 2024-05-02,Client payment Acme,TX-88240,1200.00 2024-05-03,Coffee,TX-88251,-3.50 2024-05-03,Coffee,TX-88252,-3.50
1 exact copy removed. The two coffees have different references, so both stay.
Totals check, amount: 2,328.70 − 1,200.00 removed = 1,128.70
What it won’t catch
- Exact matching won’t catch a pending payment and its settled version if the description or date changed between them. Look for those by amount and review them by hand.
- If two genuine payments are identical in every exported column, nothing in the file can separate them, and exact matching removes the second one. Check the removed rows against the statement; that is what the list of removed rows is for.
- Comparing only some columns is more aggressive. Picking just date and amount, for example, would remove two different suppliers paid the same amount on the same day.
Questions
Why does my bank CSV have duplicate transactions?
Usually because two exports overlapped by a day or more, or because the same period arrived twice, once through a bank feed and once as an uploaded statement. Pending card payments that later settle can also appear twice.
How do I remove duplicates in Excel without deleting real transactions?
Mark candidates with a COUNTIF helper column and review them before deleting anything, or use Data > Remove Duplicates with only the columns that identify a transaction ticked. Keep the original sheet so you can see what was removed.
Which copy is kept?
SpreadsheetFix keeps the first occurrence in the file and removes the later ones, so the original row order is preserved.
How do I know the totals are still right?
The totals check lists, for every amount column, the total before cleaning, the amount each rule removed and the total after, and flags any column where those don’t add up to the cent.