There are many reasons you may frequently need to extract only the unique distinct values from a column in an Excel worksheet. For example, a mailing list where names may be duplicated, product lists where the same product ID appears multiple times or as with the simple example shown here, a list of departments. There are several methods using formulas and some can be quite complex and beyond the knowledge of a casual Excel user. One simple method which is often overlooked is the use of the Advanced Filter Tool.
Using Advanced Filter, the unique values can be filtered in place or extracted to another location on the same worksheet or another worksheet.
To extract unique values from a list such as that shown above, follow these steps:-
- Select the range of cells, or make sure the active cell is in a table.
- Click on the Data ribbon tab,
- Click on Advanced in the Sort & Filter group.
Note: If using Excel 2003, click on the Data menu, Filter, Advanced Filter
The Advanced Filter dialog box will be displayed
In the Advanced Filter dialog box:
Check that the column to be filtered is selected and appears in the “List Range” box
- Click Copy to another location.
- In the Copy to box, enter a cell reference (or click on the cell)
- Select the Unique records only check box, and click OK.
The unique values from the selected range will be copied to the new location
Note: If you want to filter the list within the table, select Filter the list, in-place
Check out our full range of Excel courses here.