Add a helper column that counts how many rows share the same date, description and amount, then filter for counts above one. In Excel or Google Sheets the formula is =COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,B2,$C$2:$C$500,C2), where A is the date, B the description and C the amount. Anything showing 2 or more is a candidate duplicate, not a proven one.
What does the helper column look like?
Assume the sheet has Date in A, Description in B, Amount in C and the helper in D. In D2 enter the formula above and fill it down.
| Date | Description | Amount | COUNTIFS result |
|---|---|---|---|
| 04-Apr-2026 | Swiggy order | ₹412 | 1 |
| 06-Apr-2026 | Electricity bill | ₹1,860 | 2 |
| 06-Apr-2026 | Electricity bill | ₹1,860 | 2 |
| 09-Apr-2026 | Chai stall | ₹20 | 2 |
| 09-Apr-2026 | Chai stall | ₹20 | 2 |
Both electricity rows show 2, because each row counts itself and its twin. That is why this version cannot tell you which one to delete.
How do you flag only the repeat?
Use a running count instead: in D2 enter =COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2) and fill down. The range starts fixed at row 2 but ends at the current row, so the first appearance gets 1 and the second gets 2. Now you only act on rows with a value above 1, and the first entry of each pair is never touched.
Say a statement was pasted twice by mistake. It had 180 rows, so the sheet now has 360. Every row in the second block shows 2, and filtering D for values above 1 returns exactly 180 rows. A count that does not match the original statement length is a clue that some of the rows were genuine repeats.
How do you highlight duplicates with conditional formatting?
- Select the data rows, for example A2:D500.
- In Excel choose Home, Conditional Formatting, New Rule, Use a formula. In Google Sheets choose Format, Conditional formatting, Custom formula is.
- Enter =$D2>1 and pick a fill colour.
- Click OK. Every repeat row turns that colour.
The dollar sign before D matters. It locks the column so the whole row is judged by column D.
Are all duplicates mistakes?
No, and this is where people lose real records. Two ₹20 chai payments on the same day look identical, as do two autorickshaw rides of ₹60. Before deleting a flagged row ask whether it could be a real second purchase, then compare against your statement. If the statement also shows two, keep both.
A looser test helps catch the same payment posted on different dates, such as a UPI payment that the bank records a day later. This checks for the same description and amount within two days either side: =COUNTIFS($B$2:$B$500,B2,$C$2:$C$500,C2,$A$2:$A$500,">="&A2-2,$A$2:$A$500,"<="&A2+2)>1. It returns TRUE for any row that has a twin within two days, and the twin is flagged too, so treat TRUE as a prompt to look rather than a verdict.
What is the safe way to remove them?
- Save a copy of the file first.
- Turn the formulas into values only if you plan to sort; otherwise leave them.
- Filter the running-count column for values above 1.
- Select the visible filtered rows, right-click and delete the rows, then clear the filter.
- Check that the row count and the total match what the statement says.
In Google Sheets, Data, then Data cleanup, then Remove duplicates also works, but you choose the columns it compares and it deletes without asking for each row, so the helper column route gives you more control.
When a sheet stops being the right tool
If duplicates keep arriving because you paste statements by hand every month, the cause is the process. SpendixAI flags possible duplicate imports and has a report listing the same amount paid to the same merchant within two days, which is the check above without the formulas.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


