MEND MY LADDER
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure

How to find and delete the duplicate values in Excel?

28/3/2019

0 Comments

 
Delete 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
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 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
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 duplicates in Excel
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
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:
Duplicate and unique values
The final result after hitting the OK button:
Deleting duplicate values
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.

0 Comments



Leave a Reply.


    Author

    Hi! I write posts on growing your business


    Categories

    All
    Grow Your Business


    Archives

    August 2021
    July 2021
    April 2021
    March 2021
    February 2021
    May 2019
    April 2019
    March 2019
    February 2019


    Categories

    All
    Grow Your Business


    RSS Feed

Copyright Mend My Ladder @ 2021
Proudly Powered by Weebly
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure