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

By on August 25, 2022

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.

About Thea

You must be logged in to post a comment Login