Find Duplicates in Excel

Find Duplicates In Excel

While working in Excel, we may deal with large files and get many duplicate values. However, to remove the duplicate files, we first need to find duplicates in excel. We can easily find duplicates in excel using Conditional Formatting, COUNTIF function, Remove Duplicates, etc.,

Duplicate values will cause many problems, and we may get the wrong summary reports. It is almost necessary to check for duplicate values every time we get data from other sources or colleagues. Let us learn how to identify duplicate values, remove them, and keep the data unique.

Top 5 Methods to Find Duplicates In Excel

Let us look at the top 5 methods to find duplicates in Excel.

#1 – Conditional Formatting

Let us learn how to find duplicates in excel using conditional formatting.

For instance, we have the following city name list in Excel.

We have data with city names repeating multiple times. Let us apply conditional formatting and highlight the duplicate values.

Step 1: Select the data range where we need to highlight the duplicate values, i.e., from A2:A14.

Step 2: Go to the Home tab, and click on the Conditional Formatting drop-down list.

Step 3: Hover on Highlight Cells Rules. It will display various options, and click on the Duplicate Values… option.

Step 4: The Duplicate Values window opens.

In the first drop-down option, we can choose either Duplicate or Unique. By default, Excel chooses the Duplicate option.

Step 5: We can choose the formatting color in the next drop-down.

By default, Excel chooses Light Red Fill with Dark Red Text. However, the user can choose any available formatting colors from the above list.

Let us go with the default color. Then, we will see all the duplicate cells highlighted in the chosen color, as shown in the image below.

#2 – Conditional Formatting (Specific Occurrence)

The above example taught us how to highlight all the duplicate values. However, we may often have to find duplicates in excel at a specific occurrence.

For instance, we may have to find duplicates that occur >=3 times.

Take a look at the following invoice number list in Excel.

We need to find duplicate invoices appearing more than 2 times from the above list.

Step 1: Select the data from A2:A16.

Step 2: Go to the Home tab, and click on the New Rule… option under the Conditional Formatting drop-down.

Step 3: The New Formatting Rule window opens up. Click on Use a formula to determine which cells to format.

Step 4: Enter the COUNTIF Excel formula to find duplicates in excel, i.e., =COUNTIF($A:$A,$A2)>2 in the window.

Step 5: Now click on the Format option and choose the formatting style we would like to apply.

Step 6: In the Format Cells window, choose the fill color.

Step 7: Click on OK in the next two windows, and duplicate values appearing more than 2 times will be highlighted with the chosen color.

So, from the obtained results, it is evident that the invoice numbers LEE-88 and LKO-51 appear more than 2 times.

Formula Explanation: The formula that we have used for conditional formatting is

=COUNTIF($A:$A,$A2)>2

This formula will go through all the cells of column A and count each invoice number’s total appearances in the entire column. Then we applied the logic as >2, so whichever invoice number count is >2 will return TRUE or else FALSE.

Only those cells that satisfy the condition TRUE get highlighted with the conditional formatting color.

#3 – Change Rules (Formulas)

If we want to edit the already applied conditional formatting, we can change the rules using the edit rules option.

For instance, in the above example, we have used the logic to highlight duplicate values with more than 2 counts.

Assume we need to highlight the cells where the duplicate count is exactly 2 ; then, we can do this by changing the existing formula.

Step 1: Select the range of cells where conditional formatting is already applied, i.e., A2:A16.

Step 2: Go to the Conditional Formatting drop-down and click on the Manage Rules… option.

Step 3: The Conditional Formatting Rules Manager window opens up. Select the rule applied and then click on the Edit Rule option.

Step 4: In the Edit Formatting Rule window, change the formula logic as shown in the following image.

Enter this formula =COUNTIF($A:$A,$A2)=2

Click on OK and duplicate values appearing twice will be highlighted.

This way, we can edit rules and change the formula to find duplicates in excel at the specific occurrence.

#4 – Remove Duplicates

We can use the remove duplicate feature to remove all the duplicate values. This feature will remove all the duplicate values and retain only the unique values.

For instance, we have the following invoice table in Excel.

Using find duplicates in excel, we found duplicate values in a single column in all the above examples. However, finding duplicate values from the entire data will be a different task.

From the above table, if we need to find duplicates in excel, we need to look at all the columns and the combination of columns.     

For instance, look at the following image.

The same data is repeating twice here. All the columns’ data is repeated again in another row. This is called duplicate data.

We can remove the duplicates by using the following methods.

Step 1: Select the data from A1:D15.

Step 2: Go to the Data tab and click on the Remove Duplicates option under the Data Tools group.

Step 3: The Remove Duplicates window appears. Make sure we select all the columns and tick the My data has headers check box.

Step 4: Click OK to remove all the duplicate rows. It shows the following pop-up message.

Out of the 14 available records, Excel has found 3 duplicate values, and those 3 records are removed, and 11 unique records remain in the table.

Now, no duplicate values are there in the data.

#5 – COUNTIF Formula

By using the COUNTIF function, we can identify duplicate values. For instance, we have the following data in Excel.

Let us apply the COUNTIF function to find the duplicate values count.

Step 1: Enter the COUNTIF function in cell B2.

Step 2: Choose a range from A2:A15.

Step 3: After selecting the range from A2:A15, make it an absolute Excel reference by pressing the F4 key once.

Step 4: Criteria will be to count the city. Hence choose cell A2.

Step 5: Close the bracket and hit the Enter key to get the city count.

Step 6: Now, drag the formula to all the below cells.

Wherever the count is greater than one, those values are duplicate values. Only one value is unique, i.e., Sydney in cell A14.

Instead of showing the count of city values, we can also show the value as either duplicate or unique by combining the COUNTIF function with the IF condition.

For the same COUNTIF function, we have applied the IF condition. Here, the IF condition checks whether the city’s COUNT is greater than 1. If the count is >1, it will print the value as ‘Duplicate’ or print ‘Unique.’

Important Things To Note

• Excel needs the same values to consider them duplicate values. Even a small spacing will make the words different.
• ALT + A + M is the Excel shortcut key to remove duplicates.
• When we find duplicates in excel with multiple columns, we need to consider all the column’s data and then try to find the duplicate combination data.
• When we try to remove duplicates with multiple headers, ensure our data has headers and the same check box is ticked.
• Similarly, when we try to remove duplicates without headers, ensure we uncheck the headers in the data.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand Find Duplicates In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide Find Duplicates in Excel. Here we use the top 5 methods to find duplicates in excel with examples and a downloadable excel template. You can learn more from the following articles –

• Power Query in Excel
• FILTER In Excel
• Tables in Excel