How to find and delete the duplicate values in Excel?

Excel has made the task quite easy for all of us. It is often difficult to find the duplicate values from the list manually and it is a very time-consuming process.

Excel can do this task very effectively and efficiently and that too very fast.
​

Finding the duplicate values

Let us explore this function with some examples:

A teacher has the list of students with some of the names are repeated in the list.

• She is assigned the job to find the duplicate values and submit the correct list.

​
Here is the list of the students with the marks mentioned along with their names:

List of students with duplicate values

You can see that the names of “Smith” and “Anglina” are repeated twice which I have highlighted manually using the maroon color.

But when the list is quite large it becomes difficult and time-consuming to find the duplicate values manually. 

To make this work easy, there is an option in Excel which automatically highlights the duplicate values.

Let us understand the procedure by looking at the following screenshot:
​
Select the list of the students > Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

Finding the duplicate values

On clicking the Duplicate Values, Excel will highlight these values with colors.
​
See the following screenshot:

Highlighting the duplicate values

When you click OK tab shown in the above screenshot, the duplicate values will be highlighted with red color.

​You can click on the drop box and highlight the duplicate values with the color of your choice.
​

Deleting the duplicate values

Remove the duplicate values:

You can free your list from duplicates by permanently deleting the repeating values. Use the following procedure:
​
Select the list > Data > Remove Duplicate

Removing the duplicate values

After clicking on the Remove Duplicates, the new window will open which will give you the option to check one or more columns which contain the duplicate values and uncheck those columns which don’t contain the duplicate values.

Please look at the following screenshot:

Unchecking the column which doesn't contain duplicate values

​After clicking the OK tab, Excel will mention the number of duplicate values and the number of unique values left after deleting the duplicate values:

The final result after hitting the OK button:

The final list after deleting all duplicate values

​Total 18 unique values remained after deleting the repeating names “Smith” and “Anglina”.
​

Conclusion

It is better to save the original list into another spreadsheet before deleting the duplicate values.

This function is very useful when you have a very large list of names containing the duplicate values.

​You can very easily find the duplicate values and delete them within a very short period of time.