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.

Key Takeaways
  • Finding duplicates is possible by using conditional formatting. It highlights all the values which are there more than once in the selected range of cells.
  • Once the conditional formatting is applied, when the new values are entered and if any duplicates occur, conditional formatting automatically highlights them as duplicates.
  • Remove duplicate can find duplicate values with multiple column combinations as well.
  • The COUNTIF function counts the number of times a specific value appeared in the given range of cells.
  • We can get the duplicate word for all the duplicate values by combining the IF and COUNTIF formulas.

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.

Find Duplicates in Excel - Conditional Formatting

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.

Conditional Formatting - Step 1

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

Conditional Formatting - Step 2

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

Conditional Formatting - Step 3

Step 4: The Duplicate Values window opens.

Conditional Formatting - Step 4a

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

Conditional Formatting - Step 4b

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

Conditional Formatting - Step 5a - find duplicates excel

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.

Conditional Formatting - Step 5b
find duplicates excel

#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.

Find Duplicates in Excel - Conditional Formatting (Specific Occurrence)

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

Step 1: Select the data from A2:A16.

Conditional Formatting (Specific Occurrence) - Step 1

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

Conditional Formatting (Specific Occurrence) - Step 2

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

Conditional Formatting (Specific Occurrence) - Step 3

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

Conditional Formatting (Specific Occurrence) - Step 4

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

Conditional Formatting (Specific Occurrence) - Step 5

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

Conditional Formatting (Specific Occurrence) - Step 6

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.

Conditional Formatting (Specific Occurrence) - Step 7

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.

Find Duplicates in Excel - Change Rules - Step 1

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

Change Rules - Step 2 - find duplicates excel

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

Change Rules - Step 3

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

Change Rules - Step 4 - find duplicates excel

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

Change Rules - Step 4a

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.

Find Duplicates in Excel - Remove Duplicates

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.

Find Duplicates in Excel - Remove Duplicates -1

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.

Remove Duplicates - Step 2

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

Remove Duplicates - Step 3

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

Remove Duplicates - Step 4

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.

Remove Duplicates - Step 4a

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.

Find Duplicates in Excel - COUNTIF Formula

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.

COUNTIF Formula - Step 6

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.

COUNTIF Formula - Step 6a find duplicates excel

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 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)

1. How to find duplicates in Excel without deleting them?

If we want to find duplicates in excel without deleting them, we need to use conditional formatting to highlight them.

For instance, we have the following data in Excel.

Find Duplicates in Excel - FAQ 1

If we want to find the duplicate numbers in the above list, select the data range from A2:C6.

FAQ 1-1

Go to the Home tab, and then choose Duplicate Values under Highlight Cells Rules in Conditional Formatting.

FAQ 3-2

It will highlight all the duplicate values.

FAQ 1-3

2. What is the shortcut to find duplicates in Excel?

The shortcut to find duplicates in excel is ALT + H + L + H + D.

3. How do I highlight duplicates in Excel but keep one?

If we want to highlight duplicate values from the second occurrence, enter the New Formatting Rule window with the following formula =COUNTIF($A$2:A2,A2)>1

Find Duplicates in Excel - FAQ 2

It will highlight the duplicate values from the second occurrence onward.

FAQ 2-1

3. How do I compare duplicate values of multiple columns?

Consider the data in the following table.

Find Duplicates in Excel - FAQ 3

There are duplicate invoice numbers with the same amount. So, to find the duplicate from multiple columns, we need to combine these two columns first.

FAQ 3 -1

For this helper column, we need to apply Conditional Formatting. So, select the helper column and choose Duplicate Values under the Conditional Formatting option.

FAQ 3-2

It will highlight the duplicate values.

FAQ 3 -3

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published.