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
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.The second Ben is a different person whose address was mistyped. Change
A8to ben.k@example.com.A rule can also look inside text. Select the plans,
B2:B11, choose Text that contains, type trial, and highlight in Green.Rules are as easy to remove as to add. With
B2:B11selected, click Clear rules from the selected cells at the bottom of the panel.
Remember
- Duplicate values marks everything that appears more than once in the selected cells. Capitals are ignored; stray spaces are not.
- Text that contains finds a word anywhere inside a cell.
- Fixing the data clears the highlight by itself.
- Clear rules removes the formatting and leaves the data alone.
Previous: Highlight what needs attention · Next: Top, bottom and above average