Most of the time while dealing with the data in Microsoft Excel it is required to find the duplicate rows and remove them. Fortunately, Microsoft Excel provides a feature to find and remove the duplicate rows within the Excel Worksheet.
This article applies to:
- Microsoft Office 2010
Step (1). Open your Excel worksheet and select the range of columns from where you want to find the duplicate entries. For eg: my worksheet has only one column Fruits and I have selected the column “A”. Below is the screenshot.
Step (2). Click on the Data menu, it will display Data ribbon controls.
Step (3). Click on the Remove Duplicates icon which is under the Data Tools group.
Step (4). It will display the Remove Duplicates dialog to allow to select a list of columns.
From my example worksheet mentioned above, I have selected the column “Fruits”; here it displayed the column “Fruits”.
Step (5). From the Remove Duplicates dialog select the following options:
- Select My data has headers check box if your data has headers.
- Select the list of columns by selecting the check box before the column name in the Columns list.
- If you want to select all the columns listed in the Columns list, you can click on Select All button.
- If you want to unselect all the columns, you can click on Unselect All button. Remember that you should select at least one column that contains duplicates from the columns list in order to remove duplicates.
My example worksheet has headers, so I have selected the “My data has headers” check box and the example worksheet has duplicate rows in the Fruits column, so I have selected the column “Fruits”.
Step (6). Once you are done with the changes in the Remove Duplicates dialog, click on the OK button to find and remove duplicates from the selected columns.
Step (7). If it finds the duplicates, Excel will remove the duplicates and display the message saying how many duplicates were found and removed; and how many unique values remain.
As my sample worksheet has only 2 duplicates “Apple” and “Banana”, Excel removed the duplicate entries and displayed the message “2 duplicate values found and removed; 11 unique values remain.” which is correct.
**