This post will guide you how to extract unique values from a given range in Excel. How do I use the Advanced Filter to extract a list of unique values in Excel.

Extract Unique Values

---

Assuming that you want to extract a list of unique values from a given range: B1:B5 in your worksheet, and then select them. How to do it. You can use the Advance Filter feature to filter for unique values or remove duplicate values in one column. Here are the steps:

#1 go to DATA tab, click Advanced command under Sort & Filter group. And the Advanced Filter dialog will open.

#2 select Filter the list, in-place option in the Advanced Filter dialog, and choose one range that you want to filter the unique values in the List range text box, and then check Unique records only checkbox. Click OK button.

#3 all unique values have been filtered out in your worksheet. And all duplicated values have been hidden.

If you want to remove all duplicated values, you can also use Remove Duplicates command to achieve the result. When you remove duplicate values, the only effect is on the values in the range of cells or table. Other values outside the range of cells or table will not change or move.  When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.

Because you are permanently deleting data, it’s a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values. Here are the steps:

#1 select the range of cells that you want to remove duplicates.

#2 go to DATA tab, click Remove Duplicates command under Data Tools group. And the Remove Duplicates dialog will open.

#3 click OK button in the Remove Duplicates dialog box. And a message will appear to indicate how many duplicate values were removed, or how many unique values remain. Click OK to dismiss this message.