To extract a list of unique values from a data set, you can use a pivot table. In the example shown, the color field has been added as a row field. The resulting pivot table (in column D) is a one-column list of unique color values.


The data in this pivot tables comes from the Excel Table in column B. Excel Tables are dynamic and will automatically expand and contract as values are added or removed. This allows the Pivot Table to always show the latest list of unique values (after refresh).


The pivot table shown in the example has just one field in the row area, as seen below.

Pivot table list unique values fields


  1. Define an Excel Table (optional)
  2. Create a Pivot Table (Insert > Pivot Table)
  3. Add the color field to the Rows area
  4. Disable Grand Totals for rows and columns
  5. Change layout to Tabular (optional)
  6. When data changes, Refresh pivot Table for latest list.