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.
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!
Select the whole dataset. Go to Data then select the Remove Duplicates option in the Data Tools group.
Data → Remove Duplicates
- 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.
- A new window appears telling that our duplicate rows are removed. Click OK to proceed
- Now we have successfully removed all the duplicate rows.
Read more: How to Find & Remove Duplicate Rows in Excel
2. Use Advanced Filter Option to Remove Duplicate Rows
- 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
- 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. Click OK to remove duplicate rows.
- Our dataset is filtered and duplicate rows are removed.
- 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.
- Our duplicate rows are removed and a new unique dataset is created.
- Remove duplicate rows based on two columns in Excel [4 ways]
- How to Remove Duplicate Rows in Excel Table
- How to Remove Duplicate Rows Based on One Column in Excel
3. Insert If with COUNTIFS Formula to Remove Duplicate Rows
- Create a new column named Status in the existing dataset. Here we will apply the formula and filter the 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,
- 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
- Press Enter to apply the formula. And apply the same to the rest of the cells.
- 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
- 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
- Our duplicate rows are now filtered out.
- 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,
- 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.
- Press Enter to apply this function.
- Now apply the same formula to the rest of the cells to get the full result.
- 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
- Click OK to remove those duplicate rows. And our final result is here.
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.
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.