UNIQUE

The UNIQUE function allows us to filter out the unique values from a range of cells in a number of different ways using array functionality.

In our example, we have a list of employees, where we want to produce a separate list of all the departments in the company.

UNIQUE will give us a list, and each department in the column will appear only once.

Let’s start by selecting the cell we want to be at the top of the list, and write =UNIQUE and left parenthesis.

The first argument is the cells we will use, which for us will only be the Departments.

In the second argument, we specify whether we want to return unique columns or rows.

If we had shown the departments horizontally across the columns as opposed to the rows as it is now, we would have selected TRUE for this argument.

Instead, we enter FALSE to show that the departments are shown in the rows.

In the third argument, we tell Excel if we want to return all the unique values that exist, or if we want to return those values that occur only once.

We’ll try entering True, end the function and hit enter, and we now see that we only get Administration and Security, as they only occur once in the department list.

Instead, we go into the function and change the third argument to FALSE, and when we hit enter again, we have a complete list of the departments in the company.

We can now use this list in other functions, but it can sometimes get a little tricky because you can’t copy the results of a dynamic array function over to other cells.

Therefore, we’ll select the list, and paste the values into the first cell.