How to Use Excel UNIQUE Function (20 Examples)

In large datasets there stays 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.

Overview of Excel UNIQUE Function

In this article, I will show you various examples of using the Excel UNIQUE function.


Download to Practice


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.


Uses of Excel UNIQUE Function

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)

Using Excel UNIQUE Function for Text Values

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.

Read More: Excel VBA to Get Unique Values from Column (4 Examples)


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 orders id’s from the Order ID column.

⏩ In cell D4, type the following formula to get the unique values.

=UNIQUE(C4:C12)

Using UNIQUE Function for Numeric Values

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.

Read More: VBA to Get Unique Values from Column into Array in Excel (3 Criteria)


3. Using 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)

Using Excel UNIQUE Function to Find Unique Rows Occurred Only Once 

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 selected 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)

Using Excel UNIQUE Function to Get Unique Values in A Row

Here, in the UNIQUE function, I selected the cell range C3:K3 as an array, 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)

Using Excel UNIQUE Function to Find Unique Columns

Here, in the UNIQUE function, I selected the cell range C3:K4 as an array, selected TRUE as by_col, 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)

Using Excel UNIQUE Function to Find Unique Values Occurred Only Once

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 selected 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 the 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))

Using Excel UNIQUE Function to Find Distinct Values That Occur More Than Once

Here, in the UNIQUE function, I used FILTER(B4:B12, COUNTIF(B4:B12, B4:B12)>1) as array.

In the FILTER function, I selected the range B4:B12 as array and used COUNTIF(B4:B12, B4:B12)>1 as include.

In the COUNTIF function, I selected the range B4:B12 as range 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<>"")))

Using Excel UNIQUE Function to Count Unique Values

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.


10. 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)

Using Excel UNIQUE Function to Find Unique Values From Multiple Columns

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))

Using Excel UNIQUE Function to Find Unique Values Sorting in Alphabetical Order

Here, in the UNIQUE function, I selected the cell range B4:D12 as an array. Then passed 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)

Using Excel UNIQUE Function to Find Unique Values from Multiple Columns and Concatenate into One Cell

Here, in the UNIQUE function, I selected both cell range B4:B12& “,”&C4:C12 as an array. Now the UNIQUE function will extract the unique values from both column range then it will concatenate the unique values of both column with (,)

Now, press ENTER, and you will get the concatenate 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))

Using Excel UNIQUE Function to Find List of Unique Values Depending on Criteria

Here, In the UNIQUE function, I used FILTER(B4:B12,D4:D12>F4) as array.

In the FILTER function, I selected the range B4:B12 as array also as include 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)))

Using Excel UNIQUE Function to Find Filter Unique Values Based on Multiple Criteria

Here, 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 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)))

Using Excel UNIQUE Function to Filter Unique Values Based on Multiple OR Criteria

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 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<>""))

Using Excel UNIQUE Function to Get Unique Values Ignoring Blanks

Here, 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 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<>"")))

Using Excel UNIQUE & SORT Function to Ignore Blanks & Sort

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)

Using Excel UNIQUE & FILTER Function to Get Unique Rows Ignoring Blank

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))

Using Excel UNIQUE Function to Filter Unique Rows Ignoring Blank & Sort

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))

Using Excel UNIQUE & CHOOSE Function to Find Unique Values in Specific Columns

Here, in the UNIQUE function, I used CHOOSE({1,2}, C4:C12, B4:B12) as 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")

Error Handling with IFERROR

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 or 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.

Excel UNIQUE FUNCTION Errors


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.


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.


Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

6 Comments
  1. 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.
      dataset
      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,””)
      formula
      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,””)
      formula

  2. U have to also show unique values from multiple sheets

  3. 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.
      dataset
      • Press ALT+D and then P immediately to open up the PivotTable and PivotChart Wizard.
      process
      • In Step 1 of this wizard click on the options; Multiple consolidation ranges, PivotTable.
      • Click on Next.
      process
      • In Step 2a of this wizard click on the Create a single page field for me option.
      • Click on Next.
      process
      • 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.
      process
      Afterward, the formula will be entered into the All ranges box, and finally, click on Next.
      process
      • In Step 3 of this wizard click on the New worksheet option.
      • Click on Finish.
      process
      • Now, drag down the Value to the Rows area.
      Finally, all of the unique words will be listed in a single column.
      process

Leave a reply

ExcelDemy
Logo