How to Highlight Duplicates in Google Sheets (Every Copy, Repeats Only or Whole Rows)

Google Sheets doesn’t have a one-click “highlight duplicates” option, but a conditional formatting rule with a short COUNTIF formula does the job. The formula decides what gets colored: every copy of a value, only the second and later copies, or whole rows. We applied each rule to a test sheet and checked which cells were colored.

The examples use the same contact list as our remove duplicates guide: names in A2:A13, with email, city and amount in B to D. “Ana Lopez” appears three times (once in Seville instead of Madrid) and once more as “ana lopez”; “Cara Diaz” and “Dev Patel” appear twice; “Ben Ross” appears once plus once as “Ben Ross ” with a trailing space; row 10 is empty.

Tested in Google Sheets. We created these rules on November 14, 2026 and checked the colored cells in a PDF export of the sheet. How we test

How to add the rule

  1. Select the range to color, for example A2:D13.
  2. Open Format > Conditional formatting.
  3. Under “Format cells if…”, choose Custom formula is and paste the formula.
  4. Pick a fill color and click Done.

The formula is written for the first row of the range (row 2). Google Sheets adjusts it for every other row, which is why the dollar signs matter. Our custom formula guide explains them in detail.

1. Every copy of a duplicate, whole row

Range A2:D13, formula:

=COUNTIF($A$2:$A$13, $A2)>1

Rows 2, 4, 5, 6, 8, 9, 11 and 13 were colored across all four columns: every row whose name appears more than once. Rows 3 (Ben Ross), 7 (“Ben Ross “), 10 (empty) and 12 (Eli Stone) stayed white.

  • $A$2:$A$13 is fully locked, so every row counts against the same list.
  • $A2 locks the column but not the row. Each row checks its own name, and B, C and D are colored based on column A. Without the $ before A, the reference would shift to B2, C2 and D2 in the other columns, and each one would be checked against the list of names.

2. Only the repeats, not the first copy

To leave the first appearance white and color only the later ones, count from the top down to the current row:

=COUNTIF($A$2:$A2, $A2)>1

We applied it to a column next to the data, E2:E13, and rows 4, 5, 9, 11 and 13 were colored. Row 2, the first Ana Lopez, stayed white. The range $A$2:$A2 grows by one row each time: it starts fixed at A2 and ends at the current row. These are the rows you would delete to keep one copy of each name.

Found in testing: row 4, “ana lopez” in lower case, was colored by both rules. COUNTIF ignores upper and lower case, so it counts “ana lopez” as a fourth Ana Lopez. But “Ben Ross ” with a trailing space was never colored: to COUNTIF, an extra space makes it a different value. A highlighted list can still hide duplicates, and it can flag rows you may consider different.

3. Only rows where every column matches

Rule 1 colored Ana Lopez in Seville because only the name was compared. To color a row only when the whole row is repeated, add each column to COUNTIFS:

=COUNTIFS($A$2:$A$13, $A2, $B$2:$B$13, $B2, $C$2:$C$13, $C2, $D$2:$D$13, $D2)>1

As a helper formula next to each row, this returned TRUE for rows 2, 4, 5, 6, 8, 11 and 13. Seville (row 9) is FALSE now. Row 4 is still TRUE, because “[email protected]” and “[email protected]” also match without regard to case. For repeats only, change each range to the growing form, $A$2:$A2, $B$2:$B2 and so on.

4. Case-sensitive duplicates

If “ana lopez” and “Ana Lopez” should count as different, replace COUNTIF with EXACT, which compares upper and lower case:

=SUMPRODUCT(EXACT($A$2:$A$13, $A2)*1)>1

As a helper formula, it returned TRUE for rows 2, 5, 6, 8, 9, 11 and 13, and FALSE for row 4. For comparison, =A2=A4 returned TRUE and =EXACT(A2, A4) returned FALSE.

Empty cells and trailing spaces

  • Empty cells weren’t colored. For the empty row, COUNTIF($A$2:$A$13, A10) returned 0, so rule 1 left it white without any extra check. That’s different from date rules, where an empty cell counts as day zero; see our custom formula guide.
  • Trailing spaces hide duplicates. =LEN(A7) returned 9 for “Ben Ross “, one more than “Ben Ross”. Trim the data first with Data > Data cleanup > Trim whitespace, as shown in remove duplicates, or see extra spaces that TRIM misses.

Count the duplicates you highlighted

A helper column with =COUNTIF($A$2:$A$13, A2) shows how many times each name appears: 4 for every Ana Lopez row (the lower-case one included), 2 for Cara Diaz and Dev Patel, 1 for the rest and 0 for the empty row. To compare two lists instead of looking within one, see find duplicates between two columns. Our conditional formatting formula generator also builds a duplicates rule and its range for you.

Related guides