How to Remove Duplicate Rows in Excel Based on Two Columns

In this tutorial, I am going to show you 4 suitable ways to remove duplicate rows in excel based on two columns. You can quickly use these methods, especially in large datasets to find the duplicate records that exist. Throughout this tutorial, you will also learn some important excel tools and functions which will be very useful in any excel related task.

Download Practice Workbook

You can download the practice workbook from here.

4 Suitable Ways to Remove Duplicate Rows Based on Two Columns in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 2 columns. Initially, we formatted all the cells containing dollar values in Accounting format. For all the datasets, we have the 2 columns as Employee Name and Sales. Although we may add some columns later on if that is needed.

1. Using Remove Duplicates Feature

We can remove duplicate rows based on two columns with just a few clicks using the Remove Duplicates feature in excel. We will see in the below steps how to use this.

Steps:

• First, select any cell inside the dataset.
• Then, go to the Data tab and under Data Tools click on Remove Duplicates.

• Next, check the ‘My data has headers’ option and click OK.

• Consequently, this will remove the duplicates from the dataset.

Read More: How to Remove Duplicates from Column in Excel (3 Methods)

2. Applying Advanced Filter Option

This option in excel is the advanced version of the regular filter which helps to remove duplicates from tables. We will use this option to remove duplicate rows based on two columns in excel. Let us see how to apply this step-by-step.

Steps:

• To begin with, select any cell in the data range and navigate to the Data tab, and under Sort & Filter select Advanced.

• Now, in the Advanced Filter window, check the Unique records only option and click OK.
• As a result, this should remove all the duplicates from the worksheet.

Read More: Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)

3. Removing Duplicates Based on Specific Columns

This method is a little bit different. This is useful when you have some columns and you want to find and remove duplicate rows based on two specific columns, not based on every column in the range. Follow the steps below to see the details of this method.

Steps:

• To start with this method, click on any cell in the dataset and go to the Data tab, and then Data Tools.

• Now, in the new window, uncheck the Month and Sales column and also check My data has headers.
• Finally, click OK and this will remove the duplicate records based on the columns you selected.

Read More: How to Remove Duplicates in Excel Sheet (7 Methods)

4. Utilizing COUNTIFS Function

The COUNTIFS function in excel can take multiple ranges and criteria to count the cells that fulfill them. Let us see how to use this function to remove duplicate rows based on two columns in the dataset.

Steps:

• Firstly, double-click on cell D5 and insert the following formula:

• Then, press Enter and copy this formula to all the cells below.
• As a result, you should see the duplicate data in the Status column and now you can delete that duplicate row manually.

Read More: How to Remove Duplicate Rows Based on One Column in Excel

Similar Readings

• How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)
• Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)
• How to Remove Duplicate Names in Excel (7 Simple Methods)
• Fix: Excel Remove Duplicates Not Working (3 Solutions)

How to Remove Duplicates Based on Criteria in Excel

While working with a large dataset in excel, there is a possibility that you are getting the same duplicate values from different rows or columns. Sometimes we may need to remove those duplicate values based on some specific criteria to get a clear concept of the worksheet. Below we will see how can use VBA code to quickly achieve this.

Steps:

• Firstly, go to the Developer tab and click on Visual Basic.

• Now, in the VBA window, click on Insert and then Module.

• Next, in the module window, type in the code below:

Sub Delete_duplicate_rows()
Dim Rng As Range
Set Rng = Selection
Rng.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

• Now, close the VBA window to save the code.
• Here, under the Developer tab, click on Macros.

• Now, in the Macro window, select the macro we created and click on Run.
• Consequently, the VBA code will quickly remove all the duplicates from the data table.

Read More: How to Remove Duplicates in Excel Using VBA (3 Quick Methods)

How to Highlight Duplicates in Two Columns in Excel

When we deal with a large excel spreadsheet, duplicate values exist in our dataset often. Also sometimes it becomes important for us to find them for any specific purpose. Let us see how we can easily find those duplicates.

Steps:

• First, select the dataset including the headers.

• Next, under the Home tab click on Conditional Formatting.
• Here, from Highlight Cells Rules, select Duplicate Values.

• Now, in the new window, keep the formatting as it is if you do not want to change it, and then click OK.

• Finally, this operation will highlight the duplicate values with light red color.

Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to remove duplicate rows in excel based on two columns. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.

Related Articles

• How to Delete Duplicates in Excel but Keep One (7 Methods)
• Excel Formula to Automatically Remove Duplicates (3 Quick Methods)
• How to Remove Duplicates and Keep the First Value in Excel (5 Methods)
• Excel VBA: Remove Duplicates from an Array (2 Examples)
• How to Remove Both Duplicates in Excel (5 Easy Ways)