Md. Sourov Hossain Mithun
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.
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.
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.
- After that, use Fill Handle to AutoFill up to D12.
- After that, select D5:D12.
- Then, go to Home tab.
- After that, select Conditional Formatting.
- Following that, select Highlight Cell Rules.
- Finally, select Duplicate Values.
- A box will appear. Click OK.
- Excel will highlight the duplicate rows.
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.
- Then, go to the Home tab.
- After that, select Conditional Formatting.
- Following that, select Highlight Cell Rules.
- Finally, select Duplicate Values.
- A box will appear. Click OK.
- Excel will highlight the duplicate rows.
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.
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.
Similar Readings
- Excel Find Similar Text in Two Columns (3 Ways)
- How to Compare Rows in Excel for Duplicates
- Find Matches or Duplicate Values in Excel (8 Ways)
- Formula to Find Duplicates in Excel (6 Easy Ways)
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.
Formula Explanation
- COUNTIF($D$5:$D5,D5)>1 → This 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.
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.
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.
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.
