In large datasets, there is a possibility of having duplicate values or the same values occurring more than once. To get unique values from a range or a list you can use the Excel UNIQUE function. The Excel UNIQUE function returns a list of unique values in a range or in a list. The UNIQUE function supports text, numbers, dates, times, etc. types of values.
In this article, I will show you various examples of using the Excel UNIQUE function.
Basics of EXP Function: Summary & Syntax
Summary
The Excel UNIQUE function returns a list of unique values in a range or in a list. It’s a very easy-going function, you can extract both unique and unique distinct values and it also helps to compare columns to columns or rows to rows.
Syntax
UNIQUE(array, [by_col], [exactly_once])
Arguments
| Arguments | Required/Optional | Explanation |
|---|---|---|
| array | Required | It is a cell range or array from which to extract unique values |
| by_col | Optional | It is a Boolean value for how to compare and extract unique values. Here, FALSE means by row; TRUE means by column.default (FALSE) |
| exactly_once | Optional | It is also a Boolean value. Here, TRUE means values that occurred once; FALSE means all unique values. default (FALSE) |
Return Value
The UNIQUE function returns a list or array of unique values.
Version
The UNIQUE function is available for Excel 365 and Excel 2021.
How to Use Excel UNIQUE Function: 20 Examples
1. Using UNIQUE Function for Text Values
You can use the UNIQUE function to extract unique values from text or string values.
Here, I want to get the unique fruit name from the Product Name column.
⏩ In cell D4, type the following formula to get the unique values.
=UNIQUE(B4:B12)
Here, in the UNIQUE function, I selected the cell range B4:B12 as an array.
Now, press ENTER, and the UNIQUE function will return the list of unique values from the selected range.
2. Using UNIQUE Function for Numeric Values
In case you have numeric values you also can use the UNIQUE function to extract unique values.
Here, I want to get the unique order id’s from the Order ID column.
⏩ In cell D4, type the following formula to get the unique values.
=UNIQUE(C4:C12)
Here, in the UNIQUE function, I selected the cell range C4:C12 as an array.
Now, press ENTER, and the UNIQUE function will return the list of unique values from the selected range.
3. Using the Excel UNIQUE Function to Find Unique Rows Occurred Only Once
If you want to get the unique values that only occurred once in the list or in a range, you can use the UNIQUE function.
Let me start the procedure,
⏩ In cell D4, type the following formula to get the unique values.
=UNIQUE(B4:C12,,TRUE)
Here, in the UNIQUE function, I selected the cell range B4:C12 as an array, kept the by_col argument FALSE, or omitted it because the dataset I am using is organized in rows. Then select TRUE as exactly_once.
Now, press ENTER, and the UNIQUE function will return the list of unique values that occurred only once from the selected range.
4. Unique Values in A Row
If you want to extract unique values from a row, then you can use the UNIQUE function.
To start the procedure,
⏩In cell C6, type the following formula to get the unique values.
=UNIQUE(C3:K3, TRUE)
Here, in the UNIQUE function, I selected the cell range C3:K3 as an array and selected TRUE as by_col.
Now, press ENTER, and the UNIQUE function will return the unique values from the row.
5. Using Excel UNIQUE Function to Find Unique Columns
You also can get the unique columns by using the UNIQUE function.
To start the procedure,
⏩ In cell C7, type the following formula to get the unique columns.
=UNIQUE(C3:K4, TRUE,TRUE)
Here, in the UNIQUE function, I selected the cell range C3:K4 as an array, selected TRUE as by_col, and then selected TRUE as exactly_once.
Now, press ENTER, and the UNIQUE function will return the unique columns.
6. Unique Values Occurred Only Once
In case you want to extract unique values from a list then you also can use the UNIQUE function.
⏩ In cell D4, type the following formula to get the unique values from a list.
=UNIQUE(B4:B12,,TRUE)
Here, in the UNIQUE function, I selected the cell range B4:B12 as an array, kept the by_col argument FALSE, or omitted it because the dataset I am using is organized in rows. Then select TRUE as exactly_once.
Now, press ENTER, and the UNIQUE function will return the list of unique values that occurred only once from the selected range.
7. Find Distinct Values That Occur More Than Once
By using the UNIQUE function along with the FILTER function and the COUNTIF function, you can get distinct unique values that mean the values that occurred more than once.
Let me show the process,
⏩ In cell D4, type the following formula to get the unique values from a list.
=UNIQUE(FILTER(B4:B12, COUNTIF(B4:B12, B4:B12)>1))
Here, in the UNIQUE function, I used FILTER(B4:B12, COUNTIF(B4:B12, B4:B12)>1) as an array.
In the FILTER function, I selected the range B4:B12 as an array and used COUNTIF(B4:B12, B4:B12)>1 as include.
In the COUNTIF function, I selected the range B4:B12 as the range and also as criteria selected B4:B12 then used >1.
Now, the COUNTIF function will get the count from the values occurring more than once to FILTER values. Finally, the UNIQUE function will return the unique values occurring more than once.
Press ENTER and the UNIQUE function will return the unique values occurring more than once.
8. Using Excel UNIQUE Function to Count Unique Values
You also can count the unique values by using the FILTER function along with the ROWS function.
⏩ In cell D4, type the following formula to get the unique values from a list.
=ROWS(UNIQUE(FILTER(B4:B12,B4:B12<>"")))
Here, in the ROWS function, I used UNIQUE(FILTER(B4:B12,B4:B12<>””)) as array.
In the UNIQUE function, I used FILTER(B4:B12,B4:B12<>””) as array.
In the FILTER function, I selected the range B4:B12 as array also as include selected B4:B12<>”” to filter values, not equal blank.
Now, the UNIQUE function will return the unique values from the filtered values then the ROW function will return the unique values row count.
Press ENTER, and you will get the count of unique values.
9. Unique Values From Multiple Columns
If you want, you can extract unique values from multiple columns as well, just by using the UNIQUE function.
⏩ In cell F4, type the following formula to get the unique values from multiple columns.
=UNIQUE(B4:D12)
Here, in the UNIQUE function, I selected the cell range B4:D12 as an array.
Now, press ENTER, and the UNIQUE function will return the range of unique values from multiple columns.
10. Unique Values Sorting in Alphabetical Order
You also can use the SORT function along with the UNIQUE function to sort unique values alphabetically.
⏩ In cell F4, type the following formula to get the unique values from multiple columns.
=SORT(UNIQUE(B4:D12))
Here, in the UNIQUE function, I selected the cell range B4:D12 as an array. Then pass the unique values to the SORT function to sort the unique values alphabetically.
Now, press ENTER, and you will get the sorted unique values from multiple columns.
11. Unique Values from Multiple Columns and Concatenate into One Cell
You can extract unique values from multiple columns also you can concatenate those values into one cell while using the UNIQUE function.
⏩ In cell F4, type the following formula to get the unique values from multiple columns.
=UNIQUE(B4:B12& ","&C4:C12)
Here, in the UNIQUE function, I selected both cell ranges B4:B12& “,”&C4:C12 as an array. Now the UNIQUE function will extract the unique values from both column ranges then it will concatenate the unique values of both columns with (,)
Now, press ENTER, and you will get the concatenated values into one cell.
12. List of Unique Values Depending on Criteria
You can get the list of unique values based on criteria while using the UNIQUE function along with the FILTER function.
Here, I want to get unique values based on the criteria where the Price is greater than 400.
⏩ In cell G4, type the following formula to get the unique values based on criteria.
=UNIQUE(FILTER(B4:B12,D4:D12>F4))
Here, In the UNIQUE function, I used FILTER(B4:B12,D4:D12>F4) as an array.
In the FILTER function, I selected the range B4:B12 as an array and also included selected D4:D12>F4 to filter values, greater than the selected cell F4.
Now, the UNIQUE function will return the unique values from the filtered values.
Finally, press ENTER, and you will get the unique values based on your given criteria.
13. Filter Unique Values Based on Multiple Criteria
You also can extract the list of unique values based on multiple criteria while using the UNIQUE function with the FILTER function.
Here, I want to get unique values based on the criteria where the Price is greater than 400 and the Product name is Apple.
⏩ In cell H4, type the following formula to get the unique values based on multiple criteria.
=UNIQUE(FILTER(C4:C12, (D4:D12>F4)* (B4:B12=G4)))
Here, In the UNIQUE function, I used FILTER(C4:C12, (D4:D12>F4)* (B4:B12=G4)) as an array.
In the FILTER function, I selected the range C4:C12 as an array and used (D4:D12>F4)* (B4:B12=G4) as include where I used two criteria one is for Price and another is for the Product name.
Now, the UNIQUE function will return the unique values from the filtered values.
Finally, press ENTER, and you will get the unique values based on your used multiple criteria.
14. Filter Unique Values Based on Multiple OR Criteria
You also can use the UNIQUE and FILTER function to apply multiple OR criteria.
⏩ In cell H4, type the following formula to get the unique values from multiple OR criteria.
=UNIQUE(FILTER(B5:B13, (C5:C13=F5) + (D5:D13=G5)))
Here, In the UNIQUE function, I used FILTER(B5:B13, (C5:C13=F5) + (D5:D13=G5)) as array.
In the FILTER function, I selected the range B5:B13 as an array and used (C5:C13=F5) + (D5:D13=G5) as include where I used two criteria. Then added two criteria to apply OR to check any of the criteria.
Now, the UNIQUE function will return the unique values from the filtered values where OR is applied.
Finally, press ENTER, and you will get the unique values if any of the conditions are fulfilled.
15. Get Unique Values Ignoring Blanks
While using the UNIQUE function with the FILTER function you can extract unique values while ignoring blank cells.
⏩ In cell F4, type the following formula to get the unique values ignoring blanks.
=UNIQUE(FILTER(B4:B12,B4:B12<>""))
Here, In the UNIQUE function, I used FILTER(B4:B12,B4:B12<>””) as an array.
In the FILTER function, I selected the range B4:B12 as an array and used B4:B12<>”” as include to filter non-blank cells.
Now, the UNIQUE function will return the unique values from the filtered values.
Finally, press ENTER, and you will get the unique values while ignoring blank cells.
16. Using Excel UNIQUE & SORT Function to Ignore Blanks & Sort
You also can sort the unique values while ignoring blanks by using the UNIQUE function with the FILTER function.
⏩ In cell F4, type the following formula to get the sorted unique values ignoring blanks.
=SORT(UNIQUE(FILTER(C4:C12,C4:C12<>"")))
Here, In the SORT function, I used UNIQUE(FILTER(C4:C12,C4:C12<>””)) as array.
In the UNIQUE function, I used FILTER(C4:C12,C4:C12<>””) as array.
In the FILTER function, I selected the range C4:C12 as array and used C4:C12<>”” as include to filter non-blank cells.
Now, the UNIQUE function will return the unique values from the filtered values. Then the SORT function will sort the filtered unique values numerically.
Finally, press ENTER, and you will get the unique values while ignoring blank cells.
17. Using Excel UNIQUE & FILTER Function to Get Unique Rows Ignoring Blank
You also can get unique rows while ignoring blanks by using the UNIQUE function with the FILTER function.
⏩ In cell D4, type the following formula to get the unique rows ignoring blanks.
=UNIQUE(FILTER(B4:C12, (C4:C12<>"")*(B4:B12<>"")),FALSE, TRUE)
Here, In the UNIQUE function, I used FILTER(B4:C12, (C4:C12<>””)*(B4:B12<>””)),FALSE, TRUE as array , selected FALSE as by_col and TRUE as exactly_once.
In the FILTER function, I selected the range B4:C12 as array and used (C4:C12<>””)*(B4:B12<>””) as include to filter non-blank cells of both columns.
Now, the UNIQUE function will return the unique rows from the filtered values while ignoring blank cells.
Finally, press ENTER, and you will get the unique rows while ignoring blank cells.
18. Filter Unique Rows Ignoring Blank & Sort
While ignoring blank to get unique rows you also can sort them using the SORT function with the UNIQUE function and the FILTER function.
⏩ In cell D4, type the following formula to get the sorted unique rows ignoring blanks.
=SORT(UNIQUE(FILTER(B4:C12, (C4:C12<>"")*(B4:B12<>"")),FALSE, TRUE))
Here, In the SORT function, I used UNIQUE(FILTER(B4:C12, (C4:C12<>””)*(B4:B12<>””)),FALSE, TRUE) as array.
In the UNIQUE function, I used FILTER(B4:C12, (C4:C12<>””)*(B4:B12<>””)) as array, selected FALSE as by_col and TRUE as exactly_once.
In the FILTER function, I selected the range B4:C12 as array and used (C4:C12<>””)*(B4:B12<>””) as include to filter non-blank cells from both columns.
Now, the UNIQUE function will return the unique rows from the filtered values. Then the SORT function will sort the filtered unique values alphabetically.
Finally, press ENTER, and you will get the sorted unique rows while ignoring blank cells.
19. Using Excel UNIQUE & CHOOSE Function to Find Unique Values in Specific Columns
You can find unique values from specific columns by using the CHOOSE function along with the UNIQUE function.
⏩ In cell D4, type the following formula to get the unique values from specific columns.
=UNIQUE(CHOOSE({1,2}, C4:C12, B4:B12))
Here, in the UNIQUE function, I used CHOOSE({1,2}, C4:C12, B4:B12) as an array.
In the CHOOSE function, I used {1,2} as index_num, selected the range C4:C12 as value1, then selected the range B4:B12 as value2.
Now, the UNIQUE function will return the unique values from the selected range of the specific column.
Finally, press ENTER, and you will get the unique values from the selected range of the specific column.
20. Error Handling with IFERROR
The UNIQUE function shows the #CALC error if the value you are looking for is not available.
To handle this error, you can use the IFERROR function along with the UNIQUE and FILTER functions.
⏩ In cell H4, type the following formula to handle the error.
=IFERROR(UNIQUE(FILTER(C4:C12, (D4:D12=F4)* (B4:B12=G4))), "Value Not Found")
Here, in the IFERROR function, I used UNIQUE(FILTER(C4:C12, (D4:D12=F4)* (B4:B12=G4))) as value and provided the text Value Not Found as value_if_error.
In the UNIQUE function, I used FILTER(C4:C12, (D4:D12=F4)* (B4:B12=G4)) as array.
In the FILTER function, I selected the range C4:C12 as array and used (D4:D12=F4)* (B4:B12=G4)) as include to filter values from the selected range D4:D12 if it is equal to F4, also selected range B4:B12 if it is equal G4.
Now, the UNIQUE function will return the unique values from the filtered values. Then, the IFERROR function will check if the value is available or not if not available then it will return the text Value Not Found instead of #CALC error.
Finally, press ENTER, and you will get the unique values of the given text.
Things to Remember
🔺 The UNIQUE function will show the #NAME error if you misspell the function name.
🔺 The UNIQUE function will show the #CALC error if the value is not found.
You will get the #SPILL error in the UNIQUE function if one or more cells in the spill range are not completely blank.
Practice Section
I’ve provided a practice sheet in the workbook to practice these explained examples.
Download to Practice
Conclusion
In this article, I have shown 20 examples of the Excel UNIQUE function. I also tried to cover the when and why the UNIQUE function may show errors frequently. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
Hi. Thank you for writing this article. I am having difficulty getting UNIQUE to do what I need, and none of your many examples seems to cover my case.
Even when looking for all unique values within an array, the example data set in this article doesn’t really provide a complete test, because each column is already a different type of information (fruits, numbers, prices). My data has multiple columns of the same type of information, and even when searching for unique values in an array, UNIQUE only evaluates by column or row, listing the unique values within a column or row. So for example, the value “B17” might be present in several columns, and the array returned by UNIQUE will have at least one instance of “B17”, implying that B17 is unique when it is not.
Is there a way to check all cells of an array for uniqueness WITHIN THE WHOLE ARRAY?
Many thanks
Hello Jen,
Thanks for your question. I think there is no direct way to fulfill your requirement using the UNIQUE function. But you can use a combination of some functions to get the unique values after comparing different columns.
So, I have created the following dataset where I have some names of fruits in the two columns, List 1 and List 2. Here, we have some same fruits names in these two columns, and using the formula we will extract the unique values of these columns in the columns; Result 1, and Result 2.
For extracting the unique values of List 1, we will use the following formula in Result 1.
=IF(ISNA(VLOOKUP(B3,$C$3:$C$9,1,FALSE)),B3,””)
After comparing the unique values of List 2 with the values of List 1 we will use the following formula in the Result 2 column.
=IF(ISNA(VLOOKUP(C3,$B$3:$B$9,1,FALSE)),C3,””)
U have to also show unique values from multiple sheets
Hello, SR! Thanks for your recommendation. We’ll cover this part & give you an update soon.
My query mirrors Jen’s: 20 examples and not one covers database/multiple list style uniqueness queries. Unfortunately, this is common to most sites, which assume people only have interest in business data (Row: a thing, value of the thing, another value of the thing). Sadly, Excel is programmed in the same way. And it extends to help with most functions.
I work with words for most projects to create teaching aids, or qualitative study, and Word doesn’t have formula: my columns are word list A, word list B, C. Row 5 (for example) of my wordlists are unrelated. Reasonably, I want to query unique item. Simply, are any of my words in any list duplicated elsewhere. I wish this was simple. Same with FILTER… I must create this elaborate LET function that knits all the lists together into one column.
(Example)
Array:
deafening | raucous | rowdy
disorderly | disorderly | deafening
earsplitting | ear-split | plangent
plangent | plangent | sonorous
deafening | raucous | thunderous
reverberating | reverberating | unruly
rowdy | row | vociferous
deafening | screechy | raucous
==|> Array:
deafening | raucous |
disorderly | |
earsplitting | ear-split |
plangent | | sonorous
| | thunderous
reverberating | | unruly
rowdy | row | vociferous
| screechy |
( | = column divide )
All I want, in Jen’s words, is to check unique within a whole array, with simpler formula. And returning words rather than TRUE/FALSE, numbers or errors (when it’s unique), or transformation to a single column.
It’ll be great to see help offered, understanding how to build formula, for any function for people who work with non-related lists (columns) and qualitative data.
Hi Andy,
Thanks for your query. Unfortunately, using the UNIQUE function you cannot do your desired job directly. So, I have come up with an easy alternative way.
Here, I have created the following dataset using your example. Using the PivotTable feature of Excel, I will convert the following three columns into a single column with unique values only.
• Press ALT+D and then P immediately to open up the PivotTable and PivotChart Wizard.
• In Step 1 of this wizard click on the options; Multiple consolidation ranges, PivotTable.
• Click on Next.
• In Step 2a of this wizard click on the Create a single page field for me option.
• Click on Next.
• Now, select the range of the words including a blank column prior to this range in the Range box.
• Select Add to enter the formula of the Range box to the All ranges box.
Afterward, the formula will be entered into the All ranges box, and finally, click on Next.
• In Step 3 of this wizard click on the New worksheet option.
• Click on Finish.
• Now, drag down the Value to the Rows area.
Finally, all of the unique words will be listed in a single column.