MVP #51: Removing Duplicate Values In Excel

Occasionally, you will have a table and you’ll want to remove duplicate entries. If it were a sales transaction table, you might want to remove the duplicate sales entries. In the example below, it is a table of items (Pizzas) and we want to remove the duplicate entries leaving only unique entries.



As illustrated above, you select the entire records first and then go to Data menu, click on Remove Duplicates. You will get a confirmatory dialog box. Click on OK.



You’ll see a result showing the number of duplicate values that were found and the number of unique values found. Basically, what Remove Duplicates does is it leaves one record of each item and removes all the extra record for that item that it finds. 



When you select a table with more than one field entry, the Remove Duplicates remove only the entries that have same value in all the fields as a previous entry except if you specify which fields to exclude in the search for duplicates.

In the screenshot below, we excluded Car Sales from the fields to include in the search for duplicates. So rows that have the same entries in all the other fields will be deleted except the first entry.