All language subtitles for 4. Using the Advanced Filter

So now we've had a thorough look at sorting our data.

It's time to move on to filtering now most of us are familiar with using the dropdown arrows to filter

our datasets and extract exactly the information that we're interested in.

But did you know that there is also an advanced filter option?

And in my opinion, this is one of the most underrated commands in Excel because it's not something

I see talked about very often.

So let's take a look at how the advanced filter works because it has many uses now in this first example.

I'm going to show you how you can use the advanced filter to extract a unique list of values from your

dataset.

So if we take a look at the table on the left hand side, I have a list of sales reps.

I have the region that they belong to and then their sales information.

Now notice here in the sales rep column, I have the sales rep names repeated over and over again.

So technically, there's only roughly five sales reps that just repeated throughout this data.

Now, maybe I want to extract from this column just a unique list of all of the sales reps.

And I find this is something I have to do all the time.

Now we can extract two unique list using Excel's advanced filter.

So let's take a look at that, first of all.

So if I click somewhere over here, I'm going to say unique list of sales reps.

And then underneath here, I just want the five or so names of the sales reps.

So what we can do here is jump up to the data tab and in the sort of filter group, we have an advanced

filter option and you can see here with the screen tab.

It says it contains options for filtering using complex criteria.

So let's click on Advanced.

Now the thing with this advanced filter in this little pane opens up is it does hold in it the last

criteria you used.

So we're going to delete that out in a moment, but the first thing we need to specify is what action

we want to perform.

Now do I want to filter this list in place or do I want to copy it to another location?

Well, I want to extract a unique list of the sales rep names to a different cell.

So we want to copy to another location.

And then we have three blank fields to complete.

Now, when you're extracting a unique list of records, we only actually need to complete two of these.

And the first one is the list range.

So where are the values that we want to extract?

Well, they're in the sales rep column.

Now for this one, we don't need to specify any criteria, but we do need to let excel know where we

want to copy these unique values.

And I want to copy them to this sale just here f 11.

And the most important thing is to make sure you select unique records only when we click on.

OK.

Take a look at that.

I now have my unique list.

Now there is a way to do the exact same thing by using a formula.

And this is again one of those new formulas in Excel 2021, and I'm going to show you how that works

in the next lesson.

But for the time being, you can also use your advanced filter to extract a unique list of values.

Now what else can we use this advanced filter for?

Because that is by no means all we can do with this?

Well, let's jump across to our next worksheet where we have the same data.

We can also use the advanced filter to extract values based on specific pieces of criteria.

And the way this works is you have to have your criteria listed out in your spreadsheet.

So you can see here in this little table over here.

I want to extract from this table all of the records where the region is Asia and the sales total is

greater than 7000.

And we can do this again using our advanced filter.

So I'm going to click somewhere underneath.

Let's click on Advanced.

Now I want to copy to another location.

Let's make sure we select that and live action.

And then I'm just going to delete out all of this old criteria.

Now for this one, we are going to utilize all three of these boxes.

Now, the first thing we want to select is the information we want to extract.

While I want to extract the date, the sales rep, the region and the sales information.

So basically everything in my table.

So my list range is going to be my entire table.

I then need to specify my criteria range and I have my criteria listed in this small table just above.

So I need to select the entire little table.

Then I can specify where I want to copy these results to.

So let's click on Sell F nine.

I don't need to select unique records this time because that's not what we're doing.

All I need to do here is click.

On.

OK.

And take a look at that, I now have a filtered list that showing me just the records where the region

is Asia and the sales are over 7000.

So effectively what we've done here is a filter using and criteria filter for Asia and sales greater

than 7000.

Now I can also filter using or criteria.

So let's take a look at what that would look like as well.

So I'm just going to clear everything from here.

And let's change our filter criteria.

Now, if you want to do in all your criteria, needs to be listed one above the other.

So maybe I want to filter for records where the sales rep is Ben or Deb, and let's just keep our formatting

consistent.

Let's just apply that.

And let's also apply that like so.

So now I have these in here, it's going to do and or filter.

So let's go back to our data, tap into advance.

And we're basically going to do a very similar thing.

We're going to copy to another location and let's remove all of these.

I like to start from a clean slate, a list range where we want to pull back all of the information,

so we select the entire table.

Our criteria range is this table just here and we want to copy to this cell and click on OK.

And now I should find that my filtered results are only for the sales reps, Ben or Deb.

So that is how the advanced filter works.

It's great for extracting unique lists of information, but it's also great for filtering data using

more complex criteria.