How to Remove Duplicates in Excel (3 Easy Ways)
Mailing lists, customer records, stock sheets — sooner or later, duplicates creep in. Someone pastes the same data twice, or two lists get merged. Excel has several ways to find and remove them in seconds.
Before you start: make a copy
Remove Duplicates permanently deletes rows. Before you use it, right-click the sheet tab, choose Move or Copy, tick Create a copy and click OK. Now you've got a backup if anything goes wrong.
(You can also press Ctrl + Z straight away to undo — but a copy is safer.)
Method 1: Remove Duplicates (the quickest way)
1. Click any cell inside your data.
2. Go to Data > Remove Duplicates.
3. Make sure My data has headers is ticked if your first row contains headings.
4. Tick the columns Excel should check (see below).
5. Click OK.
2. Go to Data > Remove Duplicates.
3. Make sure My data has headers is ticked if your first row contains headings.
4. Tick the columns Excel should check (see below).
5. Click OK.
Excel tells you how many duplicates were removed and how many unique values remain. It keeps the first occurrence and deletes the rest.
Which columns should I tick?
This is the important part. Excel only treats rows as duplicates if every ticked column matches.
| You tick | What gets removed |
|---|---|
| All columns | Only rows that are completely identical |
| Just Email | Any row with an email address already seen above — even if the name or phone is different |
| First Name + Last Name | Rows with the same full name |
For a mailing list, ticking just the Email column is usually what you want.
Tip: Because Excel keeps the first occurrence, sort your data first if you want to keep a particular version — for example, sort by date (newest first) to keep the most recent record.
Method 2: Highlight duplicates first
Not sure you want to delete anything yet? Highlight duplicates so you can check them.
1. Select the column (or columns) to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values…
3. Click OK.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values…
3. Click OK.
Every repeated value is coloured. To group them together, click the column's filter arrow (Data > Filter), choose Sort by Color, and review them before you delete.
To remove the highlighting afterwards: Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Method 3: The UNIQUE function (keep your original list)
In Microsoft 365 and Excel 2021 or later, the UNIQUE function creates a de-duplicated copy somewhere else, leaving your original data untouched.
1. Click an empty cell with plenty of space below it.
2. Type: ` =UNIQUE(A2:A500) `
3. Press Enter.
2. Type: ` =UNIQUE(A2:A500) `
3. Press Enter.
Every value from A2:A500 is listed once. If the original changes, the list updates automatically.
For whole rows, select more than one column:
=UNIQUE(A2:D500)
Tip: Wrap it in SORT to get an alphabetical list: =SORT(UNIQUE(A2:A500)).
Duplicates that Excel doesn't spot
Sometimes values look the same but aren't — so Excel doesn't count them as duplicates. Common culprits:
• Extra spaces — "Smith" and "Smith " are different. Use =TRIM(A2) in a helper column to clean them up, then copy and Paste Special > Values back.
• Upper and lower case — Remove Duplicates ignores case, so "JOHN" and "john" are treated as duplicates. Just be aware of which one gets kept.
• Numbers stored as text — look for green triangles in the corner of cells, and use the warning icon to Convert to Number.
• Upper and lower case — Remove Duplicates ignores case, so "JOHN" and "john" are treated as duplicates. Just be aware of which one gets kept.
• Numbers stored as text — look for green triangles in the corner of cells, and use the warning icon to Convert to Number.
Wrapping up
Make a copy, then use Data > Remove Duplicates and think carefully about which columns to tick. If you want to check first, highlight duplicates with Conditional Formatting — and if you'd rather not delete anything at all, use =UNIQUE().
