In this lecture, we're going to take a look at
Advanced Excel Filter Options. This is our sample Dataset, though in the previous video
we saw how to put a quick filter on this using the shortcut key Ctrl+Shift+L. Now, the one
disadvantage of the normal filter is that if I for example, put a filter on this and let me
also just select these two, but you don't exactly know what the filter is on until you hover your
mouse over it and then you can see that Channel has been set to Store and Product is set to Pants
Black-Pants Blue. If you want to have full control over your filter criteria, you might want to use
Advanced Filter because there you actually put your filter criteria in separate cells, so you know
what they are. To do that, go to Data to Advanced, let's just take a look at the options that we have
here. We can either filter the list in place, which is just like the way we've been doing so far, or we
can copy the results to another location. You need to define the List Range which is normally smart
enough and it picks up the correct range, then we can define the Criteria Range. So, this is where
you are going to type in the cells, you also have the ability to extract Unique Records and that's
something I'll show you at the end of the lecture, but let's see how we can use this Criteria Range.
What you need to do is to always start with the Header of the Criteria that you want to filter.
So, let's say I want to create a filter only on store here, okay I'm just going to unfilter this by
pressing Ctrl+Shift+L. What I need to do is to put the Header where Store is in, so in this case
is Channel. Now, you can either type it in or copy it over, it's not case-sensitive. Underneath it,
I'm going to type Store. Now, we're going to go to Advanced Filter, the list is fine we're going to
leave it to also filter the list in place. Now, the Criteria is this one and this includes your Header
because this way Filter knows where to look. Okay, that automatically filters the list based on Store.
The great thing about this is that you can use And/Or Conditions you can also use Wildcards
here. So, let's take a look at an And Condition first. Let's say, I want to filter for Store and
for Pants Blue, I'm going to put Product because that's the header and I'm gonna put Pants Blue.
When you put them beside one another, it means it's an And Condition. So, let's go back to Advanced,
now we need to expand our Criteria Range to both of these, and say ok. Ok so, that filters for Store
and Pants Blue, now you can also say if it doesn't equal Pants Blue. So, Smaller and Greater Sign
here, and press Enter, and now let's reapply the Filter and we get the list of everything else
that's not Pants Blue. Now, let's take a look at the Wildcards, what if we just wanted to look at
everything that has the word Pants in there? We can put the StarSign+Pants and then StarSign
after. So, this means that Pants could also be in the middle of our label, and if you just reapply
this we get everything that has the word Pants in there. Now, let's say I wanted to look for Store
and Pants or Store and Shirt White. Okay so, this calls for an Or Condition, this means that I need to type
the Second Criteria below this, but I also need to make sure that I have my other criterias also set,
because one criteria is Store and anything with Pants or its Store and anything that has the title
Shirt White in there. Okay so, let's apply Advanced Filter again, our range needs to be updated to this
and we say ok. Okay so, we have Store and Shirt White and Store with anything that has Pants in there.
Okay so, just remember that typing in below is an Or Condition, typing beside an And Condition. Let's
do something else, let's look for Sales that are greater than 200, I can expand on this. I can add
in Sales Value should be greater than 200, okay and also for this one it should be greater than
200, unless I want to define something different for this combination. What does this mean now? It
means it shouldn't show this and it shouldn't show these and that's it right? So, you should be
hiding these two lines. Let's go back to Advanced and let's expand our Filter. Okay so, they're gone.
Now, let's take it one step further as well and say don't show me anything that's above, let's do
above 400. So, basically the List Filtered should show these values that are between 200 and 400.
Okay so, we can copy this here and just change this to less than 400. Okay so, let's see which ones
this one should hide, this shouldn't be there, this one shouldn't be there. Okay so, let's test this
and let's update this to include all of these. Okay so, that's how you can use Advanced Filter, you
can also repeat the columns like we did here and put in different conditions in there. As long as
they make sense and the And Condition applies to them, like it does with numbers or with dates. What
I'm going to show you last is how to copy Unique Records into a separate location. So, for example
let's say you were interested to get the Unique List of Products out of the entire list. So, let
me just clear the filter that we have in place and let's go back to Advanced Filter. We're only
interested in Products, so I'm just going to highlight that entire column where I have Product in. Now,
criteria range in this case, I don't need to define that because my criteria is unique records only.
Now, let's say I want to copy into another location, so let's copy it right here, and say okay, and I
get the unique list of my products. So, these are the different ways that you can use Advanced
Filter in your Excel reports. I hope you like this video, if you did, don't forget to give
it a thumbs up, and for more videos like this one, don't forget to subscribe to this channel so
that you can get updates when new videos come up.
