# UNIQUE

UNIQUE(array [,by_col] [,occurs_once])

Returns the array of unique values in a list, table or cell range.

 array The range or array from which to return unique values. by_col (Optional) A logical value indicating whether to compare rows or columns:True = Your data is arranged from left to right so you are comparing columnsFalse = Your data is arranged from top to bottom so you are comparing rows (default) occurs_once (Optional) Whether to return values that occurs once:True = Return only unique values that occur onceFalse = Return all unique values (default)

 REMARKS
 * Added in Excel 2021.* This function can create a Dynamic Array Formula.* If "by_col" is left blank, then False is used.* If "by_col" = 1, then True is used.* If "by_col" = 0, then False is used.* If "by_col" = False and the data is arranged in a column, then unique items will be returned.* If "by_col" = True and the data is arranged in a row, then unique items will be returned.* If "by_col" = True and the data is arranged in a column (instead of a row), then every item is returned. Examples 4 & 9.* If "occurs_once" is left blank, then False is used.* If "occurs_once" = 1, then True is used.* If "occurs_once" = 0, then False is used.* If "occurs_once" = False, then all the unique values will be returned.* If "occurs_once" = True, then only the unique values that occur once will be returned.* If "occurs_once" = True and no values occur only once, then #CALC! is returned.* If there are not enough blank cells to display the full array, then #SPILL! is returned. Example 10.* You can use the SORT function to return an array of data that has been sorted.* For the Microsoft documentation refer to support.microsoft.com

 A B C D E F G 1 =UNIQUE(B1:B4) = { Mon, Tue, Wed } Mon Tue Tue Wed 30 2 =UNIQUE(B1:B4,FALSE) = { Mon, Tue, Wed } Tue Tue 20 3 =UNIQUE(B1:B4,FALSE,FALSE) = { Mon, Tue, Wed } Tue Mon 10 4 =UNIQUE(B1:B4,FALSE,TRUE) = { Mon, Wed } Wed Mon 20 5 =UNIQUE(B1:B4,TRUE) = { Mon, Tue, Tue, Wed } 6 =UNIQUE(B1:B4,TRUE,TRUE) = { Mon, Tue, Tue, Wed } 7 =UNIQUE(B1:E1,TRUE) = { Mon, Tue } 8 =UNIQUE(G1:G4,FALSE) = { 30, 20, 10 } 9 =UNIQUE(G1:G4,TRUE) = { 30, 20, 10, 20 } 10 =UNIQUE(B1:B4) = #SPILL! 11 =UNIQUE(D1:D4,,TRUE) = #CALC!

 1 - What are the unique values in the vertical range "B1:B4".2 - What are the unique values in the vertical range "B1:B4".3 - What are the unique values in the vertical range "B1:B4".4 - What are the unique values in the vertical range "B1:B4" when the "occurs_once" argument is True.5 - What are the unique values in the vertical range "B1:B4" when the "by_col" argument is True.6 - What are the unique values in the vertical range "B1:B4" when the "by_col" argument is True and the "occurs_once" argument is True.7 - What are the unique values in the horizontal range "B1:E1".8 - What are the unique values in the vertical range "G1:G4".9 - What are the unique values in the vertical range "C1:C4".10 - What are the unique values in the vertical range "B1:B4". There are not enough blank cells to display the full array.11 - What are the unique values in the vertical range "D1:D4" when the "occurs_once" argument is True. There are no items occurring only once.