Highlight Duplicate Values in Google Sheets

by

Use a conditional formatting rule with COUNTIF to color every repeated value in a selected Google Sheets range.

Use the second rule when you want to leave the first appearance alone and flag only later entries.

Highlight every duplicate value in a column

This example checks project names in A2:A8. Northwind appears three times, Atlas appears twice, and Orion and Beacon appear once.

Google Sheets Format menu with Conditional formatting outlined

Use these steps to highlight every occurrence of a repeated project name.

  1. Select the cells to check. For this example, select A2:A8.
  2. Choose Format > Conditional formatting.
  3. Under Format cells if, choose Custom formula is.
  4. Enter =COUNTIF($A$2:$A$8,A2)>1.
  5. Choose a fill color and click Done.

Sheets highlights A2, A3, A4, A6, and A8. Those cells contain values that occur more than once in the selected range.

Google Sheets conditional formatting sidebar showing a COUNTIF rule that highlights all duplicate project names in A2 through A8
=COUNTIF($A$2:$A$8,A2)>1

COUNTIF counts the current cell’s value in the full range. A count above one makes the conditional-formatting rule true.

The dollar signs keep the tested range fixed. The relative A2 changes to the current row as Sheets evaluates each cell.

Read the Google conditional-formatting guide for the custom-formula control. For the function itself, see the GeoSheets COUNTIF guide.

Highlight only the second and later occurrences

Sometimes the first entry is valid and only repeated entries need attention. Use a growing COUNTIF range for that case.

Use these steps to flag later duplicate entries in the same list.

  1. Select A2:A8 or edit the existing rule for that range.
  2. Choose Custom formula is.
  3. Enter =COUNTIF($A$2:A2,A2)>1, then choose a fill color and click Done.
=COUNTIF($A$2:A2,A2)>1

This time, Sheets highlights A4, A6, and A8. They are the second or later appearances of Northwind and Atlas.

Google Sheets project list with the second and later Northwind and Atlas entries highlighted

The first reference, $A$2, stays at the first data cell. The second reference expands to the row that Sheets is currently checking.

Adapt the formula to your range

Change both range references when your data starts elsewhere. Keep the first relative cell in the formula matched to the first cell in the applied range.

Applied range Highlight every duplicate Highlight later duplicates
B5:B100 =COUNTIF($B$5:$B$100,B5)>1 =COUNTIF($B$5:B5,B5)>1
D2:D50 =COUNTIF($D$2:$D$50,D2)>1 =COUNTIF($D$2:D2,D2)>1

The fixed portions are absolute references. They stop the full test range from moving as the rule checks each row.

What the two formulas include

The all-duplicates rule highlights every copy of a repeated value. It is useful when you want to review the complete set.

The later-occurrences rule leaves the first copy unfilled. It is useful when the first record is the one to keep.

COUNTIF text matching is case-insensitive, according to Google’s COUNTIF reference. Values that differ only by letter case can therefore be treated as duplicates.

To format a whole row when a value in one column meets a condition, use the related guide on highlighting a row based on another cell.

Other Google Sheets articles you may also like

Leave a Comment