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.

The following tutorials explain how to perform other common tasks in Excel: