Here’s an overview of finding duplicated values across rows.
How to Find Duplicate Rows in Excel: 5 Quick Ways
We will use some salespersons’ names and their corresponding regions in our dataset. Some rows contain repeated values.
Method 1 – Use the CONCATENATE Function and Conditional Formatting to Find Duplicate Rows in Excel
Steps:
- We have added a new column named Combined to apply the CONCATENATE function.
- Use the formula given below in the first cell of the new column.
=CONCATENATE(B5,C5)
- Hit the Enter button to get the output.
- Use the Fill Handle to AutoFill to D12.
- Select D5:D12.
- Go to the Home tab.
- Select Conditional Formatting.
- Select Highlight Cell Rules.
- Choose Duplicate Values.
- A box will appear. Click OK.
- Excel will highlight the duplicate rows.
Read more: How to Find Repeated Cells in Excel
Method 2 – Apply Conditional Formatting
Steps:
- Select B5:C12.
- Go to the Home tab.
- Select Conditional Formatting.
- Select Highlight Cell Rules.
- Choose Duplicate Values.
- A box will appear. Click OK.
- Excel will highlight the duplicate rows.
Read more: How to Find Repeated Numbers in Excel
Method 3 – Insert the COUNTIF Function to Find Matched Rows in Excel
Steps:
- We have added another column E named Count.
- Go to the new cell E5.
- Use the following formula:
=COUNTIF(D$5:D12,D5)
- Press Enter.
- Use the Fill Handle to AutoFill to D12.
Read More: How to Filter Duplicates in Excel
Method 4 – Combine the IF and COUNTIF Functions to Find Replicated Rows in Excel
Steps:
- In Cell E5, use the given formula.
=IF(COUNTIF($D$5:$D5,D5)>1,"Duplicate","")
- 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)
- Use Fill Handle to AutoFill down to E12.
The difference between this method with the previous ones is that the first instance of a value is not considered a duplicate.
Read More: How to Compare Rows for Duplicates in Excel
Method 5 – Use IF and SUMPRODUCT Functions to Find Duplicate Rows in Excel
Steps:
- Use the following combined formula in cell D5.
=IF(SUMPRODUCT(($B$5:$B$12=B5)*1,($C$5:$C$12=C5)*1)>1,"Duplicates","No Duplicates")
- Press Enter.
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}
- Use the Fill Handle to AutoFill to D12.
Read More: Excel Find Duplicate Rows Based on Multiple Columns
Download the Practice Workbook
Related Articles
- How to Compare Two Excel Sheets for Duplicates
- How to Find Matching Values in Two Worksheets in Excel
- How to Find Duplicates in Excel and Copy to Another Sheet
- Excel VBA to Find Duplicate Values in Range
- How to Find Duplicates in a Column Using Excel VBA
- How to Use VBA Code to Find Duplicate Rows in Excel
<< Go Back to Find Duplicates in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Is there a formula to find duplicates (Laura & China and China & Laura) if table was like this:
Salesperson Region
Laura China
Ellie US
Ann Brazil
China Laura
Gemma Canada
US Ellie
Hello YVONNE
Thanks for reaching out and sharing your query. You wanted a formula to find duplicates when columns can be suffered, like Laura & China and China & Laura.
I have developed two formulas that will fulfil your requirements using the IF and COUNTIF functions. I will use column C as a Helper column, displaying the result in column D.
Follow these steps:
Step 1: Select cell D5 => Insert the given formula => Drag the Fill Handle icon to cell D10.
Step 2: Select cell D5 => Insert the given formula => Drag the Fill Handle icon to cell D10.
Hopefully, you have got the solution. Good luck.
Regards
Lutfor Rahman Shimanto