If you want a list of customers, email addresses, product IDs, or the like where they’re all different, Excel has a function to help you. We’ll show you how to use this feature to display unique values and text.
You can already use functions in Excel to sum the number of individual values. But here we show you how to display those values instead of using the UNIQUE function. In addition, we introduce easy ways to sort the list and combine values.
Remark: Beginning in March 2022, the UNIQUE feature will be available in Excel for Microsoft 365, Excel for the web, Excel 2021 or later, or Excel for iPhone, iPad, or Android phones or tablets.
RELATED: Counting Unique Values in Microsoft Excel
Use the UNIQUE function in Excel
You can use the UNIQUE feature for text or numbers, decide how you want to compare the range of cells, and choose to display results that appear only once.
The syntax for the function is UNIQUE(array, column, only_once) where only the first argument is required. Including the column argument to compare columns instead of rows and the only_once argument to return values that occur only once in the array.
If you choose to include the optional arguments, use the TRUE indicator in the formula for each. If no indicator is recorded, the function assumes FALSE.
As an example, let’s create a list of customers for an email explosion. Instead of using the existing list in cells A2 through A10 because some customers ordered more than once, we’ll create a new list in which each customer appears once.
=UNIQUE(A2:A10)
For another example, we add the third argument, only_onceto find those customers who have ordered only once.
=UNIQUE(A2:A10,,TRUE)
Since the second argument gets FALSE if nothing is included, we just add a comma after the first argument and then another comma before the last argument. Alternatively, you can use this formula to get the same result:
=UNIQUE(A2:A10,FALSE,TRUE)
You can use the UNIQUE function to display individual values as well as text. In this formula, we can list unique dates:
=UNIQUE(F2:F10)
RELATED: Sort by date in Microsoft Excel
Sort the list automatically
As mentioned, you can sort the list automatically and at the same time use the UNIQUE function to create it. To do this, simply add the SORT function at the beginning of the formula.
Remark: The SORT function is currently only available in the previously mentioned Excel versions.
The syntax for this function is: SORT(array, index, order, column) where only the first argument is required.
If you take the first list of unique customers we created above and sort them immediately, use this formula:
=SORT(UNIQUE(A2:A10))
As you can see, the UNIQUE formula is the requirement array argument to the SORT function.
By default, the SORT function displays items in ascending order. To sort the same list in descending order, you would use the following formula which adds the order argument.
=SORT(UNIQUE(A2:A10),,-1)
Notice that we have a double comma here again. This is because we don’t want the index argument, only the order argument. Use 1 for ascending order and -1 for descending order. If no value is used, the function defaults to 1.
Combine unique values
Another handy addition to the UNIQUE function that lets you combine values. For example, maybe your list has values in two columns instead of just one as in the screenshot below.
By adding the ampersand (&) operator and a space, we can create a list of first and last names of unique customers with this formula:
=UNIQUE(A2:A10&" "&B2:B10)
To break down the formula, the first array, A2 through A10, contains the first names, the ampersands concatenate the first names with the last names in B2 through B10 with a space in quotes.
You can also include the SORT function here to sort your list in ascending order with this formula:
=SORT(UNIQUE(A2:A10&" "&B2:B10))
Just as you may want to highlight duplicate values in Excel, you may want to find unique values. Keep in mind the UNIQUE function and these additional ways to use it the next time you need to create a list of individual values or text in Excel.
RELATED: Use conditional formatting to find duplicate data in Excel