Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Remove Duplicate Rows in Excel (3 Suitable 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 of 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 3 suitable ways on the topic of how to remove duplicate rows in Excel. I hope it will be helpful for you if you are looking for a similar sort of thing.


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.

Remove Duplicate Rows Excel


1. Apply Remove Duplicates Feature to Remove Duplicate Rows

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

Steps:

  • First of all, select the whole dataset.
  • Then, go to the Data tab and select the Remove Duplicates option in the Data Tools group.

Apply Remove Duplicates Feature to Remove Duplicate Rows

  • 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 all the columns and then click OK to continue.

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

  • Now we have successfully removed all the duplicate rows.

Apply Remove Duplicates Feature to Remove Duplicate Rows

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


2. Use Advanced Filter Option to Remove Duplicate Rows

With the help of the Advanced Filter feature under the Data tab, we can also remove duplicate rows. In the following section, the process is described in detail.

Steps:

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

Use Advanced Filter Option to Remove Duplicate Rows

  • 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.
  • Then click on Unique Records Only.
  • Finally, click OK to remove duplicate rows.

  • Our dataset is filtered and duplicate rows are removed.

Remove Duplicate Rows Excel

  • 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 box.
  • Afterward, select your location by inserting values in the Copy to box and click OK to continue.

  • Our duplicate rows are removed and a new unique dataset is created in the defined destination.

Remove Duplicate Rows Excel

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


Similar Readings


3. Insert IF Function with COUNTIFS 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!

Steps:

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

Insert IF Function with COUNTIFS to Remove Duplicate Rows

  • In cell E5 of the Status column, input the following formula combining the IF and the COUNTIFS functions.
=IF(COUNTIFS($B$5:$B5,$B5,$C$5:$C5,$C5,$D$5:$D5,$D5)>1,"Duplicate Row","Unique")
  • Where $B$5:$B5, $C$5:$C5, $D$5:$D5 are the ranges. $B5, $C5, $D5 are the criteria. If the argument is true, the formula will return Unique. If the argument is false, the formula will return Duplicate Row.
  • Press Enter to apply the formula.

Remove Duplicate Rows Excel

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

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

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

Remove Duplicate Rows Excel

  • Our duplicate rows are now filtered out.

  • In this method, we found duplicate rows and removed the extra ones. In another way, we can filter the unique rows from the dataset excluding the rows with duplicate rows. To do this, use the following formula,
=IF(COUNTIFS($B$5:$B$14,$B5,$C$5:$C$14,$C5,$D$5:$D$14,$D5)>1,"Duplicate Row","Unique")
  • Press Enter to apply this function.

  • Now, AutoFill the formula to the rest of the cells in column E.

Remove Duplicate Rows Excel

  • Afterward, select the entire Status column.
  • Then, go to Data and click on Filter in the Sort & Filter group.

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

  • Finally, we have our desired result without duplicate rows.

Remove Duplicate Rows Excel

Read More: Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)


Notes

👉 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

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo