One of the most common situations we can find when working with large amounts of data is the need to identify if we have duplicate values. To find duplicate values in excel, several ways can be used, either by sorting the data or by using the COUNT.SI function, by inserting a PivotTable or with the Conditional Formatting tool. We will use this last form since it is the fastest.
The first method I will show involves the use of the Conditional Format that will allow us to find duplicate values in Excel in a visual way. We start by selecting the range that contains the data and then go to Start> Styles> Conditional Formatting> Highlight cell rules> Duplicate values.
When the Duplicate Values dialog box is displayed, we can choose whether we want to highlight the duplicate values or unique values in excel.
For our example, I will select the Duplicate option. We can also choose the style that will be applied to duplicate values in excel, which, by default, is the light red color for the cell fill.
Already in the preview, we can see that Excel highlights duplicate values, and only cells that appear only once in the list will remain without a background color. Additionally, we can apply a filter to the values and then select the Filter by a color option to show only the values that are repeated within the list.
If you want to obtain the unique records from this list of duplicate values. You must copy the values to another range of cells and then use the Advanced Filter to create a list of unique values.
If you prefer to find duplicate values using an Excel formula?. then you should consider using the COUNTER function. Which allows us to count cells within a range that meet a given condition. Consider the following formula:
This formula counts how many times the value of cell A2 appears in column A. If I use this formula in our sample data, I will get the following result:
By copying the formula down, we get the number of occurrences that each value has in the list. If we want to know those values that appear more than once, we must apply a filter to show the records that have a value greater than 1 in column B:
Although either of the two methods mentioned above will be useful for finding duplicate values in Excel. Knowing both allows us to mix them to perform a more accurate search. It is possible to create a conditional formatting rule based on an Excel formula that highlights the values that are repeated a certain number of times.
For example, if I want to find the values that are repeated exactly 3 times within the list. I can create the following conditional formatting rule (Start> Styles> Conditional formatting> New rule):
The formula used is the same as in the previous section. But this time we match it to the value 3 to instruct Excel to apply the conditional format only to those cells that have said the number of repetitions. Observe the result when using this rule:
The only digit that is repeated 3 times is “2”, and therefore, Excel applies the format to these cells. In this way, we have created a conditional formatting rule that allows us to highlight values with a certain number of repetitions.
That’s it. All you need to know about how to find duplicates in excel. By following the above steps highlighted in this article, you will be able to find duplicates in excel with and without a formula.
In the end, If you have any questions, leave them in the comments, we will answer you as soon as possible, and it will surely be of great help to more members of the community. Thank you!
Also Read: BEST ALTERNATIVE SEARCH ENGINES TO GOOGLE