How to Highlight Duplicates in Excel (Without Deleting Anything)

Duplicates creep into every list I've ever looked after: the same customer added twice from two sign-up forms, an order reference typed in again by mistake, a product code pasted from last season's sheet. Before you delete anything, it's far safer to colour the repeats so you can see them, and Excel's conditional formatting does exactly that in a few clicks.

I'll show you the quick colour rule, a formula version for when you want more control, and how to switch the colouring off again. The key thing to remember is that highlighting changes nothing in your data, while removing duplicates does.

Highlighting duplicate values in Excel with conditional formatting

This is the route Microsoft's help page gives, and it's the one I'd use nine times out of ten.

1. Select the cells you want to check. That can be one column, a few columns or a block of cells.
2. On the Home tab, select Conditional Formatting.
3. Point to Highlight Cells Rules, then select Duplicate Values.
4. In the box next to values with, pick a colour style, such as light red fill with dark red text.
5. Select OK.

Every value that appears more than once in your selection now carries the colour. Both copies light up, not just the second one, which is handy because you can decide which version to keep.

Showing unique values in Excel instead of duplicates

Sometimes you want the opposite: the one-off entries that don't match anything else. In the same Duplicate Values box, the first drop-down lets you switch from Duplicate to Unique. If you'd rather build the rule from scratch, Microsoft's conditional formatting guide lists the steps:

1. Select the cells.
2. Go to Home, Conditional Formatting, then Manage Rules, and select New Rule.
3. Choose Format only unique or duplicate values.
4. In the Format all list, choose unique or duplicate.
5. Select Format, pick a number, font, border or fill style, then select OK twice.

A COUNTIF rule for highlighting repeats in an Excel column

The built-in rule compares everything in your selection with everything else. When you want to check one column against a fixed range, a formula rule gives you more say. Microsoft's own example checks a list in A2:A400 with COUNTIF and a locked, absolute range. The simplest version I'd start with uses the whole column: =COUNTIF(A:A,A2)>1, which means "colour this cell if its value appears more than once in column A".

1. Select A2 down to the end of your list.
2. Go to Conditional Formatting, New Rule, then Use a formula to determine which cells to format.
3. Type =COUNTIF(A:A,A2)>1, swapping A for your own column letter.
4. Select Format, choose a fill colour, then OK.

Because the rule points at the whole column, every row is checked against the full list, while A2 moves down one row at a time. Microsoft's version uses an absolute range instead, which is useful when you only want to check part of a column. If COUNTIF is new to you, my walkthrough on counting cells that match a condition with COUNTIF explains how the function reads its two parts.

Why Excel duplicate highlighting sometimes misses or over-colours cells

If the colours don't look right, it's usually one of these:

• Hidden spaces. "Jo Smith" and "Jo Smith " with a trailing space aren't the same to Excel, so they won't be marked as repeats.
• Wildcards. Microsoft notes that the unique and duplicate rules treat * and ? as wildcards, so entries containing them can be flagged unexpectedly.
• PivotTables. Excel can't highlight duplicates in the Values area of a PivotTable report, so run the rule on the source data instead.
• Wrong selection. The rule only looks at the cells you selected. If you added rows later, edit the rule's range in Manage Rules.

Highlighting duplicates in Excel versus removing them

These two features sit side by side in most guides, but they behave very differently. Here's how I'd explain the difference to anyone on a small team sharing one workbook.

Feature	Where it lives	What it does to your data
Duplicate Values highlight	Home, Conditional Formatting	Adds colour only, nothing is deleted
Remove Duplicates	Data, Remove Duplicates	Deletes repeat rows permanently
COUNTIF formula rule	Conditional Formatting, New Rule	Adds colour based on your own range

Microsoft's help is blunt about Remove Duplicates: the repeated data is permanently deleted. Its advice is to copy the original data to another worksheet before you run it. I'd go further and save a copy of the whole file, especially if it's your client list or order log. Once you've cleared repeats, you may also want to tidy up with a quick sweep for blank rows so the list sorts cleanly.

One more tip from experience: sort the coloured column before you decide what to delete. Repeats then sit next to each other, so it's easy to compare the two rows and keep whichever has the fuller details, like a phone number or a postcode the other copy is missing.

Clearing duplicate highlighting from an Excel sheet

When you've finished reviewing, the colours can stay or go. To take them off:

1. Select the cells with the highlight, or click anywhere on the sheet.
2. Go to Home, then Conditional Formatting, then Clear Rules.
3. Choose Clear Rules from Selected Cells, or Clear Rules from Entire Sheet.

Clearing the rule doesn't touch your values. It only removes the formatting the rule added.

Using Excel duplicate checks in a small business

For creators and small business owners, a few habits make this feature worth the habit. I'd run the highlight on email addresses before importing a list into a newsletter tool, on order references before sending a batch, and on product codes before updating stock. If you also keep a version of your data in Google Sheets, my guide to adding up columns in Google Sheets covers the matching basics there.

Frequently asked questions

How do I highlight duplicates in two columns in Excel?

Select both columns together, then use Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. Excel colours any value that appears more than once across the whole selection.

Does highlighting duplicates delete anything?

No. Conditional formatting only adds colour. Remove Duplicates on the Data tab is the feature that deletes repeated rows, and Microsoft says that deletion is permanent.

Can I highlight only the second copy of a duplicate?

The built-in rule colours every copy. A formula rule with COUNTIF and a range that grows as it moves down the column can be set up for that, but test it on a copy first.

Why won't Excel highlight duplicates in my PivotTable?

Microsoft says Excel can't highlight duplicates in the Values area of a PivotTable report. Apply the rule to the source data instead.

How do I remove duplicate highlighting?

Go to Home, Conditional Formatting, Clear Rules, then choose Clear Rules from Selected Cells or from Entire Sheet. Your values stay as they were.