Book Online: Login | Register

1300 658 388

Search form



By Marion Taylor, TP3 Senior Desktop Applications Trainer

One of the most common questions we are asked is how to extract a list of unique values from a column in Excel.

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:-

  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.


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

  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



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.



Twitter icon
Facebook icon
Google icon
LinkedIn icon
e-mail icon