UNIQUE Function in Excel – another way of filtering out data in your spreadsheets

UNIQUE Function in Excel – have you used this function before to filter out data in your spreadsheets?

UNIQUE Function in Excel: Excel iconThis week’s hint and tip is on the UNIQUE Function in Excel. Functions, also known as formulas, are used by many people in Excel on a daily basis. We have previously looked at different ways to filter in Excel through the more standard routes that most have heard of. The UNIQUE function gives another way in which you can filter out data in your spreadsheet. We cover a lot of functions in our courses but we don’t cover the UNIQUE one specifically so decided to do a hint and tip on it. We are going to go through it now below.


The UNIQUE Function

The UNIQUE function works in a similar way to Advanced Filtering on the Data Tab but with another variant as you will see below and in the video we have produced.

The layout of this function is: =UNIQUE(Array,By_col,Exactly_once)

This function has 3 parts (or arguments) to it which are talked through more in the video below:

  1. Array: the range of cells in which you want to return unique rows or columns from
  2. By_col: you either type in FALSE or TRUE
    • FALSE – to return the unique rows
    • TRUE – to return the unique columns
  3. Exactly_once: you either type in FALSE or TRUE
    • FALSE – to return rows or columns that only occur once in the array
    • TRUE – to return all the unique rows or columns that occur in the range

As there are multiple arguments in this function there is more than one variation that you can use in your spreadsheets. We are gong to look at 2 examples in this hint and tip below.


Variant 1

The first variation is to use UNIQUE to select from an array of data in a column the unique rows where they only appear once.

UNIQUE function in Excel: Variation 1 screenshot


Variant 2

The second variation is to use UNIQUE to select from an array of data in a column all the unique rows not just those appearing once.

UNIQUE function in Excel: Variation 2 screenshot


The video below goes through how to use this function in the spreadsheet attached here (clicking here will download a copy to your computer so you can try it out!). We hope that you find the video useful and enjoy learning about it!

Take a look below at the video to find out more!

We hope you liked this hint and tip on the UNIQUE Function in Excel, why not take a look at our previous one on using the automatic updating feature of Styles in Word?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy