How to Use Advanced Filters in Excel

In addition to the built-in AutoFilters in Excel, you can create Advanced Filters that allow you to extract unique records or calculate criteria based on external data.

I do not use Advanced Filters as much as I did in the past because Excel 2007 greatly improved the built-in AutoFilters. However, you will pick up at least two good tips from this lesson - including how to extract Unique Records and copy them to a new worksheet.

I invite you to visit my online shopping website - http://shop.thecompanyrocks.com - to see all of my resources and explore the many products and services that I offer.

Danny Rocks
The Company Rocks

---

Closed Caption:

hello and welcome back to tips and time
savers
i'm danny rocks in today's lesson I'm
going to introduce you to the advanced
filtering capabilities in Excel i'm
going to show you how to write and apply
criteria so that you can extract the
information that you need from an excel
data sheet
now many Excel users are already
familiar they're already using excels
auto filters so if you come across an
Excel worksheet that has this drop down
arrows next to the field labels
then you have the auto filter in place
in addition to the autofilter you can
create custom filters you can use the
advanced filter what you first want to
do a best practice is the insert several
blank rows above your data set
next what you want to do is copy over
the labels that you have for your data
set and the easiest way to do that is to
use that formula equals the cell that
contains the label for that column in
the data set this way if i change the
spelling my label for the criteria will
reflect the label for the data set
next we're going to use blank rows on
the knee that to establish the criteria
so if i want to apply a criteria which
will select in the customer filled all
of those records where the value is
alpha beta corporation or where all the
values are ABC conglomerate
I will do it this way select one cell in
your data set in Excel 2007 go to the
data tab of the ribbon in the sort and
filter group choose advanced what you
see over here is an advanced filter
dialog box
now notice that in addition to filtering
the list in place we can extract it we
can copy it to another location i'm
going to do this a little later on in
the lesson
so our list range is the data list and
the data list isn't defined as including
the labels for the data
now let's understand the criteria range
over let's cross this out here
what we want to select for the criteria
is the labels for the criteria as well
as the rows that contain the values that
were all the formulas that were using
for our criteria
so in this case from a1 through I three
we're going to filter this list in place
click OK and there you go
we have records that are either part of
the alphabet a corporation or their part
of the ABC conglomerate
now we could get the same result using
auto filters in this case using the drop
down next to the customer field will d
select all of the records and we'll
choose alpha beta and will choose the
ABC
so when we make these selections were
saying either the record belongs to ABC
or it belongs to the alpha beta so now i
have the same result
now let me show you the difference
between and or criteria and when we use
or criteria we put the values on
separate rows in this case I'm going to
use the and pot of filter in this case
what I want to do is and i'm going to
copy unpaid and place that in status on
the same row as the alpha-beta
corporation i'm putting that the status
is unpaid
so now instead of or filters i'm using
and and filter to say give me the
records were both conditions are met in
the customer filled the value is equal
to alpha beta corporation in the status
field the value is equal to unpaid
select a single cell data tab on the
ribbon advance the filter remains in
place of the this range is ok but let's
examine the criteria you see from our
last filter we went down to row 38 watch
what's going to happen if we do that if
we were to select down the road three we
would have a blank row that would tell
Excel will give me all the records so a
blank row confuses excel so we want to
make sure that we select all of the
rows that contain the labels as well as
the criteria but only those rows in
other words no blank rows or the
criteria
now when we click OK we have not all of
the records for the alpha-beta
corporation only those records that the
status is unpaid
so there we've used the and filter all
right let's remove this
now let's go back here and take another
look at the auto filters in Excel 2007
Excel 2010
I find that I don't have to use the
advanced filter as frequently as before
and that's because there are some really
cool really great date filter so before
I would have to use advanced filter if I
wanted to see only those values where it
was in the particular month the quarter
next year next month etc those are
already built in starting in Excel 2007
but there are many other reasons that I
want to use the advanced filter for
example to produce records where i'm
using a calculated field so in this case
I'm going to get rid of both of those
values that I just filtered on and let's
come over here
what I want to do is I want to create a
formula that will return a value of true
or false
and i want to give a label for this
calculated criteria that is not a label
that is already in the data set
what I want to see are the records that
were paid late in other words they were
paid so they're not blank they're not
marked as unpaid they were paid but they
were paid after the due date
so let's take a look at this formula
over here i'm using a logical formula
which will return either true or false
so the if function the logical test is
using the is blank function so is blank
g9g9 over here is the first cell in the
date paid field so I want to come in and
say if it's blank return the value true
if not return false
so over here the logical test what I
want to do is I'm going to trick excel
yes I want to go through and find those
blanks meaning that there
unpaid but i'm going to trick it and say
use the false when you find a record
that's true
just return the value false because what
i really want to see are those records
where the date paid was later or greater
than the date that they were due
all right let's show you how this works
so again single cell selected data tab
on the ribbon sort and filter advanced
we're going to filter in place
the range is correct but we want to
change the criteria the criteria will be
the label for the computed field as well
as the formula that were using to return
true or false
so filter in place apply the criteria
click OK and now you see that this read
this invoice was paid it was paid on
october third which is later than its
due date so there was a great little
formula to use if logical test is blank
the trick it into instead of returning
the value true return false because what
we really want to see are the fields or
the the records where the date that it
was paid is later than or greater than
the date that it was due
alright finally let me show you another
way that we can use the advanced filter
what I want to be able to do is extract
a list of customers
so let's return using the auto filters
so come back and put the auto filter
back on and when i click on the customer
field
here's a list of my customers well I'd
like to have that
so you can't do that with the auto
filter what we want to do is use the
advanced filter so here's how we do it
first off i have named the field over
here called customer so let's come into
the name box and over here notice that
when I named customer i included the
label that's going to be an important
element over here
so now let's come over here and choose
the advanced filter what I want to do is
I want to select unique records only and
I want to copy them to another location
the list range in this case I don't want
the entire data set remember I name to
rain so I use the f3 key board shortcut
and select customer now I don't have to
apply any criteria
that's the the list over here is going
to be from the customer name unique
records only will be my criteria when I
want to copy it to another record
well I would really like to have it come
over here onto a blank worksheet so now
I'll select the cell over here
click OK and you see that it's going to
tell me that I can only copy it to the
active sheet so i'll select the cell
over here and say copy it to that at
that location
so once again I'll come back advanced
unique records only the list range will
not be the entire data set
it's going to be a customer and I don't
need criteria but where I want to copy
it to copy to another location will be
beginning over here
click OK and there you go now what's
important when you are copying unique
records is that if you're going to make
a selection or create the name make sure
that the name includes the label
now there is a way actually to get that
customer list on a separate worksheet
and let me show you how to do it this
time I'm going to begin on a blank
worksheet and now I go up to data
advanced and I want to copy to another
location
the unique records that belong to the
customer field
so from my nameless customer no criteria
is necessary and where I want to copy it
to is on sheet2 in this case and cell a1
so the goddess that there is if you want
to have a copy to a separate sheet begin
your filtering on a blank worksheet then
retrace advance and follow through it
you your other steps as i showed you so
there you've seen how to take advantage
of the advanced filtering capabilities
in Excel and i'll look for you in the
next lesson