How to highlight duplicates in Excel
Colour duplicates with the built-in rule, or with a formula rule that marks whole rows or only repeats.
Quick rule: select the column → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
Whole rows by column A: select A2:D1000 → Conditional Formatting → New Rule → Use a formula:
=COUNTIF($A$2:$A$1000,$A2)>1Only the repeats (first occurrence stays white):
=COUNTIF($A$2:$A2,$A2)>1Two key columns (first + last name):
=COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2)>1Do it in two clicks inside Excel. CleanRows for Excel highlights, deletes or moves duplicates by the columns you pick, ignores case/spaces/accents and compares two sheets. Free up to 1,000 rows. Install CleanRows for Excel →
More guides
- How to remove duplicates in Excel (built-in, formula, add-in)
- How to compare two sheets in Excel for missing rows
- Remove duplicates but keep the last occurrence in Excel
- How to remove duplicates in Google Sheets (3 ways)
- How to highlight duplicates in Google Sheets
- Find duplicates based on two columns in Google Sheets