To find duplicate transactions in Excel, add a helper column that uses COUNTIFS to count how many rows share the same date, amount and merchant. Any row that shows a count above 1 is a candidate for a duplicate. The count alone cannot tell a double charge from two genuine purchases, so each flagged row needs a quick human check.
How do you set up the helper column?
Assume a log with Date in column A, Merchant in column B and Amount in column C, data in rows 2 to 500. In D2 enter:
=COUNTIFS($A$2:$A$500, A2, $B$2:$B$500, B2, $C$2:$C$500, C2)
Fill it down. The formula asks, for this row, how many rows have the same date and the same merchant and the same amount. A unique row returns 1. Two matching rows both return 2. In E2, add a readable flag:
=IF(D2>1, "Check", "")
Use Data, Filter on the header row and filter column E to Check to see just the suspects.
How do you mark only the second copy?
The first formula flags both rows in a pair. If you want to see only the later one, which is the one you would delete, use a growing range:
=COUNTIFS($A$2:A2, A2, $B$2:B2, B2, $C$2:C2, C2)
The range starts at row 2 and ends at the current row, so the first copy returns 1 and the second returns 2. Flag with =IF(F2>1, "Possible duplicate", "").
What does it find in a sample?
| Date | Merchant | Amount | Count | Likely story |
|---|---|---|---|---|
| 04-Oct | Cafe | ₹180 | 2 | Two coffees, one for each friend |
| 04-Oct | Cafe | ₹180 | 2 | Same as above |
| 09-Oct | Electricity board | ₹2,340 | 2 | Bill paid twice, worth checking |
| 09-Oct | Electricity board | ₹2,340 | 2 | Same as above |
| 12-Oct | Pharmacy | ₹410 | 1 | Unique |
The formula treats the cafe pair and the electricity pair identically. You decide that two ₹180 coffees on one day are plausible, while two payments of the same utility bill on one day are suspicious.
How do you tell a real repeat from a duplicate?
- Look at the narration or reference. A bank statement often has a UPI or transaction reference. If you add it as a fourth criterion, true double charges with different references stop matching, and importing the same file twice still matches.
- Ask what the merchant does. A cafe, an auto-rickshaw or a chai stall naturally repeats. A one-off bill, subscription or insurance premium usually does not.
- Check the balance column. Two rows that really happened will have different running balances.
- Compare dates against your memory. A payment retried after a failed attempt may appear twice, then reverse later.
Why do obvious duplicates sometimes not match?
COUNTIFS needs identical values. Merchant names with trailing spaces, different capitalisation of the same word and dates with time stamps all stop a match. Create a clean merchant column with =TRIM(B2) and base the count on that. For dates with times, use =INT(A2) in a helper column, which strips the time.
What is the safest way to clean up?
- Copy the sheet before you change anything.
- Filter to the flagged rows and review each pair against the statement.
- Delete only the confirmed duplicates, and delete the second copy.
- Recheck that the monthly total matches the statement total for the same dates.
SpendixAI flags possible duplicate imports and has a report called Possible duplicate charges that lists the same amount paid to the same merchant within two days. It is the same idea as the formula above, done on the statement as it comes in, and you still decide what to keep.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

