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.

excel remove duplicate rows based on two columns


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.

opening remove duplicates to remove duplicate rows based on two columns in excel

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

remove duplicates feature to remove duplicate rows based on two columns in excel

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


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.

advanced filter to remove duplicate rows based on two columns in excel

  • 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.


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.

remove duplicate rows based on two specific columns in excel

  • 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.


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:
=IF(COUNTIFS($B$5:B5,B5,$C$5:C5,C5)>1,"Duplicate","Unique")

COUNTIFS function to remove duplicate rows based on two columns in excel

  • 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.

🔎 How Does the Formula Work?
  • COUNTIFS($B$5:B5,B5,$C$5:C5,C5): This portion counts the number of times a record occurs which is in this case 1.
  • COUNTIFS($B$5:B5,B5,$C$5:C5,C5)>1: This portion returns TRUE or FALSE based on the condition.
  • IF(COUNTIFS($B$5:B5,B5,$C$5:C5,C5)>1,”Duplicate”,”Unique” : This returns Duplicate or Unique, based on the TRUE or FALSE conditions from the previous portion.

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.

How to Remove Duplicates Based on Criteria in Excel

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

insert module to remove duplicates based on criteria in excel

  • 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

VBA code to remove duplicates based on criteria in excel

  • 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.


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.

select dataset to highlight duplicates in two columns in excel

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

conditional formatting to highlight duplicates in two columns in excel

  • 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.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

ExcelDemy
Logo