The `UNIQUE` function in Excel is used to extract unique values from a range or array. It’s particularly useful for identifying distinct entries in a dataset. The function was introduced in Excel 365 and Excel 2019, so make sure you’re using a compatible version. Here’s how to use it:
Basic Syntax
The syntax for the `UNIQUE` function is:
UNIQUE(array, [by_col], [exactly_once])
- array: The range or array from which you want to extract unique values.
- by_col (optional): A logical value (TRUE or FALSE). If TRUE, the function will perform a comparison by column; if FALSE or omitted, it will perform a comparison by row.
- exactly_once (optional): A logical value (TRUE or FALSE). If TRUE, the function will return only values that occur exactly once; if FALSE or omitted, it returns all distinct values.
Steps to Use UNIQUE Function
- Select a Cell: Click on the cell where you want the unique list to start appearing.
- Enter the Function: Type `=UNIQUE(`.
- Specify the Range: Enter the range or array from which you want to extract unique values. For example: `=UNIQUE(A1:A10)`.
- Optional Parameters:
- To consider unique values by columns, add `TRUE` after the range: `=UNIQUE(A1:C10, TRUE)`.
- To filter values appearing exactly once, add `TRUE` for the third parameter: `=UNIQUE(A1:A10, FALSE, TRUE)`.
- Close the Parenthesis and Press Enter: Finalize your formula by closing the parenthesis and pressing Enter. The unique values will be displayed where you placed the function.
Examples
=UNIQUE(A1:A10)
- Unique Values from a Column:
This returns all distinct values from the range A1 to A10.
=UNIQUE(A1:C10, TRUE)
- Unique Values by Row (considering each row as an entity):
This returns unique rows from the range A1 to C10 when considered row-wise.
=UNIQUE(A1:A10, FALSE, TRUE)
- Only Values that Occur Once:
This returns values from A1 to A10 that appear exactly once in the list.
Tips
- Dynamic Arrays: The `UNIQUE` function benefits from Excel’s dynamic array capabilities, automatically spilling results into adjacent cells. Ensure you have empty space below the cell where you first enter the function, or else you may overwrite existing data.
- Updating: Since the function is dynamic, any changes to the source array will automatically update the output list.
- Filter with Other Functions: Combine with functions like `SORT` to organize unique values, e.g., `=SORT(UNIQUE(A1:A10))`.
Using the `UNIQUE` function simplifies tasks that require filtering unique entries, especially as data sizes increase.
Unlock Your Potential
Excel
Access
Power BI
