UNIQUE Function – Mastering Excels Array Tools

 

The UNIQUE function is used to extract a range or array of unique values from a given array.

The article below must help you learn all that you need to know about it.

Using array functions can be a tricky concept, and is covered in our more advanced Excel training.

Excel Unique Function

Syntax

The syntax of the unique function consists of three arguments.

Arguments

The three arguments offered by the UNIQUE function syntax are analyzed below.

• Array – This represents the range from where the list of unique values is to be extracted.
• By_col: This is an optional argument. It specifies if the uniqueness of the values is to be compared by column or by rows. ‘By Column’ equates to 0 or False, whereas, ‘by Row’ equates to 1 or True.

You may set it as 1 (true) or as 0 (false). When omitted, Excel by default takes is equivalent to 0 (false).

• Exactly_once: This is an optional argument.

If set to 1 (true), it only gives back the items that appear in the data for once.

Set to 0 (false), it returns a list of only the unique values i.e. it eradicates the instances of duplication of data, if any.

If omitted, Excel by default sets this argument to 0 (false).

Return Value

Excel returns an array of all the unique values.

Functions Library

It can be accessed from the Functions library as follows.

 

Using UNIQUE – Basic Example

Taking a look at the example below can help us learn about the basic functioning of the UNIQUE function better.

The screenshot below represents records from the procurement department.

It shows the purchase requisition raised by team members from different departments.

The same stationery item has been requested by multiple team members and appears many times in the list of stationery items.

To derive a list of stationery items to be ordered, you may want to extract a unique list of items.

Here’s how you can do it using the UNIQUE function.

If you want your own array to work on, learn how to create arrays and make one!

Step 1:

To extract a list of unique items from the given range, compose the UNIQUE function as follows.

= UNIQUE (C2:C12)

The above syntax is not as simple as it appears to be. Here’s how it is supposed to work.

• The range is set to C2:C12 which contains the list of stationery items to be filtered.
• The second argument is omitted as we want the filtration to be done by column.
• The third argument is omitted as we only want a list of unique values.

The above function can alternatively be composed as follows.

Step 2:

After you’ve set up the formula in the cell where you want the unique values to be populated in a columnar shape, hit enter.

Now all the data is pulled through correctly, you can use SORT and SORTBY to help filter down for what you need to find.

UNIQUE Use cases

Here are a few uses for the UNIQUE Function.

1 – As an auditor, you may want to filter our data for data sampling purposes.

The UNIQUE function can help you fetch the desired data only through a few clicks.

2 – When importing data from the web and other sources, the imported data is often jumbled up and not in very good shape.

The UNIQUE function can help with data sophistication of large data sets.

3 – This function can also be of help for academic result compilation if you want to filter out students who passed one or more subjects.

If you just need some data to practice this function on, try using the RAND Function to create some sample data!

UNIQUE function troubleshooting:

The UNIQUE function is a convenient substitute to the conventional methods for extracting distinct values.

Although this function has a relatively straightforward application, you may confront the following problems with the UNIQUE function.

1. #SPILL! Error

The unique values sought through the UNIQUE function are likely to take the shape of a row or a column.

Taking the example below, the list of unique stationery items is populated by Excel in six cells, starting from the cell where the formula is fed under Column C.

However, if any of these cells had been populated with any value, the results would have been different.

Instead of extracting the list of unique items, Excel gives the #SPILL! Error.

This is because cell C4 contains a value already. Delete this value and then enter the formula to resolve the error.

2. #VALUE! Error

The #Value! Error is returned by Excel when the formula is not rightly fed.

It is usually because of any error made within the composition of the arguments.

To get rid of #VALUE! Errors, use formula auditing to help find where the error lies.

Conclusion:

The modern dynamic array tools offered by Excel are one of a kind.

And you can master the same by putting in a little effort.