Extract distinct values from a column with Excel’s Advanced Filter

If you don’t want to waste time on figuring out the arcane twists of the distinct value formulas, you can quickly get a list of distinct values by using the Advanced Filter. The detailed steps follow below.

  • Select the column of data from which you want to extract distinct values.
  • Switch to the Data tab > Sort & Filter group, and click the Advanced button:

  • In the Advanced Filter dialog box, select the following options:
    • Check Copy to another location radio button.
    • In the List range box, verify that the source range is displayed correctly.
    • In the Copy to box, enter the topmost cell of the destination range. Please keep in mind that you can copy the filtered data only to the active sheet.
    • Select the Unique records only

  • Finally, click the OK button and check the result:

Please pay attention that although the Advanced Filter’s option is named “Unique records only“, it extracts distinct values, i.e. unique values and 1st occurrences of duplicate values.