Skip main navigation

Creating a Unique List in Excel

In this article by Paula Guilfoyle you will learn how to use Excels Unique function
Creating A Unique List In Excel
© paula guilfoyle

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.

An Excel table with columns for date, product, sales rep, customer, and value

In old Excel, there are many options. You could:

  1. Write a complex formula.
  2. Remove duplicates.
  3. Load the data into Power Query.
  4. Use Advanced Filter.

Advanced Filter is fine when working with static data. But, if your data changes, the unique list will not update automatically.

An Excel table and advanced filter

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]) ![An Excel table and unique formula](https://i.imgur.com/n1oARMx.png)

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.

© paula guilfoyle
This article is from the free online

Excel Skills to Make an Impression

Created by
FutureLearn - Learning For Life

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.

Start Learning now