Indzara

Get Distinct Values in Excel

Distinct values are those that appear at least once in a list. To extract them using Excel, consider a sample data of employee names as shown:

Distinct Values List of Data

Before we get to the formula, let us convert the same into a table (something we always recommend doing before applying any function to increase formula readability and for scalability when the data expands).

To convert your data into a table, select the data (as a whole), press CTRL + T and assign a name.

Distinct Values select data

Now, assign the range of values (in our employee column) a name from the Name Manager.

In our sample, let the list of data be named EMPLIST.

To get distinct values in Excel, that is the values that appear AT LEAST once, we use the in-built Excel function, UNIQUE

What does Unique do?

Distinct Values UNIQUE function

Use the SORT function around the Unique formula you just created to sort this data if you need it sorted in alphabetical order.

What does SORT do?

Distinct Values SORT function

As shown, your list will instantly extract the distinct values

Distinct Values extracted distinct values
List of Distinct Employee Names

We have a dedicated YouTube video that explains how to get the distinct values in Excel:

Leave a Reply

Your email address will not be published. Required fields are marked *