Original subtitles

In the final lesson of this section, I'm going to show you how you can use yet another new function

in Excel 2021, there are so many of them in this latest release and that is the filter function.

Now we've seen how we can use our dropdown arrows to filter.

We've seen how we can use the advanced filter to extract filtered results.

And now I'm going to show you how you can utilize the filter function to do a similar thing.

So let's start out basic and then we'll build up into a more complex filter.

So we're going to use our good old student data again.

And what I'm aiming to do here is I want to filter for all students who sat the English exam, and I

want to output that list of students into this range of cells over here.

I'm going to use the filter function in order to do that.

So let's click in cell H5 and type in equals filter.

Now, for this particular function, we have three arguments with the last one being optional.

Now, the first argument here is the array.

So what results do we want returned?

Well, I actually want all of the results returned because I want to know the block, the student,

the exam and the mark.

So the array is going to be everything in this table.

A5 to D 29.

And remember, if you do want to make this completely dynamic, then you can put this data into a table

beforehand.

Comma.

Now we need to tell Excel what we want to include.

So this is effectively where we specify what we're filtering by.

Now we're filtering by the exam English.

So we need to say we want to include the exam and we select the range here when it equals.

English.

Now I've got mine listed out in a cell, if you wanted to hard code this in, you could just simply

type in English in here and put it in quote marks, and it would effectively do exactly the same thing.

But as we have it listed in a cell, I'm going to use the cell reference.

Now those are the only two mandatory arguments, so I could close off my bracket and get my results.

But let's just take a look at that final, optional argument, if empty.

So what we can do here, additionally, is specify what we want it to say if the results of this filter

is nothing.

So if it doesn't match the word English in this table, what do we want it to say?

So I'm just going to say just produce a blank cell.

So to quote marks, let's close the bracket Hansa and see what we got.

Now, take a look at that.

I'm now getting a list of all of the students that sat the English exam.

So this works really well, and if anything changes within this data, then this is going to update.

But if we add new values to the bottom, we would need to make sure that this data is in a table in

order to get a filter to update dynamically.

So that is how you can use the filter function when you have one piece of criteria.

So in the next example, let's take a look at how we can filter by multiple pieces of criteria multiple

columns effectively.

So let's jump across to the next worksheet.

So now let's just delete out these results.

I have pretty much the same thing, but we've added in a piece of criteria.

Now we want to filter for all the students that sat the English exam who reside in the West Block.

So we have two pieces of criteria, so we need to structure our formula in a slightly different way.

So let's type in equals and filter again.

The first thing we need to specify here is our array What do we want to return?

Well, I want to return everything.

So we're going to select all of the data.

Now we need to specify what we want to include.

So this is where we set up a filter or in this case, filters because we have to now, because we have

multiple filters, we need to enclose them within brackets.

So our first filter is the exam.

So we need to select the exam range and that needs to equal English close our bracket.

That is our first filter.

We now need to specify our second filter and we separate add two filters with a multiplication sign.

Let's open a bracket and do our next filter.

So this second filter we're filtering for the Block West.

So we need to select the block range and that needs to equal West close off the bracket.

Now we could carry on going.

If I had more pieces of criteria, I would just type in another multiplication sign and carry on going.

But we only have two in this example.

Let's press comma and let's specify what we wanted to say if it doesn't find any records.

Now, this time I wanted to say no records, and that needs to go in quotes and close off our bracket.

Let's enter.

And there we go.

We have our results list.

And if this exam changes, so maybe now I want to see the results for the French exam.

That's going to update and the East Block may be I want to see results for the maths exam.

Now take a look at that.

The maths exam for the East Block has no records.

Now we do have a small typo there, so let's just retype that to make sure that still works.

Yes, it does.

So this is all extremely dynamic.

Now, in the final example of using Filter, I want to apply three filters this time, but I also want

to sort my results and we can do this by combining the filter and the source functions together.

So this time I want to filter for all students that sat the English exam who are located in the West

Block and who have a pass mark that's greater than 50.

So let's click.

And the first thing we type in here is we need to type salt and then go straight into a filter.

What are we filtering for?

What do we want to return while we want to return everything in this list, comma?

Now we can set up our filters and this time we have three separate filters.

Now remember, if you have multiple filters, they need to be enclosed in brackets.

So our first filter is going to be when the exam equals English.

That's our first filter.

We separate a separate filters.

With an Asterix, and now we can specify a second filter.

So when the block.

Equals West close off that filter, and we have a third one, so Asterix again open a bracket when the

mark is greater than.

50.

Close the bracket, coma.

We now have that optional argument where we can specify what we want it to return if it doesn't find

any results.

So I'm just going to say once again, no records.

Let's close off our filter and we're now back into assault.

So this is where we can specify exactly how we want this list sorted.

I what I'm going to say is here, once I get my filtered results, I want to sort them in descending

order by the mark.

So the first argument for sort is the array.

Now the array is going to be generated by that filter function so we can press coma to move on to the

next argument.

This is where we specify the sort index of the column that we want to sort by and remember when we were

looking at sort sort numbers, columns from left to right.

So I want to sort by the mark column, which is column number four comma.

Now I can specify if I want to soar in ascending or descending order.

Well, I want to sort in descending order, so we want a minus one in here comma.

We do have an optional argument on the end here.

We don't actually need this, so I'm not going to add it.

Let's close off as sort of enter and take a look at our results.

So we're only seeing the English exam for the West Block and the marks are all above 50 and they're

sorted in descending order by the mark.

So if I change this filter and take this mock up to 180, you can see my results update.

Let's put that back down to 50.

If I change the block to East, I get one result if I change the exam to French.

I get a different set of results, so we've managed to really effectively combine that filter and sought

to get a really nice, filtered and sorted list using dynamic functions.