Remove duplicate values is one of the most basic tasks in Excel.
- You can do it from the ribbon
- Or by formula.
Remove Duplicates from the Ribbon
You can remove duplicate values from the ribbon with this menu Data > Remove Duplicates.
How to proceed?
To extract values without duplicate, you follow these 2 steps:
- Select the column you want to remove duplicates in (including the headers)
- Click on the menu Remove Duplicates
In the next dialog box, indicate if the first row of your selection is the header of your column. If so, this row will not be included in the process to remove duplicates.
Click on OK. The process to remove the duplicates starts immediately.
At the end of processing, a dialog box shows you the number of rows deleted and the number of unique values remains.
Remove duplicate on multi-columns Excel
This tool is really impressive because it also works if you select more than 1 column. For instance, in this exercise, you want to extract the unique Last Name + First name.
- Select your 2 columns
- Activate the tool Remove duplicates
- Check the option My data has headers
- Click OK
The result is:
As you can notice, this time the tool hasn't removed Bill and Jon because the last name is different
Remove duplicates with a formula
If you work with Microsoft 365 or Excel Online, you can return a list of data without duplicate with the UNIQUE function
But in some situations, you can not use this tool. It's not because there is a bug but because the data are too complex, like in this example.
04/10/2020 @ 09:25
Duplicate values can be annoying many of the times. As I work on excel regularly and seldom spending find few duplicate cells or vale kills a lot of time. This tutorial is handy to remove all the duplicates at once and be more productive. Thanks!
23/08/2020 @ 13:33
Hi, but this is not working for me. what am i missing
I have the name in a column, with phone numbers in b,c,d,e f columns.
if i do as in the instructions in this tutorial, the net reult i get is no duplicates found.
the phone numbers you could take it as ten digit mobile numbers