To highlight duplicate transactions in Google Sheets, select your data, open Format, then Conditional formatting, choose Custom formula is, and enter a COUNTIFS test that compares date, merchant and amount together. Only rows that repeat on every one of those fields get coloured, which avoids the false alarms that a single-column duplicate rule creates.
Why not use the simple single-column rule?
The quick approach is a custom formula such as =COUNTIF(B:B, B2)>1 on the merchant column. That colours every row for a merchant who appears more than once, which in an expense sheet is nearly everything. You want a rule that lights up a row only when the whole transaction repeats.
What are the steps?
Assume your sheet has Date in column A, Merchant in column B and Amount in column C, with entries from row 2.
- Select A2:C500, the whole block of data and not just one column.
- Open Format, Conditional formatting. The sidebar appears.
- Under Format rules, change the dropdown from Is empty to Custom formula is.
- Enter the formula below.
- Choose a fill colour and click Done.
The formula is:
=AND($A2<>"", COUNTIFS($A$2:$A$500, $A2, $B$2:$B$500, $B2, $C$2:$C$500, $C2)>1)
Look at the mix of references. The ranges are locked with dollar signs on both the column and the row, so they always cover the whole data block. The criteria use $A2, $B2 and $C2, with the column locked but the row free, so each row tests itself. The AND with $A2<>"" stops blank rows from being counted as duplicates of each other.
How do you highlight only the second copy?
If you would like the first occurrence to stay plain and the repeat to be coloured, make the range expand as it goes down:
=AND($A2<>"", COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2)>1)
Row 2 counts only itself, so it is never coloured. A later row that matches an earlier one counts two and turns coloured. This is handy when you are about to delete the extras.
What does it do to a sample range?
| Date | Merchant | Amount | Highlighted by the all-fields rule? |
|---|---|---|---|
| 02-Nov | Grocery | ₹640 | No |
| 02-Nov | Grocery | ₹420 | No, amount differs |
| 03-Nov | Fuel | ₹1,500 | Yes |
| 03-Nov | Fuel | ₹1,500 | Yes |
| 05-Nov | Grocery | ₹640 | No, date differs |
The grocery rows share a merchant and two share an amount, yet none is coloured because no two agree on all three fields. The two fuel rows agree on everything, so both light up.
Why is the rule colouring nothing, or everything?
- Nothing is coloured: check that the formula refers to the first row of the applied range. If the range starts at A2, use $A2, not $A1.
- Everything is coloured: you may have left the ranges without dollar signs, so they slide down with each row.
- Dates look the same but do not match: one may be a date and the other text. Click both cells and compare how they align.
- Merchants look the same but differ: trailing spaces. Clean the column with =TRIM(B2) in a spare column.
If several people add rows to the same sheet, apply the rule to a generous range such as A2:C1000 so that new rows are covered without editing it. Formatting also travels with pasted cells, so use Paste special, Values only when you bring in a new statement. Otherwise stray rules multiply and the sheet slows down.
Are there other ways to clean duplicates in Sheets?
Yes. Data, Data cleanup, Remove duplicates lets you tick the columns that count towards a match, and removes the extra rows in one go. Copy the tab first because the removal cannot be selectively undone later. Alternatively, a formula such as =UNIQUE(A2:C500) in a new tab returns only the distinct rows and leaves the original untouched. When you pick the columns, include every field that makes a payment unique, or you will delete real repeats.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

