This method of data removal works in all versions of Excel.
Before invoking the Remove Duplicates feature, let’s make a copy of the data set by:
- clicking in the data and pressing CTRL-A two times,
- click CTRL-C to copy the selected data into memory,
- click in a new location on the sheet and press CTRL-V to paste the copy of the data into the new location.
To remove the duplicate rows from the copy of the data set:
- Click in the newly copied data set.
- Select Data (tab) -> Data Tools (group) -> Remove Duplicates.
- In the Remove Duplicates dialog box, ensure all the listed column headings and the option labeled “My data has headers” are checked and press OK.
The system reports back the number of duplicates that were removed along with the number of unique entries that remain.
Removing the duplicate rows based on a single column
If you wanted to remove all duplicated rows of the table based solely on the “Sales Agent” column, you would follow the above process but only check the box for “Sales Agent” in the Remove Duplicates dialog box.
This would result in the following “before and after” snapshot of the data.
NOTE: Only the row for the first encountered “Sales Agent” name is retained. All rows that follow with the same “Sales Agent” name are removed.
If new records are created in the original table that duplicates existing records, the deduplicated version will no longer accurately reflect the source. The “old” list of unique records would need to be deleted and the Remove Duplicates process would need to be re-executed.