How to Get Unique/Distinct Values From a Column in Excel

Lets say that you have a column with values, and you need to find out what distinct/unique values are in there.

Note: Goal is not see how many are there but to find an actual list of those values.

This is similar to SQL's

SELECT Distinct(Column) FROM Table

  1. Go to Data Ribbon menu -> Sort & Filter section and click the Advanced button.

    Excel Data/Advanced button
    Excel Data/Advanced button - Click to enlarge
  2. Fill in Advanced Filter dialog. Make sure to select Copy to another location and check Unique records only.
    Copy to is the place where the unique data will be placed at.

    Excel - Advanced Filter Dialog
    Excel - Advanced Filter Dialog - Click to enlarge
  3. Unique values from source list are copied to new list starting at cell selected in Copy to property of Advanced filter dialog.

    Excel - Unique values as a result
    Excel - Unique values as a result - Click to enlarge

Leave a Comment