Business & Productivity · Guide
How to remove duplicates in Excel
Three ways to clear duplicate rows: the Remove Duplicates button for a permanent clean-up, Advanced Filter when you want a copy, and UNIQUE when you want the list to stay live.
The Nextversity teamBusiness & Productivity schoolUpdated August 10, 20266 min read
On this page
The short answer
Select a cell in your data, go to Data > Remove Duplicates, tick the columns that define a duplicate, and click OK. Excel deletes the repeat rows and tells you how many it removed. That is the whole job for most spreadsheets.
There are two other ways worth knowing, because the button is destructive and sometimes you want the original list left alone. Advanced Filter can copy the unique rows somewhere else. The UNIQUE function builds a list that updates itself when the source changes.
Before you press Remove Duplicates, duplicate the sheet. Right-click the tab, choose Move or Copy, tick Create a copy. It takes four seconds and it has saved a lot of Monday mornings.
Method 1: the Remove Duplicates button
This is the fast, permanent one.
- Click any single cell inside your data. Do not select a whole column, or Excel will only compare that column and quietly shred the row alignment.
- Go to the Data tab and click Remove Duplicates.
- Tick My data has headers if row 1 holds your column names.
- Choose which columns define a duplicate. All columns ticked means a row must match on everything. Ticking only Email means anyone with the same email is a duplicate, whatever else differs.
- Click OK. Excel reports how many duplicate values it found and how many unique values remain.
The column choice is the part people get wrong. A customer list where the same person appears with two phone numbers is a duplicate on Email, not a duplicate on all columns. Decide what "the same" means before you open the dialog.
Microsoft's own Excel help covers the dialog in detail if you want the official wording, and Microsoft Learn has free guided modules on the same tools.
Method 2: Advanced Filter, when you want a copy
Use this when the source list is not yours to edit, or when you want the before and after side by side.
- Select your range including headers.
- Go to Data > Advanced in the Sort and Filter group.
- Choose Copy to another location, pick an empty cell as the destination, and tick Unique records only.
- Click OK.
You now have a clean copy and an untouched original. It is a static snapshot, so if the source changes you run it again.
Method 3: the UNIQUE function, when it needs to stay live
If your Excel version supports dynamic arrays (Microsoft 365 and Excel 2021 onward), one formula does it:
=UNIQUE(A2:A500)
That spills a list of the distinct values below the cell you typed it in. Add more columns for whole rows:
=UNIQUE(A2:C500)
The list updates the moment the source data changes, which makes it the right choice for a summary tab or a dashboard. It is also the safest option of the three, because nothing gets deleted.
Check before you delete
Removing duplicates is easy. Removing the wrong ones is also easy, and much harder to notice.
- Look first. Select your data, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Now you can see what Excel is about to remove. Our guide to finding and highlighting duplicates goes deeper on this.
- Watch for invisible differences. "j.smith@work.com " with a trailing space is not a duplicate of "j.smith@work.com". Run TRIM and CLEAN on the column first, or you will remove far fewer rows than you expected.
- Watch for case. Remove Duplicates ignores capitals, so JSMITH and jsmith are the same row to Excel. If case matters to your data, that is a problem worth knowing about before you click.
- Count the rows. Note the row count before and after. If the number that disappeared surprises you, undo and look again.
When you do not need a course for this
If you came here for one answer, take it and get on with your day. This is a five-minute job and a free guide is enough.
A course is for the next problem along: the one where the duplicates exist because two exports have different column orders, or because the data needs a lookup before it can be compared at all. That is where the Excel certificate earns its place, and if you are brand new to spreadsheets the introduction course starts further back. Both are included in one Nextversity subscription, along with everything else in the Business & Productivity school.
Learn the cleaning tools properly and the reward is boring in the best way: data you can trust without checking it twice.
Questions people ask
Where is Remove Duplicates in Excel?
Select any cell in your data, then go to the Data tab and look in the Data Tools group. The button is called Remove Duplicates. In Excel for the web it sits in the same place on the Data tab.
Does Remove Duplicates delete the whole row?
It deletes the whole row, but it only compares the columns you tick in the dialog. If you tick only Email, two rows with the same email are treated as duplicates even when every other column differs, and the lower row is the one that goes.
How do I undo Remove Duplicates?
Press Ctrl+Z (Cmd+Z on Mac) straight away. Once you close and reopen the file, the rows are gone for good, which is why it pays to copy the sheet before you run it.
How do I remove duplicates but keep the first entry?
Remove Duplicates already keeps the first occurrence and deletes the ones below it. If the row you want to keep is not the first, sort the data so your preferred row sits on top before you run the tool.
Can I remove duplicates without deleting anything?
Yes. Use the UNIQUE function in a spare column, or Advanced Filter with copy to another location. Both leave your original list untouched, which is the safer option when someone else owns the source data.