How to Find Duplicate Rows Based on Multiple Columns in Excel

Method 1 – Using the COUNTIFS Function

We will use the following dataset for this method.

STEPS:

Select the cell range.
Go to the Home tab and select the Conditional Formatting drop-down.
Click on the New Rule option.

A New Formatting Rule window pops up.
Select a rule type ‘Use a formula to determine which cells to format’.
Enter the following formula in the formula box:

=COUNTIFS($B$5:$B$10,$B5,$C$5:$C$10,$C5,$D$5:$D$10,$D5)>1

Select Format.

A Format Cells window opens here.
Go to the Fill option.
From the Background Color group, select the color. We can see the sample of color in the Sample box.
Click OK.

Click OK.

We can see that all the duplicate rows are highlighted with the color we’ve selected before.

Method 2 – Using the IF Function Based on Multiple Columns

We will use the following dataset for this method.

STEPS:

Select Cell F5.
Enter the following formula:

=IF(ISERROR(MATCH(B5,$D$5:$D$8,0)),"",B5)

Press Enter and use the Fill Handle tool to autofill the next cells.

How Does the Formula Work?

MATCH(B5,$D$5:$D$8,0): This will return the position of Cell B5.
ISERROR(MATCH(B5,$D$5:$D$8,0)): This will return the TRUE or FALSE value based on the presence of an error.
IF(ISERROR(MATCH(B5,$D$5:$D$8,0)),”,B5): This will display the value if it meets the above criteria; otherwise, leave the cell blank.

Method – Using the Array Formula

We will use the following dataset for this method.

STEPS:

Select Cell E5.
Enter the following formula:

=CONCATENATE(B5,C5,D5)

Press Enter and use the Fill Handle. See the below result.

Select cell F5.
Enter the following formula:

=IF(COUNTIF($E$5:$E$9,E5)=1,0,1)

Press Enter and use the Fill Handle tool for the cells below.

Select cell G5.
Enter the following formula:

=IF(F5>0,"Duplicate","N/A")

Press Enter and use the Fill Handle tool to see the result.

How Do the Formulas Work?

CONCATENATE(B5,C5,D5): This will combine the text of cells B5, C5 & D5.
IF(COUNTIF($E$5:$E$9,E5)=1,0,1): The COUNTIF function will count the number of cells from the range E5:E9 for the cell E5. And the IF function will return the value ‘0’ if it’s TRUE and ‘1’ if it’s FALSE.
IF(F5>0,”Duplicate”,”N/A”): This will return “Duplicate” if cell F5 is greater than ‘0’ and “N/A” if it’s not.

Method 4 – Using Conditional Formatting

Here we have a dataset (B4:D10) of customers with their purchased products and amounts.

STEPS:

Select the dataset.
Go to the Home tab and click on the Conditional Formatting drop-down.
Go to the Highlighted Cells Rules group and select Duplicate Values.

The Duplicate Values message box pops up.
From the drop-down menu, select the color that will indicate the duplicate cells.
Click OK.

We can see all the duplicate rows in yellow filled with dark yellow text.

Read more: How to Filter Duplicates in Excel

Method 5 – Using the Advanced Filter Feature

We will use the following dataset for this method.

STEPS:

Select the cell range.
Go to the Data tab.
From the Sort & Filter group, select Advanced.

An Advanced Filter window pops up.
Check the box ‘Copy to another location.’
Make sure the list range is already included in the data range.
Select the cell reference in the Copy to box where we want to see the duplicate rows. Here we input Cell F5.
Click on the ‘Unique records only’ option.
Select OK.

We can see the dataset without duplicate rows in the range E5:H9.

Method 6 – Using Excel VBA

We will use the following dataset for this method.

STEPS:

Select the active worksheet from the sheet bar and right-click on it.
Click on the View Code option.

A VBA Module window opens. We can also open it by pressing the ‘Alt + F11’ keys. Click on Insert > Module.
Enter the following formula:

Sub DuplicateRows()
Dim Rng1 As Range, Rng2 As Range, R1 As Range, R2 As Range, outRng As Range
xTitleId = "DuplicateRowsInExcel"
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("Rng1 :", xTitleId, Rng1.Address, Type:=8)
Set Rng2 = Application.InputBox("Rng2:", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each R1 In Rng1
xValue = R1.Value
For Each R2 In Rng2
If xValue = R2.Value Then
If outRng Is Nothing Then
Set outRng = R1
Else
Set outRng = Application.Union(outRng, R1)
End If
End If
Next
Next
outRng.Select
Application.ScreenUpdating = True
End Sub

Click on the Run option or press the F5 key.

A Macros confirmation box pops up. Select the sheet and click on Run.

We can see the DuplicateRowsInExcel message box. Here input the Rng1, in which we will highlight the duplicate rows from the worksheet.
Select OK.