There are two ways to get a list of distinct values in a column in Excel:
Method 1: Use the UNIQUE Function
You can use the UNIQUE function to extract a list of distinct values from a particular range.
Method 2: Use Remove Duplicates Tool
You can use the Remove Duplicates tool from the Data tab to remove all duplicate values in a range and be left only with distinct values.
The following examples show how to use each method in practice with the following column of values in Excel:
Example 1: Use UNIQUE Function
We can type the following formula into cell B2 to extract a list of distinct values from the range A2:A12:
=UNIQUE(A2:A12)
The following screenshot shows how to use this formula in practice:
This formula returns a list of distinct values from column A.
The benefit of using this approach is that you can get a list of distinct values from a range without actually modifying the original range.
Example 2: Use Remove Duplicates Tool
Another way to get a list of distinct values in a column is to use the Remove Duplicates tool.
To use this tool simply highlight the cell range A2:A12, then click the Remove Duplicates icon within the Data Tools group from the Data tab:
In the new window that appears, check the box next to the column you’d like to remove duplicates from (in this case the column is called “Values”):
Once you click OK, all duplicate values will be removed from the column so that only the distinct values remain:
A message box also appears that tells us 4 duplicate values were removed and that 7 distinct values remain.
The benefit of using this method is that you can directly modify the original list of values if you’d like to only have a list of distinct values.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count Unique Names
Excel: How to Count Unique Values by Group
Excel: How to Count Unique Values Based on Multiple Criteria
