Exceljet

Quick, clean, and to the point

Pivot table list unique values

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.

Data

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).

Fields

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

Pivot table list unique values fields

Steps

  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.

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.