How to Highlight Duplicates in Excel – Conditional Formatting

2026-08-07

  • Excel
  • duplicates
  • conditional formatting
  • highlight
  • tutorial

Highlight duplicates (keep all rows)

How to highlight duplicates in excel without deleting them:

  1. Select the column or range (include header only if you want it checked too—usually start at row 2).
  2. Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
  3. Choose a fill color → OK.

Every value that appears more than once is highlighted. Unique values stay plain.

Highlight only the 2nd+ occurrence

Use a formula rule (select A2:A1000, assume data in column A):

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

Conditional Formatting → New Rule → Use a formula. This marks repeats after the first, similar to “keep first” logic.

Highlight vs remove

Goal Method
See duplicates visually Conditional Formatting (this guide)
Delete extras / preview groups Remove duplicates online
Desktop delete Data → Remove Duplicates
Count unique IDs Count unique values

Full workflow: Remove duplicates guide.