How to Highlight Duplicates in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 6 min read · includes 2 free exercises

In short: Select the range, open Format > Conditional formatting, choose Custom formula is and enter =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:

  1. Select the cells you want to format.
  2. Click Format, then Conditional formatting. A toolbar opens on the right.
  3. Under "Format cells if", choose Custom formula is.
  4. Enter your formula in the Value or formula box and choose the formatting.
  5. Click Done.

The example

Here is a list of names in cells A1:A6. Ann and Ben each appear twice.

A
1Name
2Ann
3Ben
4Ann
5Cara
6Ben

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

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.

Hint: lock the whole range with dollar signs.

Exercise 2: Only the repeats

Write the custom formula that highlights only the second and later occurrences, for a rule written in A2.

Hint: lock the start of the range only.

What to learn next

Syntax and rules in this guide were checked against Google's Highlight duplicates help page. See all guides.