How to Highlight Duplicates in Google Sheets

Step by step

1. Select the range — click the column letter for a whole column.
2. Format → Conditional formatting.
3. Under "Format rules", change Format cells if… to Custom formula is.
4. Enter =COUNTIF($A$1:$A, A1)>1, matching the column you selected.
5. Choose a fill colour under Formatting style.
6. Click Done.

Sheets applies the formula to the first cell of the range and repeats it down, adjusting relative references as it goes. Google's conditional formatting documentation covers the rule types in full.

Why the dollar signs are the whole trick

This is where nearly every failed attempt goes wrong, and understanding it takes thirty seconds.

The formula is written once for the top cell and then applied to every cell below, with relative references shifting down.

Cell	Rule becomes	Correct?
A1	COUNTIF($A$1:$A, A1)>1	Yes
A2	COUNTIF($A$1:$A, A2)>1	Yes — range stayed put
A3	COUNTIF($A$1:$A, A3)>1	Yes

Now the same thing without the $:

Cell	Rule becomes	Correct?
A1	COUNTIF(A1:A, A1)>1	Yes
A2	COUNTIF(A2:A, A2)>1	No — no longer searches A1
A3	COUNTIF(A3:A, A3)>1	No — misses A1 and A2

The search range shrinks as it moves down, so each cell only looks at itself and what follows. Earlier duplicates go unnoticed, and the result looks random rather than obviously broken.

Lock the range, leave the compared cell relative. That is the rule for every conditional formatting formula, not just this one.

Useful variations

Highlight only the repeats, not the first occurrence

=COUNTIF($A$1:A1, A1)>1

Note the mixed reference: the range starts anchored at $A$1 but ends at the current row, so it only counts occurrences above. The first instance stays unhighlighted and every subsequent one is flagged — better when you intend to delete the extras and keep one.

Highlight the whole row when a column repeats

Select the full data range — say A1:F100 — and use:

=COUNTIF($A$1:$A, $A1)>1

Both parts of $A1 matter: the $ before A locks the column so every cell in the row tests column A, while the row number stays relative so it moves down.

Duplicates across two columns

=COUNTIFS($A$1:$A, $A1, $B$1:$B, $B1)>1

Flags rows where the combination of A and B repeats — first and last name together, for instance.

Case-sensitive duplicates

COUNTIF ignores case, so "SMITH" and "smith" count as the same. When case matters:

=SUMPRODUCT(--EXACT($A$1:$A$1000, A1))>1

EXACT compares case-sensitively. This needs a bounded range rather than an open-ended one.

When it highlights nothing

If the rule runs but nothing lights up, the values are not identical to Sheets even though they look identical to you:

• Trailing spaces. "Smith " and "Smith" are different. Test with =LEN(A1) on two that look the same.
• Numbers stored as text. 1234 and "1234" do not match.
• Non-breaking spaces from a web paste — character 160, invisible and not removed by TRIM alone.

Clean the column first with =TRIM(SUBSTITUTE(A1, CHAR(160), " ")), paste the result back as values, then re-apply the rule. The same causes break lookups — see why VLOOKUP cannot find a match that exists.

Removing duplicates rather than highlighting them

Highlighting shows you what is there; these remove them:

• Data → Data cleanup → Remove duplicates. Deletes rows in place, keeping the first of each. Irreversible once saved, so copy the sheet first.
• =UNIQUE(A1:A100) outputs a de-duplicated list to a new location, leaving the original untouched. Safer, and it updates automatically as the source changes.

Highlight first, check what has been flagged, then remove. Deleting before looking is how legitimate near-duplicates get lost.

To stop duplicates and typos being entered in the first place, how to make a drop-down list in Google Sheets covers data validation and restricting what people can type.

The same anchoring idea drives conditional formatting in Excel: Excel conditional formatting based on another cell covers highlighting whole rows and why $B2 works where B2 does not.

Frequently asked questions

How do I highlight duplicates in Google Sheets?

Select the column, then Format → Conditional formatting, set "Format cells if" to "Custom formula is", and enter =COUNTIF($A$1:$A, A1)>1. Choose a colour and click Done.

Why is my duplicate highlighting not working?

Usually missing $ signs — without them the search range shifts down with each row and stops covering earlier values. Lock the range as $A$1:$A and leave the compared cell relative.

How do I highlight duplicates but not the first occurrence?

Use =COUNTIF($A$1:A1, A1)>1. The range ends at the current row, so it counts only earlier occurrences and leaves the first instance unhighlighted.

How do I highlight an entire row when a value repeats?

Select the full data range and use =COUNTIF($A$1:$A, $A1)>1. The $ before the column letter makes every cell in the row test column A.

Why does it highlight nothing when I can see duplicates?

The values are not identical to Sheets — usually trailing spaces, numbers stored as text, or non-breaking spaces from a web paste. Clean with =TRIM(SUBSTITUTE(A1,CHAR(160)," ")) and try again.

Is COUNTIF case-sensitive?

No — "SMITH" and "smith" are treated as the same value. For case-sensitive matching use =SUMPRODUCT(--EXACT($A$1:$A$1000, A1))>1 with a bounded range.

How do I remove duplicates instead?

Data → Data cleanup → Remove duplicates deletes them in place, keeping the first of each. =UNIQUE(A1:A100) produces a clean list elsewhere without touching the original, which is the safer option.

Conclusion

=COUNTIF($A$1:$A, A1)>1 under Custom formula is, with the dollar signs exactly there. Locked range, relative comparison cell — that pattern is what makes every conditional formatting formula behave.

If it highlights nothing, the problem is the data rather than the rule: trailing spaces and text-formatted numbers are the usual culprits, and they break lookups the same way.

Related: merging cells in Google Sheets covers formatting pitfalls, and removing duplicates in Excel covers the equivalent there.