Find duplicates
PremiumHighlight the whole row when the customer ID appears more than once in A2:A6. In D2, write the rule with COUNTIF. Lock the whole range and the column in A2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer ID | Name | City | Duplicate? | ||
| 2 | 1001 | Anne Holm | Aarhus | |||
| 3 | 1002 | Jonas Berg | Odense | |||
| 4 | 1001 | Anne Holm | Aarhus | |||
| 5 | 1003 | Mette Lund | Vejle | |||
| 6 | 1004 | Ali Khan | Aalborg | |||
| 7 | ||||||
| 8 |
Premium lesson
Unlock Conditional formatting and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
COUNTIF($A$2:$A$6,$A2) counts how many times the ID in row 2 appears in the column. 1001 appears twice, so the count is 2, and 2>1 returns TRUE.
Excel also has a ready-made rule under Highlight Cells Rules > Duplicate Values. It only colors the cells in the column you selected. With this formula you color the whole row, and you decide what counts as a duplicate.
Common mistake: Typing >=1, which is TRUE for every row because every ID appears at least once.
The answer
- D2
=COUNTIF($A$2:$A$6,$A2)>1