Creating a Unique List in Excel
In this article, we are going to explore the first of our new Dynamic Array functions. The UNIQUE function. As the name describes, the UNIQUE function will allow you to extract a unique list of items.
Our Example
Consider the following table of sales data. It is a small table but it is perfect for our example. Suppose you need a unique list of Products.
In old Excel, there are many options. You could:
- Write a complex formula.
- Remove duplicates.
- Load the data into Power Query.
- Use Advanced Filter.
Advanced Filter is fine when working with static data. But, if your data changes, the unique list will not update automatically.
There are too many steps when using Power Query for the sole purpose of extracting a unique list. And if you were to use a formula, it would be a complex array formula.
However, obtaining a unique list is easy with Excel’s new UNIQUE function.
UNIQUE Function in Excel
The syntax for UNIQUE is:
=UNIQUE (array, [by_col], [occurs_once])
We will explore the full detail of the UNIQUE function at a later stage. But as we know parameters in excel functions surrounded with square brackets [ ] can often be left out as they are optional.
Therefore, to get a unique list of the Products, the only parameter we need to feed the UNIQUE function is the array.
By using the formula (in only one cell):
=UNIQUE (sales [Products]) 
As we entered an array, Excel will return an array. On hitting Enter, not only will the cell with the formula return a value, but the unique list will Spill down to other cells.
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
