Okay, so auto filters or simple filtering is very powerful and very useful. It's also very quick obviously, 'cause we get nice dropdown lists. Sometimes though, we need to do something more complicated and for that we may need to use an advanced filter. So let's look at how these work.

The first thing is that when we're using advanced filters we need to do some work on the spreadsheet before we even get into the advanced filter dialogue box. The first thing we need to do is set up a special range of the spreadsheet called the criteria list. So we're gonna build this little criteria area of our spreadsheet. Typically this is down at the top. Usually of the data that we are about to filter just so that it's nearby and we can see exactly what it is we're about to filter. So that's exactly what I'm gonna do. I'm just gonna add in some rows into my spreadsheet.

Give me some space at the top here. And I'm just gonna copy the titles from my database up into this new area of my spreadsheet. Okay, this is important because it means that when I'm building my criteria I can guarantee that the titles in my criteria area are gonna match exactly the titles that I've got in my database, because this is how it's gonna do this matching process. Okay, so let's take a typical example that we could achieve with an auto filter, but we are just gonna do it using advanced filters. So let's say I wanted to find all Betsy's sales in the South. So that's what I'm gonna do at the top here. I'm gonna set up and all it is. I'm gonna type in Betsy underneath name and then come across to location and we're gonna have the South.

Okay, so I've set up my criteria.

The next thing to do is I'm gonna come down to my database. So again, like we did before for turning our filters on we just have to have our cursor somewhere in the data. And then to go to an advanced filter it's alt A to get to the data ribbon and Q for advanced. So alt, A, Q. And we've got a number of different options. The most common of which, of course, is filter the list in place exactly like an auto filter would. So that's what we're gonna do first. The list range you'll notice is automatically picked for us. The criteria range is the thing that we need to go and select here. So click in the criteria range box and then all we need to do is go up to our criteria section of our sheet and we need to select all the column headings as well as the data we'd like to filter. So there's two rows. Hit enter or click on okay. And now you should see we've got the filter in place. So Betsy's sales in the South.

To turn off the filter we go back to the data ribbon. So alt A and then you can see C is clear and goes back to an unfiltered list. Okay, so even if I had the filter still in place and I hadn't cleared the filter worth saying that if I change the filters criteria at the top here. So if I change Betsy to Ashley nothing would change about the filter data. They're not dynamically linked in any way. There are no formulas driving this. To run this again I'd have to run the advanced filter again after I'd changed the filter.

I can go and start adding and removing items from this criteria list and then running the filter again. And then obviously I'll be editing my filtered list in place.

Let's talk about something a little bit more complicated then. Let's say we want to get not just Betsy's sales in the South but also Ashley's sales in the South.

It's important to realize that items on the same line in our criteria list are like saying and. So the filtered item has to match both Betsy and the South.

So name Betsy, location South, and if that's true it gets into our filtered list. So everything on the same row is like and and logic. To add all logic, we go on to multiple rows. So I could add in Ashley underneath here and now we would get Ashley's data, but notice that we haven't specified a location for Ashley. So we would be getting, if I were to run this filter now, Betsy's sales in the South as well as all of Ashley's sales. If I wanted all of Ashley's sales in the South, I would have to explicitly say I wanted Ashley's sales in the South.

Now of course I could still do this with an auto filter, because I could have Betsy's South, Ashley's South and that would be fine. But what isn't possible with an auto or simple filter is when you want to compare two different areas and two different people. So for example, let's say I wanted Betsy's South location I don't want to compare it with Ashley's Midwest location.

So I could filter and say I only want Betsy's South sales and Ashley's Midwest sales. Now when I run this auto filter I would only get those two items. So you can see that sometimes auto filters give you the capability to do some sorts of filtering that aren't possible using an auto filter. There are a couple of other features that the advanced feature has over the auto filters or simple filters.

The first one of these is that we can copy the data to another location. So if I were to add in a new sheet I'm gonna copy the headings from here and paste them into our new sheet here.

And this is where I wanted to extract that filtered data too. So the way to do this then is I can go to the sheet where I want to copy the data too. Make sure you are not underneath your column headings where you want to copy to, because otherwise Excel will think you're trying to filter this data list. So make sure your curse is just somewhere further down the list. Then we're gonna go into our advanced filter. So it alt, A, Q. We're gonna say we want to copy the list to another location. This time we are gonna go and choose the list range. So we're gonna come back to our filtering list and choose the whole list. The criteria range, well we've built that already. That's in our criteria at the top of our page. So we can go and select that.

And then where do we want to copy it to? Well, we want to copy it to the location where we've put our headers. Once we've got all that information in all we need to do is hit enter or click okay. And now we've got exactly the filter that we asked for. So Betsy's sales in the South and Ashley's sales in the Midwest. And we've extracted that out, leaving our filtered data. Well in fact, leaving our data completely unfiltered. All we've done is left the data alone, but just sucked out the bit of information that we requested. So that's one useful feature of an advanced filter. There's another really handy thing about an advanced filter and that's by saying that I only want unique items. So let's say that I wanted a list of salespeople. I've got this list of names and I know that these things repeat a number of times. What I want is a unique list of just them, the salespeople one person wants. So what I'm gonna do is I'm gonna just copy the name column and I'm just gonna move it out to the side here. So let's just get it at the edge here. So I'm just gonna put name here. And what I'm doing is I'm building up the columns that I'd like to extract. So when you do fill an advanced filter not only can you filter out rows, but I can filter out columns of data as well. So once I've done that, I'm gonna click into our data and we're gonna do an advanced filter. So alt, A, Q. we're gonna say I want to copy the list to another place. The list range is correct. I'm not gonna put a criteria range. I'm not filtering anything out. I'm just using the this unique records only feature. So I don't want a criteria, but I do want to tick this little box that says unique records only. Where do I want to copy it to? Well, that's the one I'm gonna only choose the name column, 'cause that's the only bit of information that I'd like to extract. And then click okay. And there we go. There's my extracted list of names from my database. Unique values only, very handy. So those are some of the features of advanced filters. Hopefully you'll find that really useful.