Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:04,900 --> 00:00:10,930
In the final lesson of this section, I'm going to show you how you can use yet another new function
2
00:00:10,930 --> 00:00:19,210
in Excel 2021, there are so many of them in this latest release and that is the filter function.
3
00:00:20,080 --> 00:00:23,140
Now we've seen how we can use our dropdown arrows to filter.
4
00:00:23,440 --> 00:00:27,970
We've seen how we can use the advanced filter to extract filtered results.
5
00:00:28,120 --> 00:00:33,280
And now I'm going to show you how you can utilize the filter function to do a similar thing.
6
00:00:34,150 --> 00:00:38,790
So let's start out basic and then we'll build up into a more complex filter.
7
00:00:38,830 --> 00:00:41,710
So we're going to use our good old student data again.
8
00:00:41,920 --> 00:00:50,890
And what I'm aiming to do here is I want to filter for all students who sat the English exam, and I
9
00:00:50,890 --> 00:00:55,270
want to output that list of students into this range of cells over here.
10
00:00:55,530 --> 00:00:58,750
I'm going to use the filter function in order to do that.
11
00:00:58,990 --> 00:01:03,430
So let's click in cell H5 and type in equals filter.
12
00:01:03,730 --> 00:01:09,730
Now, for this particular function, we have three arguments with the last one being optional.
13
00:01:10,570 --> 00:01:13,230
Now, the first argument here is the array.
14
00:01:13,240 --> 00:01:15,280
So what results do we want returned?
15
00:01:15,970 --> 00:01:20,290
Well, I actually want all of the results returned because I want to know the block, the student,
16
00:01:20,290 --> 00:01:21,940
the exam and the mark.
17
00:01:22,540 --> 00:01:27,010
So the array is going to be everything in this table.
18
00:01:27,010 --> 00:01:29,200
A5 to D 29.
19
00:01:29,830 --> 00:01:34,630
And remember, if you do want to make this completely dynamic, then you can put this data into a table
20
00:01:34,630 --> 00:01:35,320
beforehand.
21
00:01:36,100 --> 00:01:36,550
Comma.
22
00:01:37,540 --> 00:01:40,870
Now we need to tell Excel what we want to include.
23
00:01:40,870 --> 00:01:44,740
So this is effectively where we specify what we're filtering by.
24
00:01:44,980 --> 00:01:48,280
Now we're filtering by the exam English.
25
00:01:48,940 --> 00:01:56,710
So we need to say we want to include the exam and we select the range here when it equals.
26
00:01:57,790 --> 00:01:58,360
English.
27
00:01:59,680 --> 00:02:05,080
Now I've got mine listed out in a cell, if you wanted to hard code this in, you could just simply
28
00:02:05,080 --> 00:02:10,870
type in English in here and put it in quote marks, and it would effectively do exactly the same thing.
29
00:02:11,380 --> 00:02:15,430
But as we have it listed in a cell, I'm going to use the cell reference.
30
00:02:15,640 --> 00:02:20,290
Now those are the only two mandatory arguments, so I could close off my bracket and get my results.
31
00:02:20,560 --> 00:02:24,220
But let's just take a look at that final, optional argument, if empty.
32
00:02:25,690 --> 00:02:32,710
So what we can do here, additionally, is specify what we want it to say if the results of this filter
33
00:02:32,710 --> 00:02:33,700
is nothing.
34
00:02:33,700 --> 00:02:38,470
So if it doesn't match the word English in this table, what do we want it to say?
35
00:02:39,070 --> 00:02:42,430
So I'm just going to say just produce a blank cell.
36
00:02:42,460 --> 00:02:47,650
So to quote marks, let's close the bracket Hansa and see what we got.
37
00:02:48,310 --> 00:02:49,440
Now, take a look at that.
38
00:02:49,450 --> 00:02:54,550
I'm now getting a list of all of the students that sat the English exam.
39
00:02:54,850 --> 00:03:00,700
So this works really well, and if anything changes within this data, then this is going to update.
40
00:03:01,000 --> 00:03:06,580
But if we add new values to the bottom, we would need to make sure that this data is in a table in
41
00:03:06,580 --> 00:03:09,880
order to get a filter to update dynamically.
42
00:03:10,030 --> 00:03:14,700
So that is how you can use the filter function when you have one piece of criteria.
43
00:03:14,710 --> 00:03:20,920
So in the next example, let's take a look at how we can filter by multiple pieces of criteria multiple
44
00:03:20,920 --> 00:03:22,090
columns effectively.
45
00:03:22,660 --> 00:03:24,910
So let's jump across to the next worksheet.
46
00:03:25,300 --> 00:03:27,760
So now let's just delete out these results.
47
00:03:28,720 --> 00:03:32,200
I have pretty much the same thing, but we've added in a piece of criteria.
48
00:03:32,320 --> 00:03:39,580
Now we want to filter for all the students that sat the English exam who reside in the West Block.
49
00:03:40,480 --> 00:03:45,190
So we have two pieces of criteria, so we need to structure our formula in a slightly different way.
50
00:03:45,910 --> 00:03:49,760
So let's type in equals and filter again.
51
00:03:49,780 --> 00:03:53,980
The first thing we need to specify here is our array What do we want to return?
52
00:03:54,550 --> 00:03:56,000
Well, I want to return everything.
53
00:03:56,020 --> 00:03:58,810
So we're going to select all of the data.
54
00:03:59,890 --> 00:04:02,410
Now we need to specify what we want to include.
55
00:04:02,410 --> 00:04:09,130
So this is where we set up a filter or in this case, filters because we have to now, because we have
56
00:04:09,130 --> 00:04:13,120
multiple filters, we need to enclose them within brackets.
57
00:04:13,990 --> 00:04:16,720
So our first filter is the exam.
58
00:04:17,410 --> 00:04:26,550
So we need to select the exam range and that needs to equal English close our bracket.
59
00:04:26,560 --> 00:04:28,180
That is our first filter.
60
00:04:29,080 --> 00:04:35,740
We now need to specify our second filter and we separate add two filters with a multiplication sign.
61
00:04:36,760 --> 00:04:39,370
Let's open a bracket and do our next filter.
62
00:04:40,180 --> 00:04:44,050
So this second filter we're filtering for the Block West.
63
00:04:44,710 --> 00:04:53,500
So we need to select the block range and that needs to equal West close off the bracket.
64
00:04:54,130 --> 00:04:55,270
Now we could carry on going.
65
00:04:55,270 --> 00:05:00,610
If I had more pieces of criteria, I would just type in another multiplication sign and carry on going.
66
00:05:01,270 --> 00:05:03,280
But we only have two in this example.
67
00:05:03,640 --> 00:05:09,490
Let's press comma and let's specify what we wanted to say if it doesn't find any records.
68
00:05:10,060 --> 00:05:18,580
Now, this time I wanted to say no records, and that needs to go in quotes and close off our bracket.
69
00:05:19,690 --> 00:05:20,590
Let's enter.
70
00:05:21,010 --> 00:05:22,030
And there we go.
71
00:05:22,030 --> 00:05:23,560
We have our results list.
72
00:05:24,250 --> 00:05:28,510
And if this exam changes, so maybe now I want to see the results for the French exam.
73
00:05:28,630 --> 00:05:35,440
That's going to update and the East Block may be I want to see results for the maths exam.
74
00:05:35,680 --> 00:05:36,760
Now take a look at that.
75
00:05:36,760 --> 00:05:41,140
The maths exam for the East Block has no records.
76
00:05:41,740 --> 00:05:45,860
Now we do have a small typo there, so let's just retype that to make sure that still works.
77
00:05:45,880 --> 00:05:46,630
Yes, it does.
78
00:05:46,900 --> 00:05:50,620
So this is all extremely dynamic.
79
00:05:51,070 --> 00:05:58,120
Now, in the final example of using Filter, I want to apply three filters this time, but I also want
80
00:05:58,120 --> 00:06:04,600
to sort my results and we can do this by combining the filter and the source functions together.
81
00:06:04,750 --> 00:06:11,290
So this time I want to filter for all students that sat the English exam who are located in the West
82
00:06:11,290 --> 00:06:15,260
Block and who have a pass mark that's greater than 50.
83
00:06:16,210 --> 00:06:17,290
So let's click.
84
00:06:17,500 --> 00:06:25,390
And the first thing we type in here is we need to type salt and then go straight into a filter.
85
00:06:26,500 --> 00:06:27,850
What are we filtering for?
86
00:06:27,850 --> 00:06:32,980
What do we want to return while we want to return everything in this list, comma?
87
00:06:33,550 --> 00:06:39,280
Now we can set up our filters and this time we have three separate filters.
88
00:06:39,910 --> 00:06:44,260
Now remember, if you have multiple filters, they need to be enclosed in brackets.
89
00:06:45,190 --> 00:06:52,510
So our first filter is going to be when the exam equals English.
90
00:06:53,560 --> 00:06:54,760
That's our first filter.
91
00:06:55,330 --> 00:06:57,270
We separate a separate filters.
92
00:06:57,450 --> 00:07:01,950
With an Asterix, and now we can specify a second filter.
93
00:07:02,820 --> 00:07:04,740
So when the block.
94
00:07:05,810 --> 00:07:16,100
Equals West close off that filter, and we have a third one, so Asterix again open a bracket when the
95
00:07:16,130 --> 00:07:19,550
mark is greater than.
96
00:07:20,900 --> 00:07:21,480
50.
97
00:07:23,360 --> 00:07:25,770
Close the bracket, coma.
98
00:07:26,720 --> 00:07:32,090
We now have that optional argument where we can specify what we want it to return if it doesn't find
99
00:07:32,090 --> 00:07:32,900
any results.
100
00:07:32,990 --> 00:07:37,340
So I'm just going to say once again, no records.
101
00:07:38,030 --> 00:07:42,860
Let's close off our filter and we're now back into assault.
102
00:07:43,370 --> 00:07:47,330
So this is where we can specify exactly how we want this list sorted.
103
00:07:47,680 --> 00:07:53,420
I what I'm going to say is here, once I get my filtered results, I want to sort them in descending
104
00:07:53,420 --> 00:07:55,250
order by the mark.
105
00:07:55,610 --> 00:07:58,700
So the first argument for sort is the array.
106
00:07:59,210 --> 00:08:07,310
Now the array is going to be generated by that filter function so we can press coma to move on to the
107
00:08:07,310 --> 00:08:08,060
next argument.
108
00:08:08,900 --> 00:08:15,140
This is where we specify the sort index of the column that we want to sort by and remember when we were
109
00:08:15,140 --> 00:08:19,460
looking at sort sort numbers, columns from left to right.
110
00:08:20,270 --> 00:08:26,300
So I want to sort by the mark column, which is column number four comma.
111
00:08:27,140 --> 00:08:31,910
Now I can specify if I want to soar in ascending or descending order.
112
00:08:32,600 --> 00:08:38,090
Well, I want to sort in descending order, so we want a minus one in here comma.
113
00:08:38,480 --> 00:08:40,880
We do have an optional argument on the end here.
114
00:08:41,090 --> 00:08:43,640
We don't actually need this, so I'm not going to add it.
115
00:08:43,940 --> 00:08:49,130
Let's close off as sort of enter and take a look at our results.
116
00:08:50,000 --> 00:08:58,190
So we're only seeing the English exam for the West Block and the marks are all above 50 and they're
117
00:08:58,190 --> 00:09:01,670
sorted in descending order by the mark.
118
00:09:02,090 --> 00:09:07,940
So if I change this filter and take this mock up to 180, you can see my results update.
119
00:09:08,030 --> 00:09:09,680
Let's put that back down to 50.
120
00:09:10,520 --> 00:09:17,270
If I change the block to East, I get one result if I change the exam to French.
121
00:09:18,430 --> 00:09:24,340
I get a different set of results, so we've managed to really effectively combine that filter and sought
122
00:09:24,340 --> 00:09:29,380
to get a really nice, filtered and sorted list using dynamic functions.
12284
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.