Highlight Duplicates in Google Sheets

Highlight duplicates in Google Sheets with a formula that works. Type your range, choose what to color, copy the formula and paste it into conditional formatting. Choose the comma or semicolon version, whichever your sheet uses.

Highlight
Separator
=AND(A2<>"",SUMPRODUCT(--($A$2:$A=A2))>1)

If you see #ERROR!, switch the separator.

Large sheet? Use a closed range like A2:A5000 — it's faster.

Apply to range: A2:A

  1. 1. Select A2:A in your sheet.
  2. 2. Open Format → Conditional formatting.
  3. 3. Under Format cells if, choose Custom formula is.
  4. 4. Paste the formula.
  5. 5. Pick a fill color and click Done.

How to add the rule

The first step sets the cells the rule colors. Keep it the same as the range you typed: the formula is written for the top-left cell, and Sheets shifts it down and across for every other cell. The generator fills in the range for you, so the two always match.

Steps two and three open the rule editor and switch it to a custom formula — the built-in conditions can't compare a cell with the rest of the column. The last two steps add the formula and pick how duplicates look. The rule then updates whenever you edit the sheet, so new repeats get colored on their own.

Which formula to use

A range with several columns and Match whole rows turned off highlights any cell whose value appears anywhere in the block.

Why COUNTIF formulas give wrong results

A common suggestion is =COUNTIF($A$2:$A,A2)>1. It has two traps:

The formulas here use SUMPRODUCT with a plain = comparison instead. It has no wildcards and treats blank cells like any other value. The first part of the formula stops empty cells and empty rows from being highlighted.

#ERROR! and semicolons

In many countries numbers are written with a decimal comma (1,5). Sheets in those locales expects a semicolon between function arguments, and a formula with commas shows #ERROR!. It is not converted for you. Switch Separator to semicolon and you get:

=AND(A2<>"";SUMPRODUCT(--($A$2:$A=A2))>1)

To see which one your file uses, open File → Settings and look at Locale. English function names work in every locale. The generator picks the separator from your browser language; the locale of the sheet is what counts.

How to find duplicates in Google Sheets

The quickest way is to highlight them with the generator above — every repeated value gets colored. Choose Repeats only (keep the first one clear) to color only the extra copies, or a range with several columns to find whole duplicate rows. Want a list of the duplicate rows or a clean copy of your data instead? Paste your cells into Remove Duplicates.

Highlight duplicates FAQ

How do I highlight duplicates in Google Sheets?

Select the range, open Format → Conditional formatting, choose Custom formula is and paste a formula such as =AND(A2<>"",SUMPRODUCT(--($A$2:$A=A2))>1). Then choose a fill color. The generator above builds the formula for your range.

How do I find duplicates in Google Sheets?

Highlight them. Select the range, add a conditional formatting rule with Custom formula is and paste the formula from the generator above. Every repeated value gets colored, so the duplicates stand out at a glance.

Why does my duplicate formula show #ERROR!?

Your sheet probably uses a locale with a decimal comma, which needs semicolons between arguments. Switch the separator to semicolon and paste the formula again.

How do I highlight duplicate rows?

Enter a range with several columns, such as A2:C, and keep Match whole rows on. A row is highlighted only when every cell matches another row. Up to 20 columns are supported.

How do I highlight duplicates in two columns?

To color values in one column that also appear in another, enter a one-column range such as A2:A, choose Values that also appear in another range and type the other column under Other range: B2:B on the same sheet, or Sheet2!B2:B on another sheet. You get =AND(A2<>"",SUMPRODUCT(--($B$2:$B=A2))>0).

How do I highlight duplicates but keep the first one?

Choose Repeats only (keep the first one clear). The first copy stays clear and every later copy is highlighted: =AND(A2<>"",SUMPRODUCT(--($A$2:$A2=A2))>1).

Does it work for large sheets?

It can. A rule on an open range like A2:A checks every row of the column and can slow a big sheet down. Use a closed range such as A2:A5000 that covers your data.