PlayInfoBox

Home /Utilities /How to Highlight Duplicate Values in Excel with Conditional Formatting

How to Highlight Duplicate Values in Excel with Conditional Formatting

Tap a step to see how to highlight duplicate values before deciding what to do with them.

  1. Select the range to check

    Select the cell range where you want duplicate values found and highlighted.

  2. Click "Home > Conditional Formatting"

    On the "Home" tab, click "Conditional Formatting."

  3. Choose "Highlight Cells Rules > Duplicate Values"

    Click "Highlight Cells Rules" in the menu, then select "Duplicate Values."

  4. Choose a highlight style

    Pick a formatting style, such as red text or a yellow fill, and click OK.

  5. Why is this useful?

    It lets you visually confirm which values are duplicated before deleting anything, so you can review the data safely without losing information by mistake.

See Before You Delete

Deleting duplicate rows outright can feel risky, especially in data you do not fully trust yet. Highlighting duplicates first lets you see exactly which values repeat and where, so you can make an informed decision instead of guessing.

For Permanent Removal, Go One Step Further

If you decide the highlighted duplicates really should go, it is worth also learning how to find and remove duplicate values outright β€” between highlighting and removing, you can handle almost any duplicate-data cleanup without writing a single formula.

Frequently Asked Questions

Can it highlight only the second occurrence, not the original entry?

The default rule highlights every duplicate occurrence equally. To distinguish the first occurrence from later ones, you need a custom rule built with a COUNTIF formula.

Can this check for duplicates across multiple columns?

The built-in rule only detects duplicates within a single selected range. To check whether a combination of values across several columns repeats, you need to build a separate custom formula rule.