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.

An Excel table and unique formula

© paula guilfoyle
This article is from the free online

Excel Skills to Make an Impression

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education