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:
You can see that the names of “Smith” and “Anglina” are repeated twice which I have highlighted manually using the orange 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
On clicking the Duplicate Values, Excel will highlight these values with colors. See the following screenshot:
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
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:
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:
Total 18 unique values remained after deleting the repeating names “Smith” and “Anglina”
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.