Using the UNIQUE function in Excel
is probably one of the easiest ways to
find unique values in your data.
So, it's currently only available to
Office 365 users, but
should also be available in newer versions of
Excel in the future. The UNIQUE
function in Excel works by
returning a list of unique values
from within the data you select. So this
can be letters, numbers, and most other
characters. What makes this function
so powerful is that it was created in a
way to give you options as to
how you can get those unique
values. Probably the most
common way to use the UNIQUE function
is to have it return a list where each
item is listed once. So, in this
scenario, all items are
listed down the same column, some
of the items are repeated, and you want
to generate a list where each item is
listed only once. For this
example, you could simply type
equals UNIQUE, open
parentheses, select the
items that you want to see the unique
values for, making sure to
exclude any headers, and press Enter.
The way that the UNIQUE function looks at
columns and rows always makes
me have to stop and think for a moment. So,
the default is to look through your data
when each item that you're looking
through is in a row. So, this
actually would be like our example here,
where the items are in column A,
but the UNIQUE function is looking at
rows two, three, four, five, and all the
way down what we selected. So,
because this is the default, in our
first example we were able to just
select our data and press Enter.
But if we want to be more specific,
we can also
type equals
UNIQUE, select our
data, and, so, for
this function - the last two arguments -
if your response is
False you can skip it. Which is why we skipped
it in the first example.
But typing False here to
return UNIQUE rows will
give us the same result.
Now, sometimes you
may need to use the UNIQUE function
along columns or in this
case where the list is in a row, but
each item that we're going to
apply the UNIQUE function to is
in a different column so column
B, C, D, E, etcetera.
So, in this case, if we wanted to
get the unique values
across these Attendee
names, we would type
equals UNIQUE,
select the names - again don't forget
to omit from your data
any headers - and, this time,
we want to type True
because we're looking across the
columns.
And now we can close our parentheses.
You can also use the UNIQUE function to
generate a list that omits
anything that is listed more than once
in your selected data. So, for
example, in this list of
First Names only Buddy, Emerson,
and Jill appear once. All the other
names are repeated. So, you can use the
UNIQUE function to return
only Buddy, Emerson, and Jill.
So, to do this, type
equals UNIQUE,
select your data,
and because our items are listed
in the default layout, we can
either type False for the second
argument or type two commas
and skip it altogether. And
then the third argument, you can
select "Any Items that
Appear Exactly Once" - which is what
we want - or the default,
which is to "Return Every Distinct Item."
But because we only want items that appear
exactly once we'll type
True,
close our parentheses, and now we
have a list that just
shows Buddy, Emerson, and
Jill. You can also use the UNIQUE
function to find unique
values that span
multiple adjacent rows or
columns. So, for example, say
you want to see unique values
for both the First Name and the Last
Name, not just the First Name. So, to
do this, you would type
UNIQUE,
and select the data below
First Name and Last Name,
and press Enter.
So, like other functions in Excel, the
UNIQUE function can be combined,
or nested, with other functions
to generate more custom results. So,
for example let's say that we want to
alphabetize the results of
the UNIQUE function. So, we could
combine the UNIQUE and
SORT functions to do this. So, in
this example, we would want to
nest the UNIQUE function
inside of the SORT
function to generate
alphabetized results.
For more tutorials like this make sure to click
on these links. Thanks!