How To Remove Duplicate Rows In Excel

In this article, I will tell you how to remove duplicate rows in excel with two methods. The first method is using the excel advanced filter feature, this method can reserve the original datasets. The second method is to use excel’s remove duplicates menu, this method will remove the duplicate rows permanently.

1. Remove Excel Duplicate Rows Using Excel Advanced Filter.

1. This method can filter out the unique rows ( at least one of the columns in the rows is not duplicate ) in an excel worksheet and then copy them to the target excel cell range.
2. Using this method, you can get the unique rows in an excel worksheet and can reserve the original dataset.
3. Below is the example excel cell dataset.
   
4. Select the cell range in your excel worksheet, or you can just click the first cell in the worksheet cell range.
5. Click the Data —> Advanced icon in the Sort & Filter group.
6. Then it will pop up the Advanced Filter dialog window.
   
7. I run excel on macOS, the dialog window is similar if you run Excel on Windows.
8. If you select the radio button Filter the list, in-place and check the checkbox Unique records only.
9. When you click the OK button, it will filter out the duplicated rows and collapse all the duplicate rows to show only one.
10. The other duplicated rows are hidden, they are not removed.
    
11. If you right-click the short underline at the beginning of the collapsed rows and click the Unhide menu item in the popup menu list, then it will display the hidden row.
12. If you select the radio button Copy to another location, you can specify where to paste the filtered-out unique rows.
13. You can specify the target cell range in the Copy to: input text box.
14. Then when you click the OK button, it will copy all the unique rows in the List range box to the target cell range specified in the Copy to box.
15. And the original data sets are not modified at all.
16. This method can only filter out the rows where all the columns in the rows contain the same value.
17. If you just want to filter out duplicated rows where only specified column cells contain the same value, you should use excel’s remove duplicates menu.

2. Remove Excel Duplicate Rows Using Excel Remove Duplicates Menu.

1. Using this method, you can remove the duplicated rows by specified columns in the cell range.
2. Select the cell range where you want to remove the duplicated rows, then click the Data tab on excel top menu bar.
3. Click the Remove Duplicates menu item in the Data Tools group to open the Remove Duplicates popup dialog.
   
4. You can select multiple columns by checking the checkbox before the column name.
5. Then it will remove the rows that only the selected columns contain the duplicate data.
6. For example, if you only check the checkbox before the column name comment_post_name and uncheck all the other column name checkboxes.
7. Then when you click the OK button, it will remove all the rows that only the comment_post_name column contains the duplicate data.
8. Below is the result of the above action in this example.
   
9. This method will remove the duplicated rows permanently, it can not reserve the original data sets.
10. If you select all the columns in the Remove Duplicates dialog window, it will do the same as what we do using the excel advanced filter in section 1.