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

Here’s an overview of finding duplicated values across rows.

find duplicate rows in excel


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.

Dataset


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.

CONCATENATE function find duplicate rows in excel

AutoFill

  • Select D5:D12.

Conditional Formatting

  • Go to the Home tab.
  • Select Conditional Formatting.
  • Select Highlight Cell Rules.
  • Choose 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 Repeated Cells in Excel


Method 2 – Apply Conditional Formatting

Steps:

  • Select B5:C12.

Conditional Formatting

  • Go to the Home tab.
  • Select Conditional Formatting.
  • Select Highlight Cell Rules.
  • Choose 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 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.

COUNTIF Function

  • Use the Fill Handle to AutoFill to D12.

COUNTIF Function

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.

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)
  • Use Fill Handle to AutoFill down to E12.

IF and COUNTIF Functions

Note

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.

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}
  • Use the Fill Handle to AutoFill to D12.

result IF and SUMPRODUCT Functions

Read More: Excel Find Duplicate Rows Based on Multiple Columns


Download the Practice Workbook


Related Articles


<< Go Back to Find Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

2 Comments
  1. 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

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Dec 3, 2023 at 5:51 PM

      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.

      =IF(B5 < C5, B5 & " " & C5, C5 & " " & B5)

      Step 2: Select cell D5 => Insert the given formula => Drag the Fill Handle icon to cell D10.

      =IF(COUNTIF($D$5:$D$10, D5)>1, “Duplicates”, “No Duplicates”)

      Hopefully, you have got the solution. Good luck.

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo