Course / Conditional formatting

Find duplicates at a glance

Highlight the entries that appear twice, fix one, find text inside cells, and clear the rules again. Beginner · 4 min · Duplicate values, Text that contains, Clear rules

A list with the same person in it twice sends two emails, counts one sale double, and makes every total wrong. Spotting the repeat by eye in a long list is slow and unreliable.

Duplicate values is a rule that needs no setting at all: it highlights every value that appears more than once in the cells you selected.

It compares what the cells show, and it ignores capitals, so Ana@Example.com and ana@example.com count as the same. It does not ignore a stray space: clean those up with TRIM first.

Steps

  1. Is anyone on this sign-up list twice? Select the emails, A2:A11, click Conditional formatting in the toolbar, choose Duplicate values, and click Apply rule.

  2. The second Ben is a different person whose address was mistyped. Change A8 to ben.k@example.com.

  3. A rule can also look inside text. Select the plans, B2:B11, choose Text that contains, type trial, and highlight in Green.

  4. Rules are as easy to remove as to add. With B2:B11 selected, click Clear rules from the selected cells at the bottom of the panel.

Remember

Previous: Highlight what needs attention · Next: Top, bottom and above average