How to Highlight Duplicates in Google Sheets
=COUNTIF($A$2:$A$6, A2)>1. Every value that appears more than once is highlighted.
The steps
Duplicates are found with a conditional formatting rule that uses a custom formula:
- Select the cells you want to format.
- Click Format, then Conditional formatting. A toolbar opens on the right.
- Under "Format cells if", choose Custom formula is.
- Enter your formula in the Value or formula box and choose the formatting.
- Click Done.
The example
Here is a list of names in cells A1:A6. Ann and Ben each appear twice.
| A | |
|---|---|
| 1 | Name |
| 2 | Ann |
| 3 | Ben |
| 4 | Ann |
| 5 | Cara |
| 6 | Ben |
Highlight every duplicate
Select A2:A6 and use this formula. Google's help page gives the same pattern for finding duplicates:
=COUNTIF($A$2:$A$6, A2) > 1
This highlights Ann and Ben in both places they appear. Here is why it works: Sheets applies the rule to each selected cell in turn. For each one, COUNTIF counts how often that cell's value appears in the whole range. If the count is above 1, the rule is true and the cell is formatted.
Why the dollar signs matter
Google's page says to write the formula for the first cell of your selection, and that references without dollar signs shift for each cell. The range $A$2:$A$6 is locked so every cell counts against the same list. A2 is left relative so it moves to A3, A4 and so on.
Highlight only the repeats
To leave the first occurrence alone and mark only later ones, lock the start of the range and let the end move:
=COUNTIF($A$2:A2, A2) > 1
This highlights the second Ann in A4 and the second Ben in A6. At A4 the range is A2:A4, which contains Ann twice.
Common mistakes
- Missing dollar signs. Without them, the range shifts down with each cell and the count is wrong.
- Writing the formula for the wrong cell. Use the first cell of the selected range.
- Selecting the header. If the header is in the range, it can be formatted too.
Try it yourself
Type your formula into the box, starting with =, then press Check. This is the formula you would enter in the conditional formatting panel.
Exercise 1: Every duplicate
Write the custom formula that highlights every cell whose value appears more than once in A2:A6. The rule is written for A2.
Exercise 2: Only the repeats
Write the custom formula that highlights only the second and later occurrences, for a rule written in A2.
What to learn next
- Remove duplicates: two ways to clean a list.
- COUNTIF: count the rows that match a condition.
- UNIQUE: remove duplicates from a list.
Syntax and rules in this guide were checked against Google's Highlight duplicates help page. See all guides.