Indzara

Find Unique Values with Formulas

Unique values are those that appear exactly once in a list.

Let us learn how to extract the unique values from a list in Excel using a sample scenario: Consider we have a list of employee names:

Unique Values with Formulas list of data

Let us extract only the unique employee list from this and before we get to the formula, let us convert the same into a table (something we always recommend to do 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.

Unique Values with Formulas 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.

Now, to get the unique list of values, that is the values that appear at least once. We use the in-built Excel function, UNIQUE

What does Unique do?

Unique Values with Formulas 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?

Unique Values with Formulas SORT function

As shown, your list will instantly extract the unique values.

Unique Values with Formulas extracted unique values
Unique Employee List

We have a dedicated YouTube video that explains how to extract UNIQUE values in Excel:


Leave a Reply

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