Remove Duplicates From Excel 2010

The ability to remove duplicate entries from a list is a useful one, and one that can be used in a variety of every day situations. Imagine, for example, that you are writing a shopping list and are copying and pasting ingredients from several recipes into an excel spreadsheet. Some ingredients will be repeated. Garlic is an ingredient in spaghetti bolognese, chicken and ginger, and so on. In your spreadsheet, many ingredients may be duplicated several times. How do you remove these duplicates?

It’s easier than you think. The hardest part is finding the command to do it!

That very command is found on the Data tab in the Data Tools group.

Remove Duplicates Command

Here is a sample spreadsheet with ingredients in it. Open that in Excel and then click the Remove Duplicates button. The Remove Duplicates window opens and allows you to confirm the column that you will perform the operation on. In our simple spreadsheet, Excel rightly selects Column A.

Remove Duplicates Window

Click OK and a confirmation dialogue informs us that 4 duplicate values were found and removed (18 unique values remain).

If you tend to do a lot of removing duplicates, why not add the command to the Quick Access Toolbar? That way you won’t have to jump to the Data tab every tim you want to use it. Right click on the Remove Duplicates command and select Add to Quick Access Toolbar.

Add to Quick Access Toolbar

Now the command will appear in the Quick Access Toolbar (in top left hand corner of the screen). If you later decide to remove it again, it’s a simple case of right clicking on it and selecting Remove from Quick Access Toolbar.

Remove Duplicates Command in the Quick Access Toolbar