This blog is part 3 of a series that looks at the amazing functionality of some of the most popular Office 365 Excel functions. In parts 1 and 2 the focus was on the FILTER and SORT functions. Building on this, we will explore how useful the UNIQUE function is in creating  unique lists more efficiently.

What is the UNIQUE function and why is it useful

The UNIQUE function lets you extract all the multiple entries in a list and populates them as single entries in a unique list. For example, clients that do business with a company may place many different orders so their name will get repeated many times. Having a unique list of clients can help to show summaries of total orders or sales.

How is it different from other functions?

The UNIQUE function is one of a number of Office 365 functions that behaves differently to all other Excel functions. With the UNIQUE function, the results automatically ‘spill’ into all available cells below. In contrast, all other functions require the result to be copied down manually.

How to apply the function

Let’s take a list of names where some of them appear more than once – see below:

1. Type =UNIQUE(A1:A14) in an empty cell to produce the following unique list:

   
   2. To return a range from a horizontal list do the following:

   

   A) Select the range of cells in the list e.g. E1:R1Type a comma and then TRUE – B) this returns all unique items across all the columns.

   Another really useful feature of UNIQUE is being able to produce a list of all items that appear only once. From the original list, we can see all the items that have no duplicates i.e.. those that display no colour (see below):

   
   3. To return a distinct list type =UNIQUE(A1:A14,,TRUE) where:
   A) the 2 commas mean “bypass the 2^nd part” because the list is vertical not horizontal
   B) TRUE returns only items that appear once in the original list 

   The result is the following list:
   

   One thing to look out for is if any data is ‘blocking’ this space then you will get a #SPILL error. Consequently, the resulting data will not populate the cells. (See below)

   To remove the spill error, simply delete the data that is blocking the spill. Other Office 365 functions such as SORT and FILTER also have this ‘spill’ feature.

   Conclusion

   The Excel UNIQUE function is an amazing tool that allows you to extract specific data quickly and efficiently from a list of multiple entries.