UNIQUE Function – Mastering Excels Array Tools

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

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

Excel Unique Function

Syntax

Syntax of the unique function consists of three arguments.

= UNIQUE (array, [by_col], [exactly_once])

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.

Formulas > Functions Library > Lookup & Reference > UNIQUE Function

 

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.

The UNIQUE function of Excel can also be of great use to other professions including researchers, statisticians, chemists, etc.

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.

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.

= UNIQUE (C2:C12, 0, 0)

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.

 

The list produced by Excel contains all stationery items for once i.e. all duplicated values as appearing in the original column are eradicated.

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, you may try auditing your formulas for accuracy.

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.

Need an overview of everything Arrays can do in Excel? Check out our guide to Excel array formulas here.