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