How to highlight duplicates in Google Sheets
Google Sheets can highlight duplicates with a custom conditional-formatting formula, but the right formula changes when you need case handling, whitespace cleanup, whole-row matching, or only later occurrences. Here are the practical options.
Published and reviewed August 3, 2026 · By Codeprint
The short answer
Select your data, open Format › Conditional formatting, choose “Custom formula is,” and enter a COUNTIF formula appropriate for the first cell in the selected range. For a one-column range beginning at A2, a common formula is =COUNTIF($A$2:$A,A2)>1.
If you do not want to maintain formulas, Duplicate Highlighter for Google Sheets builds a native rule from plain-language options and can also match duplicate rows.
Four steps with Duplicate Highlighter
- Select one continuous range. Include the columns you want checked and, when possible, leave the header row outside the selection.
- Choose values or duplicate rows. Use per-column values for repeated emails or IDs, whole-range values for repeats anywhere, or row keys for records that match on selected columns.
- Normalize only what makes sense. Ignore blanks, trim surrounding whitespace, and optionally ignore case. Keep type distinctions when 001 and 1 mean different things.
- Preview and apply. Review the local preview when available, then apply a native conditional-formatting rule. The rule continues updating as the sheet changes.
Duplicate cells versus duplicate rows
A duplicate cell means the value appears more than once under the selected comparison mode. A duplicate row means selected key columns match another row. For a contact list, email and phone might define a duplicate while name formatting and notes are allowed to differ.
Why duplicates sometimes look identical but do not match
- One value has leading or trailing spaces.
- Capitalization differs and matching is case-sensitive.
- One cell is text while another is numeric.
- A formula displays an empty string rather than a truly blank cell.
- Dates or error values have different underlying types.
Choose normalization deliberately. Automatic fuzzy matching can merge records that are actually distinct, which is why Duplicate Highlighter does not use AI or fuzzy matching in version 1.
Do highlights keep updating?
Yes. Both a manually created conditional-formatting formula and a rule created by Duplicate Highlighter remain native Google Sheets rules. New or edited cells are evaluated by Sheets without keeping the add-on open.
What about very large sheets?
Applying a rule does not require the add-on to read every selected cell, so Duplicate Highlighter does not impose its own row cap. Google Sheets and Apps Script limits still apply. Preview may be unavailable for a selection that cannot be scanned within an Apps Script execution window, while Apply can remain available.
Next step
See the complete matching workflow, compare the free and Pro plans, or review troubleshooting answers.