Excel's Conditional Formatting can colour every repeated entry for you: its Duplicate Values rule sits on the Home tab. Pick a colour and the matches light up while your list stays exactly as it was. Keep Remove Duplicates, on the Data tab, for when you're sure the extra copies should go for good.
Before you start checking for repeats
Duplicates creep in whenever lists are merged, imported or typed up by more than one person. Typical jobs include checking a sign-up list before a mailing, spotting an order number entered twice, or making sure each customer appears once in a contact sheet. A minute of prep stops a confusing result later, so run through these first:
- Select the cells you want checked before opening any menu.
- Matching follows what each cell shows, not what's stored underneath.
- Outlines and subtotals must come off before any removal.
- Highlight or filter before deleting, as Microsoft itself suggests.
Highlighting duplicate values in a few clicks
- Select the cells to check.
- On the Home tab, find the Style group.
- Click the arrow beside Conditional Formatting.
- Point to Highlight Cells Rules.
- Pick Duplicate Values.
- Set the values and a highlight format.
Highlighting is the gentle option because it only changes how cells look. Think of a mailing list merged from two exports: you can scan the coloured names, spot the people who signed up twice, and decide case by case before anything is deleted. When colour is all you need, the walkthrough on highlighting duplicates in your own colours goes further.
The Advanced filter route suits a different moment: you need a tidy view for a report today, yet want the full list intact tomorrow. It hides the repeats rather than colouring them:
- Select Data > Sort & Filter > Advanced.
- Tick Unique records only.
Clear the filter afterwards and every row returns untouched. Picture a stock list where the same product code was keyed in on three different days: the filtered view shows that code once, which is handy for a quick headcount of products without editing the sheet.
What happens to your data afterwards
The three approaches part ways here. A highlight leaves every cell where it was. The Unique records filter only tucks repeats out of sight for now; clear the filter and they're back. Remove Duplicates is the permanent one: the repeats are deleted, not hidden.
When you run it from Data, then Data Tools, Excel keeps the first time a value turns up and deletes the identical ones further down. So if the version you'd rather keep sits lower in the list, sort it to the top before you start.
Ticking only some columns needs care. Those columns together become the match key, yet a match wipes out the entire row, including columns you never ticked. If two customer rows share an email address but hold different phone numbers, one phone number disappears with its row. The guide to removing duplicates for good walks through that delete step in more depth.
Reports with subtotals need one extra job first. Removal won't run on data that's outlined or subtotalled, so take the outline and subtotals off, clean the list, then add them back once the repeats have gone.
For a big clean-up, a safe order looks like this: copy the sheet as a safety net, colour the repeats, scan them, filter to unique records to preview the tidy version, and only then run the removal. Each stage shows you something before anything becomes permanent.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
The date trap that hides real duplicates
Here's the catch most people miss. Microsoft's guide to unique and duplicate values explains that the comparison runs on what a cell displays, not the value stored inside it. A date shown as 3/8/2006 and the same date shown as Mar 8, 2006 are treated as two unique entries, even though they're the same day.
Mixed formats tend to creep in when data is pasted from different places, an export here, a sign-up form there. Two cells holding the same thing can slip through because they look different on screen. Before trusting a clean result, give the column one consistent format so like is compared with like.
If you suspect a mismatch, select the whole column, apply one format to every cell in it, then run the highlight again. Pairs that were hiding behind different displays tend to show up straight away. Check flagged pairs before deleting anything.
Frequently asked questions
Why can't I flag repeats in PivotTable values?
Excel won't apply a unique or duplicate format to PivotTable Values fields, so that part of a report is off limits for this rule.
Does the rule work on a formatted table as well as a plain range?
Yes. Formatted tables and plain ranges both take the rule, and so does a PivotTable report, apart from its Values area.
Can I check one column instead of the whole sheet?
Yes. Highlight just that column before you open Conditional Formatting, and only those cells get compared.
Can the repeats show up in a colour I choose?
Yes. Setting up the Duplicate Values rule includes picking the format used for the highlight, so choose whatever stands out against your sheet.
Is there a way back after Remove Duplicates?
Treat it as permanent, because the repeats are deleted rather than hidden. Keep a copy of the sheet from before the clean-up so you've got the original to return to.
