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
Thank you for sharing this – great tip and just learnt something new in Excel which saved me at least 30 minutes of time!
Hello Jon,
You’re very welcome! Glad to hear it saved your time. Excel wins again!
Let us know if you want more tips like this. Keep exploring Excel with ExcelDemy!
Regards
ExcelDemy