Microsoft Office Training London 020 7920 9500
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.
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.
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.
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.
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!
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.
The SUMPRODUCT function is one of those hero functions that, once discovered, you wonder how you ever managed without. It does not just do “what it says on the tin”, there’s a lot more to it than that. In particular, it’s one of the most powerful and flexible filter functions in Excel. And so much better than SUMIF or SUMIFS.
It’s easy to calculate someone’s age from their date of birth if you know about Excel’s DATEDIF function, unfortunately it’s easy to miss this function as it is not documented. Excel will not help you fill in the DATEDIF function interval values, you need to see the list here.
Our Excel double click tricks are some of those little things that make your life so much easier. You probably know most of them already. Or do you? I think that anyone who uses Excel regularly should know them.
Complicated text formulas using either ampersands and the CONCATENATE function are the bane of our life. Not any more! Excel new text functions will really help us nail those text formulas. We’ll be looking at the CONCAT function and the TEXTJOIN 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.
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…