Posted On October 29, 2015 By Marion Taylor

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.

newsletter1

 

To extract unique values from a list such as that shown above, follow these steps:-

  1. Select the range of cells, or make sure the active cell is in a table.
  2. Click on the Data ribbon tab,
  3. Click on Advanced in the Sort & Filter group.
newsletter2

 

Note: If using Excel 2003, click on the Data menu, Filter, Advanced Filter

The Advanced Filter dialog box will be displayed

newsletter3

 

In the Advanced Filter dialog box:

Check that the column to be filtered is selected and appears in the “List Range” box

  1. Click Copy to another location.
  2. In the Copy to box, enter a cell reference (or click on the cell)
  3. Select the Unique records only check box, and click OK.

The unique values from the selected range will be copied to the new location

newsletter4

 

Note: If you want to filter the list within the table, select Filter the list, in-place

That’s it!


If you’d like to learn more about manipulating and summarising large datasets, join our Excel Intermediate Level 2 and Excel Advanced Level 1 courses in Sydney or Melbourne.

Check out our full range of Excel courses here.