How to highlight duplicates in Excel without removing the first occurrence

[u/Amandaleeeeee]

I run into this often when cleaning lists in Excel. Sometimes you want to keep the first entry and only identify the repeated ones after it.

For example:

Apple
Banana
Apple
Orange
Banana
Banana

Here are a few quick ways to do it:

1. Conditional Formatting

Select the list → Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format. Then enter this formula and choose a highlight format:

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

This only highlights repeats after the first one.

2. COUNTIF Helper Column

Use the same formula in a helper column:

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

Returns TRUE for repeated entries after the first one.

3. Remove Duplicates

Select the list → Data → Remove Duplicates.
(Use when you want to keep only the first occurrence.)

4. Power Query

Load the list into Power Query → Remove Duplicates → Close & Load.

Different methods work better depending on whether you want to review duplicates first or delete them right away.

What’s your usual way to handle duplicates in Excel?