Excel: Highlight Duplicates Without Deleting Them

Want to see duplicate values before you remove anything? Conditional Formatting colors every repeated value in a few clicks, and nothing is deleted.

1. Select the data

Select the column or range you want to check, for example A2:A500.

2. Open the duplicate rule

Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

3. Pick a color

Leave the first box on Duplicate, choose a color and click OK. Every value that appears more than once is highlighted. Switch the box to Unique to highlight values that appear only once.

4. Review them together

Turn on Data > Filter, click the column's filter arrow and use Filter by Color to show only the highlighted rows. When you are sure, Data > Remove Duplicates deletes the extra copies.

Data > Filter > Filter by Color

FAQ

Does it check whole rows or single cells?

Single cell values. To find duplicate rows, add a helper column that joins the columns (for example =A2&"|"&B2) and highlight duplicates in that column.

How do I remove the highlighting?

Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.