Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Find Duplicate Rows in Excel (5 Quick Ways)

While working in Excel there can be some duplicate rows in your worksheet, and then you may want to find or highlight the duplicate rows because the duplicate rows can create a lot of trouble. In this article, you will learn 5 easy methods to find duplicate rows in Excel.

This is an overview of this article.find duplicate rows in excel


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


5 Quick Methods to Find Duplicate Rows in Excel

Let’s get introduced to our dataset first. I have used some salespersons’ names and their corresponding regions in our dataset. Please have a look that there are some duplicate rows in the dataset.

Dataset


Method 1: Use CONCATENATE Function And Conditional Formatting to Find Duplicate Rows in Excel

First of all,  I will use the CONCATENATE function and Conditional Formatting to find duplicate rows in Excel. The CONCATENATE function is used to join two or more strings into one string.

Steps:

  • At first, we’ll combine the data from every row. That’s why I have added a new column named “Combined” to apply the CONCATENATE
  • Type the formula given below

=CONCATENATE(B5,C5)

  • Then hit the Enter button to get the output.

CONCATENATE function find duplicate rows in excel

  • After that, use Fill Handle to AutoFill up to D12.

AutoFill

  • After that, select D5:D12.

Conditional Formatting

  • Then, go to Home tab.
  • After that, select Conditional Formatting.
  • Following that, select Highlight Cell Rules.
  • Finally, select Duplicate Values.

Duplicate

  • A box will appear. Click OK.

Duplicate value box

  • Excel will highlight the duplicate rows.

result find duplicate rows in excel

Read more: Excel Find Duplicate Rows Based on Multiple Columns


Method 2: Apply Conditional Formatting

In this method, we’ll again use Conditional Formatting. This method is a bit easier because it will not need any helper column like the previous method.

Steps:

  • Select B5:C12.

Conditional Formatting

  • Then, go to the Home tab.
  • After that, select Conditional Formatting.
  • Following that, select Highlight Cell Rules.
  • Finally, select Duplicate Values.

Duplicate

  • A box will appear. Click OK.

Duplicate value box

  • Excel will highlight the duplicate rows.

result find duplicate rows in excel

Read more: How to Find & Remove Duplicate Rows in Excel


Method 3: Insert COUNTIF Function to Find Matched Rows in Excel

Here we’ll use only the COUNTIF function to find duplicate rows in Excel. The COUNTIF function will count the duplicate numbers and then from that, we’ll be able to detect the duplicate rows. I have added another column named “Count”.

Steps:

  • Activate Cell E5.
  • Then, type the given formula

=COUNTIF(D$5:D12,D5)

  • After that, press ENTER to get the output.

COUNTIF Function

Here, Excel will count the instances if the rows are duplicates in the range D5:D12.

  • After that, use Fill Handle to AutoFill up to D12.

COUNTIF Function

Similar Readings


Method 4: Combine IF Function And COUNTIF Function to Find Replica Rows in Excel

In this method, we’ll combine the IF function and the COUNTIF function to find duplicate rows in Excel. The IF function checks whether a condition is met and returns one value if true and another value if false.

Steps:

  • In Cell E5 write the given formula.

=IF(COUNTIF($D$5:$D5,D5)>1,"Duplicate","")

  • Then press ENTER to get the output.

IF and COUNTIF Functions

Formula Explanation

  • COUNTIF($D$5:$D5,D5)>1This is the logical test. It will be TRUE if a duplicate row appears.
    • Output: FALSE
  • IF(COUNTIF($D$5:$D5,D5)>1,”Duplicate”,””)This becomes,
  • IF(FALSE,”Duplicate”,””)
    • Output: “” (blank)
  • After that, use Fill Handle to AutoFill up to E12.

IF and COUNTIF Functions

Note

The difference between this method with the previous ones is that here the 1st instance of a row is not considered a duplicate one.


Method 5: Use IF Function And SUMPRODUCT Function Together to Find Duplicate Rows in Excel

In our last method, we’ll use another combination of two functions- the IF function and the SUMPRODUCT function. The SUMPRODUCT is a function that multiplies the range of cells or arrays and returns the sum of products.

Steps:

  • Write the combined formula in Cell D5.

=IF(SUMPRODUCT(($B$5:$B$12=B5)*1,($C$5:$C$12=C5)*1)>1,"Duplicates","No Duplicates")

  • Then, press ENTER to get the output.

IF and SUMPRODUCT Functions

Formula Breakdown

  • SUMPRODUCT(($B$5:$B$12=B5)*1,($C$5:$C$12=C5)*1)>1 → The SUMPRODUCT function will check the array whether it is greater than 1 or not. Then it will show TRUE for greater than 1 otherwise FALSE. It will return as-
    • Output: {TRUE}
  • IF(SUMPRODUCT(($B$5:$B$12=B5)*1,($C$5:$C$12=C5)*1)>1,”Duplicates”,”No Duplicates”) → Then the IF function will show “Duplicates” for TRUE and “No duplicates” for FALSE. The result will be-
    • Output: {Duplicates}
  • After that, use Fill Handle to AutoFill up to D12.

result IF and SUMPRODUCT Functions


Conclusion

I hope all of the methods described above will be good enough to find duplicate rows in excel. Feel free to ask any questions in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo