There are two main ways for you to find out whether there are duplicate values in your Excel. The first and most straightforward is the remove duplicates feature, which once used, removes the entire line where the duplicate value was found.
Another option is conditional formatting with color highlighting for duplicate values. In this case, there is no line removal, only differentiated formatting is applied to the double values. Let's see how to do each case.
1. How to Remove Duplicates Step by Step
To start you need to have a data table. In our example, we have a staffing table for a company. See that it has columns of name, registration, sex, birth, etc. To use the feature, you must select all data in the table (CTRL + T shortcut):
As the data was filled by a person it is possible that errors occur in the process and some information may have been repeated. In that case, it would be awful for the parsing of the table to have duplicate values in it. See that in the example I have the name Amanda Santos, as well as all your information, being repeated.
In this table it was quite simple to find the value repeated because we only have 11 names registered. Now imagine a spreadsheet with 1.000 entries, it would be impossible to verify name by name. In such cases we need to use the remove duplicates feature. It is in the DATA tab, within the data tools group:
When using this functionality, a window will open asking which column you want to check. Note that the window already comes with all values marked. In this case, you need to select which column or columns you want to parse. Think about me, would it help me to see what are the duplicate values of sex? Of course not?! After all, we only have two options (male and female), and so we would certainly have a number of items being removed in the wrong way.
The trick here is to understand which column contains values that need to be unique. It could be the name, a unique product code, the RG or even an individual license plate.
In our case, I chose to choose the name. See that I just marked the name and no other item. I did this not to confuse my analysis of what is being removed from the table with the remove duplicates feature.
After pressing OK in the Remove Duplicates window a new window opens informing you how many values have been removed. Notice that 3 repeated values were found and removed. This was because I just showed the image of a piece of the table above
In the end, this would be the final table that we would have without the entire lines where the name Amanda Santos appeared doubled.
See that analysis left only the first entry of Amanda Santos, taking out the values that were below. This occurred because the removal of duplicate values is always done from the bottom up, leaving only the first value that was entered in the worksheet.
See how easy it is? But as I said at the beginning of the post, there is a second way for you to analyze duplicate values in a worksheet without being able to remove duplicates directly.
2. Conditional Formatting of Duplicated Values
It may be that at some point you want to analyze what the duplicate values are before you remove them from your spreadsheet. For these cases it is possible to use the highlighting of duplicate values with conditional formatting.
The first step to this is to select the range of values you are going to analyze. In our case it is the column with names of the employees:
After that, go to the HOME tab and choose the conditional formatting option
Note that you will have a number of options, but the correct one for this case is the cell rules> duplicate values highlighting.
After choosing this option Excel will open a conditional formatting window:
Note that when you open this option the duplicate values are already checked before you even press OK. If you cancel the highlighting will disappear. If you want to move on, you can use light red with dark red font or change the formatting in the open window options.
If you want to learn this and many other features, try the free lessons of our courses. Beginner and Intermediate Excel or advanced Excel. If you want to learn more about other Excel features, just tell me in the comments below what you think is most important.