How to Remove Duplicate Rows in Excel (3 Ways)

While working with a large dataset in Excel, there is a possibility that you are getting the same duplicate rows or columns. Sometimes we may need to remove those duplicate rows to get a clear concept about the worksheet. Excel provides some built-in features and formulas by which you can easily remove duplicate rows. Today, in this article, we will learn how to remove duplicate rows in Excel.


Download Practice Workbook

Download this practice sheet to practice while you are reading this article.


3 Suitable Ways to Remove Duplicate Rows in Excel

Consider a situation where you are given a dataset of some book names, their authors, and the price of those books. Now in the dataset, there are some rows containing duplicate values. We need to remove those duplicate rows. To do that we will use three different methods discussed below.

Apply Remove Duplicates Feature to Remove Duplicate Rows

1. Use Remove Duplicates Feature to Remove Duplicate Rows

First, we will use the Remove Duplicate feature which is a built-in feature in Excel. Let’s follow these steps to learn!

Step 1:

Select the whole dataset. Go to Data then select the Remove Duplicates option in the Data Tools group.

Data Remove Duplicates

Apply Remove Duplicates Feature to Remove Duplicate Rows

Step 2:

  • In the Remove Duplicates window, check on the Select All option as we want to remove the duplicate rows from every column. Make sure to check on the aa columns and then click OK to continue.

Apply Remove Duplicates Feature to Remove Duplicate Rows

  • A new window appears telling that our duplicate rows are removed. Click OK to proceed

Apply Remove Duplicates Feature to Remove Duplicate Rows

  • Now we have successfully removed all the duplicate rows.

Apply Remove Duplicates Feature to Remove Duplicate Rows


2. Use Advanced Filter Option to Remove Duplicate Rows

Step 1:

  • To remove duplicate rows using the advanced filter, select the whole dataset, go to Data then in the Sort & Filter group, click on Advanced.

Data → Sort & Filter → Advanced

Use Advanced Filter Option to Remove Duplicate Rows

Step 2:

  • In the Advanced Filter window, check on Filter the List, in-Place to filter the dataset in its current location. Select your List-Range values.

Use Advanced Filter Option to Remove Duplicate Rows

  • Then click on Unique Records Only. Click OK to remove duplicate rows.

Use Advanced Filter Option to Remove Duplicate Rows

 

  • Our dataset is filtered and duplicate rows are removed.

Use Advanced Filter Option to Remove Duplicate Rows

Step 3:

  • If we want to make our unique dataset list in another place in the worksheet, just click on Copy to Another Place in the Advanced Filter Select your place by inserting values in the Copy to box. Click OK to continue.

Use Advanced Filter Option to Remove Duplicate Rows

  • Our duplicate rows are removed and a new unique dataset is created.

Use Advanced Filter Option to Remove Duplicate Rows


3. Insert If with COUNTIFS Formula to Remove Duplicate Rows

We also can use formulas to remove duplicates from rows. We can use the combo of the IF and the COUNTIFS formula to find out the duplicate values and then filter them. Let’s learn this method!

Step 1:

  • Create a new column named Status in the existing dataset. Here we will apply the formula and filter the duplicate rows.

 

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

  • In cell E4 of the Status column, combine the IF and the COUNTIFS Insert the values into the formula and the final form is,
=IF(COUNTIFS($B$4:$B4,$B4,$C$4:$C4,$C4,$D$4:$D4,$D4)>1,"Duplicate Row","Unique")
  • Where $B$4:$B4, $C$4:$C4, $D$4:$D4 are the ranges
  • $B4, $C4, $D4 are the criteria
  • If the argument is true then the formula will return Unique
  • If the argument is false then the formula will return Duplicate Row

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

  • Press Enter to apply the formula. And apply the same to the rest of the cells.

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

Step 2:

  • So we have got the status of our dataset. Now we will filter the dataset. Click on the Status column header and go to Sort & Filter in the Editing Group and click on Filter

Home → Sort & Filter → Filter

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

  • Now we have the filter option in every column of our dataset.

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

  • Click on the drop-down filter icon in the Status column. From the given option, uncheck Duplicate Row to filter the dataset and click OK

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

  • Our duplicate rows are now filtered out.

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

Step 3:

  • In the previous steps, we found out duplicate rows without the first occurrence. You can easily filter the duplicate rows including the first occurrence too. To do this, the formula is,
=IF(COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1,"Duplicate Row","Unique")
  • Where the new range is $B$4:$B$13, $C$4:$C$13, $D$4:$D$13. And the rest arguments are the same as before.

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

  • Press Enter to apply this function.

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

  • Now apply the same formula to the rest of the cells to get the full result.

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

Step 4:

  • We will now filter them as we used in the previous steps. Apply the filter in those columns and in the Status column, filter the data using only the Unique Criteria

Insert If with the COUNTIFS Formula to Remove Duplicate Rows

  • Click OK to remove those duplicate rows. And our final result is here.

Insert If with the COUNTIFS Formula to Remove Duplicate Rows


Things to Remember

👉 When you select your Range, you have to use the absolute cell references ($) to block the array.

👉 You can filter your data in other places in the worksheet while using the Advanced Filter option.


Conclusion

Removing duplicate rows in excel is quite easy if you follow the procedures we discussed in this article. We hope this article proves useful to you. If you have any ideas or suggestions, you are welcome to share your thoughts in the comment box.


Similar Articles to Explore

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo