Exceljet

Quick, clean, and to the point

The UNIQUE function

In this video, we'll introduce the UNIQUE function.

One of the new functions that comes with the dynamic array version of Excel is UNIQUE. The UNIQUE function lets you extract unique values in a variety of ways.

The UNIQUE function takes three arguments. The first argument, "array" is the source data your are working with. The second argument, "by_col" means by column. Use this when data should be compared across columns instead of rows. The last argument, exactly_once, tells UNIQUE to return values that only appear once in source data.

On this first worksheet, I've got a list of 16 fruits with some duplicates. Let's use the UNIQUE function to extract a list of unique fruits.

To enter the function, I'll type an = sign, then "uniq", and then I'll hit the tab to confirm.

UNIQUE has just one required argument, called "array". If you have a normal vertical list of values in rows, this is the only argument you need to provide.

When I give UNIQUE the range B5:B20, and hit enter, we get a list of 12 unique fruits in a dynamic array.

If I overtype one of the values in the source data, you can see that the spill range immediately updates.

I'll undo that change.

UNIQUE has another argument called "exactly_once" that allows you to retrieve unique values that appear just once in the source data.

By default, this argument is FALSE. But if I set the value to 1 or TRUE, you can see we get an even smaller list. These are the fruits that appear in the column B only once.

Since "apple" appears more than once, it is not included.

You'll sometimes hear these values described as "distinct" as opposed to "unique".

On the next sheet, I have the same data but in a horizontal format.

In C6, if I enter UNIQUE function without any arguments, you can see we don't get unique values.

I need to set the "by column" argument to 1 or TRUE to tell UNIQUE to compare values across columns.

Once I do that, we get the same list that we saw on the first worksheet.

The same is true if I want to extract fruits that appear exactly once in the source data.

If I try to set exactly once to 1 and leave "by column" blank, it doesn't work. I need to set both arguments to 1 (or TRUE) to get a list of fruits that appear just once in the source data.

Dynamic Array Formulas are available in Office 365 only.

Related shortcuts

Author 
Dave Bruns