Tip:
Highlight text to annotate it
X
The advanced filter tool allows you to run some sophisticated filters through a data
base something like this. In order to make it work what you need to do is have a separate
section that specifies what you are looking for. The easiest way to do this is to take
the headers of your data base, copy and paste them to a new section and then put your criteria
in –so for example, here you’ll see I want Sales people 002, I want them to be bigger
than 500 for product A, bigger than 500 for product B, for C and I want Salesperson 3,
less than 500 for A, less than 500 for B but don’t worry about C. Now we can click under
the Data tab, Sort and Filter the advanced button. What you’ll see Excel does – it
tries to guess where the information is but says tell me where the list of information
is. So we say highlight from…… to….. and then Excel will ask what the criteria is and
in this case it seems to have guessed correctly so that’s fine. It could ask for unique
records but for now just ignore that and I click ok. You’ll see what its done – its
filtered the information only for items that meet the criteria. If you remember all of
these need to be more than 500 - we had one situation where salesperson 3 was less than
500 for those 2 but this one didn’t matter. To get back to the full list, there’s a
button called clear, click it, you’ll see everything comes back. Let’s do the same
thing now but only look at the Unique Records. Look at this data base, you’ll see the items
in yellow are exact duplicates of each other. You’ll see the numbers are exactly the same,
the date, the sales person, etc. I am just going to remove this criterion. Click Advanced.
It has asked for the list, give it again, the criteria is fine. Now I am going to say
I only want to see the unique records. Now when I say ok you’ll see what its done is
shown only the unique records. Again to get back to the full list. Click clear. Just to
point out, when we click Advanced, you’ll see that there are 2 options here- you can
either filter the list in place which does exactly what we saw. Only the relevant cells
are highlighted. We could have clicked copy to another location- in which case we could
have said take the information and put it into another spreadsheet or another group
of cells.