In many tables that you work with, you only want to have unique values in a column. However going through and finding all the duplicates can seem like a daunting task if the table is very large and you don’t know the quicker ways to do this. Excel gives us multiple ways to deal with duplicates. I am going to go over two of these ways. These are quite easy to implement and only take a moment of your time.
I will review one way which will just highlight the rows that are duplicates and also go over a way to delete all the duplicates and only leave the unique records.
Method 1: Highlighting the duplicate records.
We have the following table and we want to know which students are on here multiple times.
So what we need to do first is to highlight column A, which is the Student name.
Then go to the Home Tab and over to Conditional formatting. Drop down to highlight Cell Rules and then the next drop down to Duplicate Values.
Now another menu will pop up. This will tell you how you want the cells to be formatted. It defaults to Red fill with Red Text but you can click the drop down and choose a different color or even do a custom formatting to make it however you want it to look.
So when this is done, you will get the following table:
As you can see, all the names that are duplicates are highlighted. You can then put a filter on this table and sort them by color if you like. Then you can find all the red cells, which would be the duplicate names.
Method 2: Removing the Duplicate Records
Let’s assume we have the same table as our first example:
But here, we want to go ahead and delete all the duplicate records.
To do this, we need to highlight the table or just put our cursor on it. Then Excel will recognize the full table.
Then go to the Data Tab and then over to Remove Duplicates.
After pressing it, another menu will pop up:
With this menu, you have to pick which columns you want Excel to look at and compare to see if they are duplicates.
For example, if we only check Student, then it will only look at column A. It will look for duplicate names only and when it finds them, it will delete the whole row. It will keep the first record and delete all the rest.
Here is our result if we just clicked Student:
However, if we want to look for records from our original table where more than one column match, then we would need to check more boxes.
For example, if we wanted to find all the records where the name and the test score match, then we would need to check those two boxes like below:
Then it will scan this list:
and see that there is only one instance of name and score being the same. That is Tim and score of 77 appears twice. So it will keep the first record and delete all other instances.
So if you look at the above table, you will only see Tim and 77 once.
That is my overview on two easy methods to take care of duplicate entries. There are other ways as Excel usually gives us multiple ways to accomplish any task.
I hope you learned something from this. Thanks!