The new Unique() dynamic array function doesn’t let you do anything new in Excel, but it makes a once-complicated task into simplicity itself. Unique or distinct, no-duplicate lists, counting and sorting the results are all possible with easy formulas that won’t confuse.
For as long as there’s been Excel, people have wanted to get ‘unique’ or ‘distinct’ lists from long tables of data:
- All the names or items from a source list, each name only appears once in a DISTINCT list. No matter how many times it appears in the source, e.g. getting customers or products from a list of transactions.
- Just the items that appear only once in a source list known as a UNIQUE list. Ignoring any items that appear more than once.
- Items that appear more than once (i.e the Distinct list without Unique items). We’ll call these Repeat items though that’s not an accepted term. The Unique() function can’t do this directly but it’s possible with a little extra trickery.
The complete example for this article. The source table, ‘MOCRCustomers’, at left. At right are three Unique/Distinct lists made by Unique(). All Customers (Distinct in Col E), One-Time Customers (Unique in Col G) and Repeat Customers (not Unique in Col K).
So many uses for these unique/distinct lists.
- Grabbing a list of customers or products from a table of individual sales, see above.
- Removing possible duplicate transactions or items from a table (part of ‘normalizing’ data).
For a long time, Excel had a Remove Duplicates option on the Data tab, but that Distinct list is done just once. If the source list changes, the ‘no duplicates’ list isn’t updated. And it only deletes duplicate values, Remove Duplicates doesn’t make a list of ‘unique’ once-only items.
Enter Unique()
Enter Unique() one of the dynamic array functions that will handle both distinct and unique requirements AND refresh automatically.
It’s been possible in Excel by various workarounds but now all you need is Unique(). It’s not only simpler but also faster and automatic.
Unique() is part of the dynamic array features in Excel for Windows/Mac and elsewhere. It will return either a list of the values with duplicates removed OR a list of the values that only appear once in a larger list. The syntax is:
=UNIQUE (array, [by_col], [occurs_once])
array – the source range, table or array
by_col - Sorting By row = FALSE (default); by column = TRUE.
occurs_once – FALSE = all values with duplicates removed (default). TRUE = values that occur only once in the source list.
Just the names or labels
Unique() lets you get a list of names or labels from a much longer list. For example, you want a list of your customers based on sales history. You only need each name once.
In this and all the examples the source list is on the left. It’s quite short but your list could be hundreds or thousands of rows long.
On the right is the list of all customers. The many duplicated names are only shown once.
The formula for Column E is:
=UNIQUE(MOCRCustomers[Customer],FALSE,FALSE)
MOCRCustomers[Customer] – the list to de-duplicate
FALSE – search by Row
FALSE – don’t limit to values found only once.
One time only items
Maybe you only want to see the items that only appear once. These could be ‘one time’ customers or data entry errors that need fixing.
The formula for Column G is:
=UNIQUE(MOCRCustomers[Customer],FALSE,TRUE)
MOCRCustomers[Customer] – the list to de-duplicate
FALSE – search by Row
TRUE – limit to values found only once.
(the two formulas are the same, except for the last parameter).
As you can see above, this Unique list shows up some data entry errors. ‘Christopher’ instead of ‘Chris’ elsewhere. The famous EECOM Sy Liebergot has one purchase with a common spelling error. Change those two mistakes and the Unique list refreshes right away.
There’s another common need for unique or distinct lists, what we call ‘repeated’ values. See the ‘Repeat Customers’ column K above. We’ll explain how to do that in a follow up article, Extend UNIQUE() for distinct values that appear more than once
Great and simple ‘live’ sorting with filtering in Excel
Better live sorting in Excel 365
Easy and better lists with Excel 365’s Filter()
Extend UNIQUE() for distinct values that appear more than once
Unique filter in Excel – the manual ‘one-off’ options
Three ways to make a unique list in Excel, the hard way and the new way.
