Excel has more than one way to deal with duplicates, and the right choice depends on whether you want to delete them, see them first, or produce a clean list without touching the original. Here are four methods and when each is the correct tool.
First: Make a Backup
Two of these methods permanently delete rows. Before you start, copy your sheet (right-click the tab → Move or Copy → check Create a copy) so you always have the original to fall back on. Also decide whether you are removing absolute duplicates (entire rows that match) or partial duplicates (one column, like a repeated email) — that choice changes your settings below.
Method 1: Remove Duplicates (Fast, Permanent)
Best when the data is already reviewed and you just want it cleaned:
- Click any cell inside your data.
- Go to the Data tab → Data Tools group → Remove Duplicates.
- If your data has a header row, check "My data has headers."
- Under Columns, pick your criteria. Leave all columns checked to delete only fully identical rows; to dedupe by one field (like an ID), click Unselect All and check just that column.
- Click OK — Excel reports how many duplicates it removed.
Two things to know: it keeps the first occurrence and deletes later matches, and it is not case-sensitive ("SMITH" and "smith" count as the same).
Method 2: Highlight Duplicates (Non-Destructive)
Use this when you want to review before deleting anything:
- Select the range or column to check.
- On the Home tab, click Conditional Formatting → Highlight Cells Rules → Duplicate Values.
- Pick a color and click OK. Every repeated value is now shaded so you can inspect it.
Nothing is deleted — this only marks the duplicates visually.
Method 3: The UNIQUE Function (Excel 365)
If you have Microsoft 365 or Excel 2021, =UNIQUE() returns a clean list without altering the source. In an empty cell, type =UNIQUE(A2:A100) and Excel spills the de-duplicated values into a new column. Because it is a formula, it updates automatically as the source data changes — ideal for live reports.
Method 4: Advanced Filter (Copy Out Unique Rows)
To pull unique records into a separate location without deleting anything:
- Select your range, then go to Data → Advanced (in the Sort & Filter group).
- Choose Copy to another location, set the "Copy to" cell, and check Unique records only.
- Click OK — a clean, unique copy appears, original untouched.
Which Method Should You Use?
| Goal | Use |
|---|---|
| Quickly clean reviewed data | Remove Duplicates |
| Review before deleting | Conditional Formatting |
| Live list that auto-updates | UNIQUE function |
| Keep original, extract a clean copy | Advanced Filter |
When in doubt, highlight first and delete second — it is the safest habit, especially on data you cannot easily recreate.
Which Method to Use
| Goal | Best method |
|---|---|
| Quickly clean reviewed data | Remove Duplicates (Data tab) |
| Review before deleting | Conditional Formatting → highlight |
| Live list that auto-updates | UNIQUE function (Excel 365) |
| Keep original, extract clean copy | Advanced Filter |
Two Things That Trip People Up
- Remove Duplicates is not case-sensitive. "SMITH" and "smith" are treated as identical.
- It keeps the first occurrence and deletes later matches. If you need to keep the last entry instead, sort the data first.
Frequently Asked Questions
How do I remove duplicates in Excel?
Select a cell in your data, go to the Data tab, click Remove Duplicates, choose which columns define a duplicate, and click OK. Back up your sheet first, since this permanently deletes rows.
How do I find duplicates without deleting them?
Select the range, go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. This shades repeats so you can review them before acting.
Is there a formula to remove duplicates in Excel?
Yes. In Excel 365 or 2021, =UNIQUE(range) returns a de-duplicated list that updates automatically as the source changes.