Answers / Question 9

Highlight the whole row when one column has a duplicate (Google Sheets)

One conditional formatting formula colors the entire row whenever its value in a key column appears more than once. Variants for repeats only and two columns.

The problem

You'd like every row whose ID or email also appears elsewhere to stand out in full, not just the one cell, and you'd rather not add a helper column to make it happen.

Inspired by a public question on Reddit (r/googlesheets). The problem above is described in our own words and the solution below is ours.

The fix

Select the whole table, for example A2:Z.
Format → Conditional formatting, choose Custom formula is.
Enter the formula below (written for row 2, the first row of the range) and pick a color.

Whole row, based on duplicates in column BFormula

=COUNTIF($B$2:$B, $B2)>1

The $ in front of B locks the column, so every cell in a row looks at that row's B value; leaving the row number unlocked lets the rule move down row by row.

Variations

Only the 2nd, 3rd... copies (first one stays plain)Formula

=COUNTIF($B$2:$B2, $B2)>1

Duplicates of A and B togetherFormula

=COUNTIFS($A$2:$A, $A2, $B$2:$B, $B2)>1

Ignore blank cellsFormula

=AND($B2<>"", COUNTIF($B$2:$B, $B2)>1)

Good to know

COUNTIF ignores upper/lower case but not extra spaces. Clean stray spaces with Data → Data cleanup → Trim whitespace.
On very large sheets (tens of thousands of rows) open-ended ranges can make scrolling sluggish; limit the range, e.g. $B$2:$B$5000.

Formulas follow documented Google Sheets / Excel behavior; they aren't machine-tested, so try them on a copy first. Always try new automations on a copy of your sheet first.