List unique values in an Excel range

No matter what you use Excel for, you will always come across the need to generate a list of unique values from a range. Or in other words the need to get rid of duplicates in a list.

The following trick is a quick and easy way of getting a list of unique values using the “Advanced Filter” function.

In our example there are 8 names listed but only 6 of them are unique (click on images for an enlarged image)

Fig 1 - Our sample list of names

Step 1 – Select the entire column by clicking on the column letter

Step 2 – Copy the selected cells by either pressing Ctrl & C or by going to the Edit drop down menu and selecting copy

Step 3 – Find a spare area of the worksheet to work in, or select an empty sheet and paste the data in to it.

Fig 3 - Our cells pasted in to a spare area

Step 4 – Select one of the cells you just pasted in to place

Step 5 – In the Data menu select Filter > Advanced Filter. In the 2007 ribbon, go to the Data tab and click on the Advanced button in the “Sort & Filter” section.

Step 6 – Select if you want to sort the list in its current place or in to another location. As we already have the original list, select the first option.

Fig 4 - The advanced filter window

Step 7 – Click the “Unique records only” option and click OK

Fig - 5Our sorted list

You now have a list free of duplicates containing only unique values.

-

-

-