Excel UNIQUE Function

Excel UNIQUE function

List Unique Entries

You can use the Excel UNIQUE function to extract a list of unique or distinct items from a range of Excel cell values. It’s really easy.

Excel UNIQUE function

Just type “=u” into a cell and you’ll see a list of Excel functions pop up. Double-click UNIQUE in the list to start your formula. Enter the cell range that contains your values. Your formula should look something like this:

=UNIQUE(B4:B16)

Press ENTER to see the results.

Make sure that you leave enough empty space under the formula to show the results. Otherwise you might get an error.

As the extraction is done with a formula that means it’s dynamic. You only have to do it once. If you ever change the entries in the original list then the extraction of your unique values automatically updates to reflect the changes.

Excel UNIQUE function

Sorting the Results

Excel UNIQUE function

Oh, you wanted the results in alphabetical order did you?

The UNIQUE function can’t do that, but the SORT function can. All you have to do is wrap one function inside the other.

Double-click your formula so that you can do the required edit. Type SORT and an open bracket at the start then a closed bracket at the end.

Your formula should now look something like this:

=SORT(UNIQUE(B4:B16))

Press ENTER and your formula now returns the entries in alphabetical order. A-Z order is the default for the SORT function.

Excel UNIQUE function

Unique or Distinct?

Some people get very angry when you say things like “almost unique” or “very unique” because unique, properly, means “one of a kind”. It’s an absolute idea. The Excel UNIQUE function returns a list of everything in the list but excluding any repetitions. It should really be called DISTINCT, but they’ve gone for a more popular definition of unique.

Excel UNIQUE function

You can change this if you want to.

The function has a few optional arguments. You need the third one:

TRUE- Return items that appear exactly once

FALSE- Return every distinct item

Change your formula so that it reads as follows:

=UNIQUE(B4:B16,,TRUE)

Two commas means you’re giving the third argument. TRUE to return items that appear exactly once.

And here’s the result. There’s only one item. Corunna only appears once in the list. It’s one of a kind.

In fact, it’s unique!

Excel UNIQUE function
Excel UNIQUE function

The UNIQUE function is just one of the new type of Excel functions, Dynamic Array Functions that were released with Excel 365. To learn more about these revolutionary functions, come along on our dedicated Excel Dynamic Array Functions Course.

Call 020 7920 9500 now for details.

Related Posts

Excel UNIQUE function

Usually the formulas you need for percentages and differences are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore.

Read More »
Excel UNIQUE function

I think the Excel FILTER function does the filter job better than AutoFilter. It’s a live formula and an extraction, you don’t have to filter your data in place. There’s no need for that clunky Advanced Filter…

Read More »
Excel UNIQUE function

You can use Excel’s ROMAN function in a formula whenever you need to express normal, Arabic numbers as Roman numerals. And you can use the ARABIC function to convert them back the other way…

Read More »
Excel UNIQUE function

Generating random text or Lorem Ipsum for Word and PowerPoint. Do you ever need filler or placeholder text for Word documents or PowerPoint text boxes? It’s quite handy when you want to plan the design and layout of your document…

Read More »
Excel UNIQUE function

Do you ever have to deal with those cluttered PowerPoint slides? You know; the ones crowded with layers of overlapping shapes and text boxes—and you need to change the one in the middle of the pile…

Read More »
Excel UNIQUE function

I must say that I quite like Keynote, and prefer it to PowerPoint. But if there’s one thing that gets my goat, it’s those pesky text boxes! You can’t seem to resize them properly. What you need is an 8-handled text box…

Read More »