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
- Check Copy to another location radio button.
- 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.
