Microsoft is always adding useful features to Excel. The UNIQUE function is available in Excel 2019, Excel 2021, and Excel for Microsoft 365.
So what does the UNIQUE function do?
The UNIQUE function lets you extract a list of distinct values from a list or range. You can specify whether values are arranged in rows or columns, and you can indicate whether to display distinct values or entries that exist only once.
The UNIQUE function’s output is dynamic. If you add, remove, or otherwise modify values in the source range, the UNIQUE function’s output will update to reflect those changes.
If you’re like me, you can probably think of several ways you’ll use the UNIQUE function in Excel!
Syntax for the UNIQUE Function in Excel
The UNIQUE function allows you to specify the following options.
=UNIQUE(array,[by col],[exactly_once])
Array is a required argument. This is where you specify the range or array containing the values you want to parse for unique entries.
By col is an optional parameter that indicates how you’d like to compare values.
- True will compare columns.
- False (or omitted) will compare rows.
Exactly_once is an optional parameter that specifies whether to restrict output to values that appear exactly once.
- True returns entries from all rows or columns that appear exactly once.
- False (or omitted) returns all distinct rows or columns from the range or array.
3 Ways to Extract Distinct Values with the Unique Function in Excel
A couple of examples will help you understand how to use the UNIQUE function to extract distinct entries from a range.
Example #1: Extract Distinct Values from a Column
In the first example (below), I typed =UNIQUE(C2:C12).
The only argument I provided was the array: C2:C12. The UNIQUE function compares values in the selected column and returns all distinct entries by default.
Some city names appear in column C more than once but the UNIQUE function’s output only lists distinct entries – there are no duplicates.
Example #2: Extract Values From a List That Appear Exactly Once
In this example, I entered =UNIQUE(C2:C12,FALSE,TRUE).
The third argument in the function is set to true which causes the UNIQUE function to restrict its output to items that appear exactly once.
Toronto, Miami, and St Louis each appear exactly once in the list of cities in column C.
Example #3: Extract Distinct Values From a Row
Most of the time when you use Excel, data will be arranged in columns. Occasionally, your array may be in rows.
In this example, I entered =UNIQUE(C15:M15,TRUE,TRUE).
The first argument identifies the range where my data resides. The second argument indicates that columns should be used to determine which values are distinct, The third argument specifies that output should be restricted to city names that appear exactly once.
Toronto, Miami, and St Louis each appear exactly once in the list of cities in row 15.
Is the UNIQUE Function Available in Excel 2016?
If you’re still an Excel 2016 user, you won’t be able to use the UNIQUE function. The UNIQUE function is only available in Excel 2019, Excel 2021, and Excel for Microsoft 365.
Parting Thoughts
I hope you’ve enjoyed reading this article on the UNIQUE function.
If you want to learn about other functions in Microsoft Excel, you might want to check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. Both of these functions are incredibly useful for anyone needing to combine data from multiple sources.
See you next time!
