How to use the unique function in Microsoft Excel

The UNIQUE function in Microsoft Excel does exactly as its name suggests: it extracts unique values ​​from an array. In other words, you will only see each value once in the result, even if it appears multiple times in the source. Let’s take a look at how it works, how to use it, and how to avoid some of the risks.

Unique function syntax

The UNIQUE function in Excel has three arguments—one required and two optional:

=UNIQUE(a,b,c)

where

• A is the array from which you want to return unique values,
• for A logical value that specifies whether unique rows (FALSE) or columns (TRUE) are returned, and
• C It is also a logical value, but this time it specifies whether all unique values ​​(FALSE) or those that occur only once (TRUE) will be returned.

If you delete the arguments for wow CFALSE is assumed to be the default. This means that only the argument is entered A It means that all unique rows in the array will be returned.

The default behavior of the unique function in Excel is to extract an array while removing duplicates in the process. In other words, even if a value appears more than once in the source data, it will only appear once in the result. You can use this function on a one-dimensional array (one column or row) or a two-dimensional array (two or more columns or rows). Here’s how.

The UNIQUE function is commonly used to clean up a one-dimensional array that contains duplicate entries.

In this spreadsheet, the table named T_PLWinners Lists all Premier League champions since 2010.

Since the source data contains duplicate values ​​because some teams have won the title more than once, your goal is to create a cleaner list that lists each winner only once. In other words, you want to apply the UNIQUE function to the Winner column in the T_PLWinners table. To do this, in cell D2, type:

=UNIQUE(T_PLWinners[Winner])

And press Enter.

In this case, there is no need to provide arguments for and C Because you want to return all unique rows in the array, which is the default behavior. However, if the team list is in Row 2 instead of Column B, you will need to type TRUE For the argument for.

Extract unique values ​​from two or more columns or rows

The UNIQUE function in Excel can also be applied to two or more columns or rows at a time to return unique combinations.

Let’s say you want to extract the first and last names of all award winners from this named table T_Prizesbut only once if they win multiple prizes.

The principle is remarkably similar to extracting unique values ​​from a one-dimensional array, the only change being the cells referenced in the argument A:

=UNIQUE(T_Prizes[[First]:[Last]])

Notice how the formula refers to both the column with the first heading and the column with the last heading. Also, although there are two names in the source data, they are listed as unique names, as they are paired with different surnames.

If you select all columns in a formatted table for the argument AA structured reference contains only the table name, not the column headers.

Use the same method to extract unique column-based groups, but remember to type TRUE For the argument for.

Use the unique function to return values ​​that appear only once

When you ignore the argument C In the UNIQUE function, the result includes all distinct values, whether they appear once or multiple times in the source data. However, write TRUE For the argument C Forces Excel to return a list of values ​​that appears only once.

The table below is named T_transactionsand you want to contact all customers who have made just one transaction with you.

This is where the final argument to the UNIQUE function comes into play:

=UNIQUE(T_Transactions[Customer],,TRUE)

Type TRUE for the arguments for and C In the same formula, columns whose unique values ​​appear are returned only once.

Use the unique function alongside other Excel functions

The power of Excel’s unique functionality becomes truly evident when combined with other functions.

Count and Unique: Calculate unique values

Nesting UNIQUE into COUNTA counts the number of unique values ​​in the array.

This table, called T_PremChamp, lists the Premier League winners in each season since 2010. You want to use this data to determine how many different teams have won the Premier League in that period.

In the following formula, the UNIQUE function produces a theoretical list of all teams without any duplicates, and the COUNTA function counts the number of cells that this theoretical list will occupy:

=COUNTA(UNIQUE(T_PremChamp[Winner]))

Since UNIQUE is a dynamic array function, the resulting value will increase if a new team is added to the table.

Sort and Unique: Extract and sort unique values

When used alone, the UNIQUE function returns values ​​in the order in which they appear in the original data set. For example, use UNIQUE to return all unique values ​​from the WINNER column T_premew The table here returns Chelsea as the third value, even though it is closest to A alphabetically.

To fix this, you need to merge the above unique formula inside SORT. This forces Excel to create a theoretical list of unique values, and the SORT function returns this list in alphabetical order:

=SORT(UNIQUE(T_PremW[Winner]))

Unique and Filter: Extract unique values ​​based on criteria

The FILTER function in Excel allows you to extract values ​​from a data set based on certain criteria you set. Nesting it within UNIQUE means that if a given value matches the criteria more than once, it will not be repeated in the result.

In this example, the names of all winners who received more than 50% of the votes in a given year are extracted from T_sounds table using the FILTER function.

=FILTER(T_Votes[Name],T_Votes[Vote%]>0.5)

However, Patricia Naylor appears more than once in the data source, that is, it appears more than once in the result. So, to fix this problem, and return each name only once, merge the formula inside UNIQUE:

=UNIQUE(FILTER(T_Votes[Name],T_Votes[Vote%]>0.5))

UNIQUE, FILTER, AND COUNTIF: Extract values ​​that occur more than once

In the final example, the UNIQUE function can be used in conjunction with FILTER and COUNTIF to extract values ​​that occur more than once.

Here, the names of individuals whose names appear at least twice are extracted using this formula:

=UNIQUE(FILTER(T_PWin[Name],COUNTIF(T_PWin[Name],T_PWin[Name])>1))

Using FILTER and COUNTIF will only include names that appear at least twice, but will be repeated in the result.

Therefore, the UNIQUE function removes these duplicates to ensure that each name appears only once.

Issues you may encounter when using UNIQUE in Excel

As with all functions in Excel, some scenarios and environments prevent it from working as you expect. Here are some hurdles you may face, and what you can do to jump them:

The UNIQUE function is not the only one in Excel that can be used to extract data. For example, the CHOOSECOLS and CHOSOEROWS functions allow you to extract specific columns or rows, the GETPIVOTDATA function allows you to extract specific information from a PivotTable, and with the DROP function, you can remove a specified number of rows or columns from the beginning or end of an array.