Microsoft Excel’s UNIQUE function does exactly as its name suggests—it extracts unique values from an array. In other words, you’ll only see each value once in the result, even if it appears multiple times in the source. Let’s look at how it works, how to use it, and how to avoid certain pitfalls.
The UNIQUE function is available to those using Excel for Microsoft 365, Excel for the web, the Excel mobile and tablet apps, and one-off versions of Excel released in 2021 or later.
OS
Windows, macOS, iPhone, iPad, Android
Brand
Microsoft
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
The UNIQUE Function Syntax
Excel’s UNIQUE function 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,
- b is a Boolean value that determines whether unique rows (FALSE) or columns (TRUE) are returned, and
- c is also a Boolean value, but this time, it defines whether all unique values (FALSE) or only those that occur exactly once (TRUE) are returned.
If you omit arguments b and/or c, FALSE is assumed as the default. This means that inputting only argument a 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’ll only appear once in the result. You can use this functionality on a one-dimensional array (a single column or row) or a two-dimensional array (two or more columns or rows). Here’s how.
To follow along as you read this guide, download a free copy of the Excel workbook used in the examples. After you click the link, you’ll find the download button in the top-right corner of your screen, and when you open the file, you can access each example on a separate worksheet tab.
The UNIQUE function is most commonly used to clean up a one-dimensional array that contains repeated entries.
In this spreadsheet, the table named T_PLWinners lists all Premier League champions since 2010.
The UNIQUE function is case-insensitive. This means that it would treat Geek, geek, and GEEK as the same text string.
Given that the source data contains repeated values as some teams have won the title more than once, your aim is to create a cleaner list that only lists each winner once. In other words, you want to apply the UNIQUE function to the Winner column of the T_PLWinners table. To do this, in cell D2, type:
=UNIQUE(T_PLWinners[Winner])
and press Enter.
Rather than typing the structured reference to the column header manually, hover your cursor over the column header until you see a small, black down arrow, and click once to select the whole column.
In this case, there’s no need to provide arguments b and c because you want to return all unique rows in the array, which is the default behavior. That said, if the list of teams ran across row 2 instead of column B, you would need to type TRUE for argument b.
Excel’s UNIQUE function is a dynamic array function, meaning the result spills from the cell where you typed the formula. It also means that when you add more rows of data to the source table, the result updates to reflect the changes.
Extracting 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 simultaneously to return unique combinations.
Let’s say you want to extract the first and last names of all prize winners from this table, named T_Prizes, but only once if they have won multiple prizes.
The principle is pleasantly similar to extracting unique values from a one-dimensional array—the only change being the cells referenced in argument a:
=UNIQUE(T_Prizes[[First]:[Last]])
Notice how the formula references both the column headed First and the column headed Last. Also, even though there are two Tims in the source data, they are both listed as unique names, as they’re paired with different surnames.
If you select all columns in a formatted table for argument a, the structured reference contains the table name only, not the column headers.
If, after extracting the distinct values, you want to turn the result from a dynamic array to a fixed array, select the cells, press Ctrl+C to copy them, and then press Ctrl+Shift+V to paste them as values. This removes the formula while retaining the cell contents.
Use the same method to extract unique column-based combinations, but remember to type TRUE for argument b.
Using the UNIQUE Function to Return Values Appearing Only Once
When you omit argument c in the UNIQUE function, the result includes all distinct values, whether they appear once or multiple times in the source data. However, typing TRUE for argument c forces Excel to return a list of values that appear only once.
The table below is named T_Transactions, and you want to contact all the customers who have only had one transaction with you.
This is where the final argument of the UNIQUE function comes into play:
=UNIQUE(T_Transactions[Customer],,TRUE)
Typing TRUE for arguments b and c in the same formula returns columns whose unique values appear only once.
Using the UNIQUE Function Alongside Other Excel Functions
The power of Excel’s UNIQUE function truly becomes evident when it’s combined with others.
All the examples below only use argument a of the UNIQUE formula, though you can apply the same principles to UNIQUE formulas whose arguments b and/or c are the non-default Boolean value, TRUE.
COUNTA and UNIQUE: Counting Unique Values
Nesting UNIQUE in COUNTA counts the number of unique values in the array.
This table, named T_PremChamp, lists the winner of the Premier League 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 the teams without any duplicates, and the COUNTA function counts the number of cells that this theoretical list would occupy:
=COUNTA(UNIQUE(T_PremChamp[Winner]))
Because UNIQUE is a dynamic array function, the resultant value will increase if a new team is added to the table.
SORT and UNIQUE: Extracting and Sorting Unique Values
When used on its own, the UNIQUE function returns the values in the order in which they appear in the original dataset. For example, using UNIQUE to return all unique values from the Winner column of the T_PremW table here returns Chelsea as the third value, even though it’s the closest to A alphabetically.
To fix this, you need to nest the above UNIQUE formula inside SORT. This forces Excel to generate a theoretical list of unique values, and the SORT function manifests this list in alphabetical order:
=SORT(UNIQUE(T_PremW[Winner]))
UNIQUE and FILTER: Extracting Unique Values Based on Criteria
Excel’s FILTER function lets you extract values from a dataset based on certain criteria you set. Nesting it inside UNIQUE means that if a certain value matches the criteria more than once, it’s not duplicated in the result.
In this example, the names of all winners who have gained more than 50% of the vote in a given year are extracted from the T_Votes 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, meaning she appears more than once in the result. So, to fix this, and return each name only once, nest the formula inside UNIQUE:
=UNIQUE(FILTER(T_Votes[Name],T_Votes[Vote%]>0.5))
If you want the result of the unique, filtered list to be alphabetical, nest the whole formula inside SORT.
UNIQUE, FILTER, and COUNTIF: Extracting Values That Occur More Than Once
In a final example, the UNIQUE function can be used alongside 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 only FILTER and COUNTIF would also include only those names that appear at least twice, but they’d be repeated in the result.
So, the UNIQUE function removes these duplicates to ensure each name appears only once.
Use >2 in the final argument of the formula to extract values that occur more than twice, >3 for those that occur more than three times, and so on.
Problems You Might Encounter When Using UNIQUE in Excel
As with all functions in Excel, certain scenarios and environments stop them from working as you would expect. Here are some hurdles you could encounter, and what you can do to jump them:
Problem
What This Means
What to Do
You see the #NAME? error.
The version of Excel you’re using doesn’t support the UNIQUE function.
Switch to Excel for Microsoft 365, Excel for the web, the Excel mobile and tablet apps, or one-off versions of Excel released in 2021 or later, and try again.
You see the #SPILL! error.
The area where the UNIQUE function’s dynamic array of results wants to spill is blocked.
Clear the cells where the result is trying to spill, and try again. On the other hand, if you’ve typed the formula into the cell of a formatted Excel table, convert the table to a regular range, and try again.
You see the #REF! error.
The UNIQUE formula references an array in another workbook, but that workbook is closed.
Open the workbook containing the referenced array, and try again.
An alert appears to tell you that there’s a problem with the formula.
You might have mistyped part of the formula.
When you click “OK,” the first part of the formula causing the problem is highlighted in the formula bar. Double-check and amend that part of the formula, and try again. Repeat this process until the formula is accepted. Alternatively, click “Help” for more support.
The UNIQUE function isn’t the only one in Excel that can be used to extract data. For example, the CHOOSECOLS and CHOSOEROWS functions let you extract specific columns or rows, the GETPIVOTDATA function allows you to extract specific information from a PivotTable, and using the DROP function, you can remove a specified number of rows or columns from the start or end of an array.
