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.


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.


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.

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo