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 you 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 key 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 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
