Using the UNIQUE Function

Written by Allen Wyatt (last updated January 29, 2022)
This tip applies to Excel Excel in Microsoft 365 and 2021


1

Danny is unclear on how the UNIQUE worksheet function is used. He questions whether it only works with numbers, or will it work with text. He is particularly interested in how many characters in text values it might compare.

The UNIQUE function was added in the version of Excel provided with Microsoft 365. It is one of the rather unique (no pun intended) database functions now available in Excel. It is designed to analyze a range of cells and return the unique values in that range. It will work with any primary data type that Excel supports—numbers, text, dates, times, etc.

This is the syntax of the UNIQUE function:

UNIQUE(Range, Direction, ExactlyOnce)

According to this syntax, the UNIQUE function can use up to 3 parameters. Only one of those parameters is mandatory, however, and that is the Range parameter. This one indicates the range of cells you want UNIQUE to evaluate. The function returns an array of values, so that it spills over into whatever cells are available beneath the cell in which the function is used.

If the Range is a single column, then the unique values in that column are returned. When evaluating text values, UNIQUE doesn't pay attention to whether the text is uppercase or lowercase; it is all treated the same. Plus, length of the text is not a factor.

If the Range you specify is multiple columns (such as A2:B19), then UNIQUE analyzes the range as if it were a single column. For instance, if A6:B6 is "Frank, 12," and A12:B12 is "Frank, 41," then UNIQUE includes rows for both in what it returns—the key isn't on the uniqueness of the first column, but on the total uniqueness of all columns, considered together.

This understanding of the way that the Range parameter is understood can come in handy. Let's say that you have a list of names, with the last name being in column A and the first name being in column B. There are duplicates in the list, but you want the unique names to be determined. You could, if desired, derive the unique values in this way:

=UNIQUE(B2:B49&" "&A2:A49)

What gets returned is the unique names, in the form of first name, space, last name. If you prefer to have them with last name first, you could just as easily have used this variation:

=UNIQUE(A2:A49&", "&B2:B49)

You could even take it a step further and sort the names by wrapping the whole thing in the SORT function:

=SORT(UNIQUE(A2:A49&", "&B2:B49))

Back to the UNIQUE function, however. The second parameter (Direction) can be either True or False. The default for this parameter is False, which means that you are evaluating values downward, in a single column (or in an aggregated column, as described earlier). If you want to evaluate values across a row, you would set this parameter to True, as shown here:

=UNIQUE(A2:Q2,True)

The third parameter (ExactlyOnce) is the really interesting parameter. Like the Direction parameter, ExactlyOnce can be either True or False. The ExactlyOnce parameter also defaults to False, which means that all unique values are returned from the Range. If you set this parameter to True, however, then UNIQUE returns values from the Range that occur only once in that range.

As an example, let's say you had a bunch of transactions in the range A1:K1459. The first row is for column headers, and column A contains an account number for each transaction. These transactions are for the last six months, and your boss wants you to figure out which accounts made only a single transaction during that time so that you can offer them a special incentive to purchase again. You could use the following formula to determine the desired accounts:

=UNIQUE(A2:A1459,,True)

You end up with just those account numbers you need—the ones that purchased only once during the last six months.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12835) applies to Microsoft Excel Excel in Microsoft 365 and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Freezing a Table

Tired of Word changing the dimensions of table cells to accommodate what you place in those cells? You can instruct Word ...

Discover More

Using the WEEKNUM Function

Need to know which week of the year a particular date falls within? Excel provides the WEEKNUM function so you can easily ...

Discover More

Ctrl+V Pasting is Flakey

When you copy and paste a formula, you usually want to see the formula where you pasted. If you don't get the formula, ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Ways to Concatenate Values

Users of the most recent versions of Excel have four different ways available to combine values into strings. Even those ...

Discover More

Phantom Counts

Two common worksheet functions used to count things are COUNT and COUNTA. Not understanding how these functions treat ...

Discover More

Counting the Number of Blank Cells

If you need to count the number of blank cells in a range, the function to use is COUNTBLANK. This tip discusses the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 - 3?

2022-01-30 16:34:57

MichelExcel

Another good asset for this formula is the addition of the filter function to remove (or include) unwanted values. In my file, I have a column where we have the DIN number (unique ID in Canada for Drug Identification Number). The first row contains the header name "DIN" which I don't want to sort but If you don't include the first row in the formula you get an issue when you add a line at row 2 (and get a big mess in an inventory). Some product don't have a DIN (If they are a raw material item or R&D product). I also have empty lines in back-up which contain no value at all. The filter function remove the header row, the empty values and the non-DIN product (flagged "N/A"). DIN is a 8 digit number with leading zero(s). (e.g. 00258964)

=SORT(UNIQUE(FILTER($O$1:$O$1648,($O$1:$O$1648<>"N/A") * ($O$1:$O$1648<>"")*(RIGHT($O$1:$O$1648,3)<>"DIN"))))

this formula made me save hours of sorting, cutting, and pasting (and sometimes redoing cause of mistakes)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.