Return a list of unique values in an array or range
=UNIQUE(array,[by_col],[exactly_once])
Only available in Excel for Microsoft 365 and Excel 2021.
UNIQUE defines 'the array to select unique values from', 'whether the data is organized by column' and 'whether to only include values that appear once'.
For example, =UNIQUE(D3:D17,,TRUE) will look for unique values that only appear once in the vertical range D3:D17.
=UNIQUE(D3:N3,TRUE,TRUE) will look for unique values that only appear once in the horizontal range D3:N3.
UNIQUE examples.
In the examples below, the UNIQUE function is used to return unique lists of years, regions or country/region arrays from a table of population data.
Use the UNIQUE function to return a list of unique values in an array or range.
Syntax
=UNIQUE(array,[by_col],[exactly_once])
The UNIQUE function consists of at least 1 argument:
- array. Required. The range or array containing the rows or columns from which to return the unique values.
- by_col. Optional. A logical value to determine whether the values should be compared by row (FALSE or 0, default) or by column (TRUE or 1).
- exactly_once. Optional. A logical value to determine whether to consider all values (FALSE or 0, default) or only values that appear once in the array (TRUE or 1).
Try it now!
- Enter your formulas into the grey cells.
Scope
UNIQUE defines 'the array to select unique values from', 'whether the data is organized by column' and 'whether to only include values that appear once'.
|
=UNIQUE({1;1;2;2;3;3}) || result is all unique values in the vertical array (by row), returning {1;2;3}. |
|
=UNIQUE({1,1,2,2,3,3},TRUE) || result is all unique values in the horizontal array (by column), returning {1,2,3}. If the by_col argument had been omitted or set to FALSE, the function would have returned {1,1,2,2,3,3}. |
|
=UNIQUE({1;1;2;2;3;3;4},,1) || result is any unique value that only appears once in the vertical array (by row), returning 4. If the exactly_once argument had been omitted or set to FALSE or 0, the function would have returned {1;2;3;4}. |
|
=SORT(UNIQUE(C3:C17)) || result is a sorted list of unique values from the range C3:C17. |
|
=UNIQUE(C3:C17&" "&D3:D17) || result is a concatenated list of unique values (separated by a space) from the ranges C3:C17 and D3:D17. |
Caveats
- UNIQUE can return an array (multiple rows or columns of values). These arrays can spill into neighbouring cells.
- array
- Can include text, numbers, dates or times.
- by_col
- FALSE or 0 (default). Values compared by row.
- TRUE or 1. Values compared by column.
- exactly_once
- FALSE or 0 (default). All unique values returned.
- TRUE or 1. Only unique values that appear once returned.
Common issues
- #CALC!: A unique list can not be identified. For example, =UNIQUE({1,1,2,2,3,3},TRUE,TRUE) returns #CALC! as there are no values that only appear once.
- #REF!: Linked dynamic arrays are in a different closed workbook. Both workbooks need to be open for linked dynamic array to refresh.
Functions Category