Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,050 --> 00:00:08,290
So now we've had a thorough look at sorting our data.
2
00:00:08,370 --> 00:00:15,370
It's time to move on to filtering now most of us are familiar with using the dropdown arrows to filter
3
00:00:15,370 --> 00:00:19,450
our datasets and extract exactly the information that we're interested in.
4
00:00:19,690 --> 00:00:23,770
But did you know that there is also an advanced filter option?
5
00:00:24,460 --> 00:00:30,100
And in my opinion, this is one of the most underrated commands in Excel because it's not something
6
00:00:30,100 --> 00:00:31,930
I see talked about very often.
7
00:00:32,770 --> 00:00:39,070
So let's take a look at how the advanced filter works because it has many uses now in this first example.
8
00:00:39,100 --> 00:00:45,070
I'm going to show you how you can use the advanced filter to extract a unique list of values from your
9
00:00:45,070 --> 00:00:45,670
dataset.
10
00:00:46,540 --> 00:00:50,640
So if we take a look at the table on the left hand side, I have a list of sales reps.
11
00:00:50,650 --> 00:00:55,390
I have the region that they belong to and then their sales information.
12
00:00:55,870 --> 00:01:02,050
Now notice here in the sales rep column, I have the sales rep names repeated over and over again.
13
00:01:02,380 --> 00:01:07,660
So technically, there's only roughly five sales reps that just repeated throughout this data.
14
00:01:08,470 --> 00:01:14,380
Now, maybe I want to extract from this column just a unique list of all of the sales reps.
15
00:01:14,920 --> 00:01:18,160
And I find this is something I have to do all the time.
16
00:01:18,460 --> 00:01:22,450
Now we can extract two unique list using Excel's advanced filter.
17
00:01:22,750 --> 00:01:25,180
So let's take a look at that, first of all.
18
00:01:25,990 --> 00:01:32,530
So if I click somewhere over here, I'm going to say unique list of sales reps.
19
00:01:33,820 --> 00:01:38,470
And then underneath here, I just want the five or so names of the sales reps.
20
00:01:39,310 --> 00:01:45,160
So what we can do here is jump up to the data tab and in the sort of filter group, we have an advanced
21
00:01:45,160 --> 00:01:48,310
filter option and you can see here with the screen tab.
22
00:01:48,310 --> 00:01:52,600
It says it contains options for filtering using complex criteria.
23
00:01:53,500 --> 00:01:55,540
So let's click on Advanced.
24
00:01:56,020 --> 00:02:00,820
Now the thing with this advanced filter in this little pane opens up is it does hold in it the last
25
00:02:00,820 --> 00:02:01,990
criteria you used.
26
00:02:02,440 --> 00:02:07,000
So we're going to delete that out in a moment, but the first thing we need to specify is what action
27
00:02:07,000 --> 00:02:07,930
we want to perform.
28
00:02:08,680 --> 00:02:13,360
Now do I want to filter this list in place or do I want to copy it to another location?
29
00:02:13,540 --> 00:02:19,270
Well, I want to extract a unique list of the sales rep names to a different cell.
30
00:02:19,840 --> 00:02:22,150
So we want to copy to another location.
31
00:02:22,360 --> 00:02:26,680
And then we have three blank fields to complete.
32
00:02:27,430 --> 00:02:31,630
Now, when you're extracting a unique list of records, we only actually need to complete two of these.
33
00:02:32,200 --> 00:02:34,570
And the first one is the list range.
34
00:02:34,960 --> 00:02:37,300
So where are the values that we want to extract?
35
00:02:37,930 --> 00:02:40,690
Well, they're in the sales rep column.
36
00:02:40,990 --> 00:02:46,570
Now for this one, we don't need to specify any criteria, but we do need to let excel know where we
37
00:02:46,570 --> 00:02:48,400
want to copy these unique values.
38
00:02:49,030 --> 00:02:53,200
And I want to copy them to this sale just here f 11.
39
00:02:53,470 --> 00:02:59,620
And the most important thing is to make sure you select unique records only when we click on.
40
00:02:59,980 --> 00:03:00,670
OK.
41
00:03:00,910 --> 00:03:01,780
Take a look at that.
42
00:03:01,780 --> 00:03:04,030
I now have my unique list.
43
00:03:04,330 --> 00:03:08,110
Now there is a way to do the exact same thing by using a formula.
44
00:03:08,590 --> 00:03:14,590
And this is again one of those new formulas in Excel 2021, and I'm going to show you how that works
45
00:03:14,590 --> 00:03:15,640
in the next lesson.
46
00:03:16,210 --> 00:03:21,580
But for the time being, you can also use your advanced filter to extract a unique list of values.
47
00:03:21,790 --> 00:03:24,190
Now what else can we use this advanced filter for?
48
00:03:24,190 --> 00:03:27,070
Because that is by no means all we can do with this?
49
00:03:27,730 --> 00:03:31,990
Well, let's jump across to our next worksheet where we have the same data.
50
00:03:32,170 --> 00:03:39,430
We can also use the advanced filter to extract values based on specific pieces of criteria.
51
00:03:39,700 --> 00:03:44,050
And the way this works is you have to have your criteria listed out in your spreadsheet.
52
00:03:44,890 --> 00:03:47,320
So you can see here in this little table over here.
53
00:03:48,100 --> 00:03:56,830
I want to extract from this table all of the records where the region is Asia and the sales total is
54
00:03:56,830 --> 00:03:58,570
greater than 7000.
55
00:03:59,320 --> 00:04:02,650
And we can do this again using our advanced filter.
56
00:04:03,040 --> 00:04:04,990
So I'm going to click somewhere underneath.
57
00:04:05,380 --> 00:04:07,150
Let's click on Advanced.
58
00:04:08,260 --> 00:04:10,600
Now I want to copy to another location.
59
00:04:10,690 --> 00:04:13,080
Let's make sure we select that and live action.
60
00:04:13,090 --> 00:04:16,060
And then I'm just going to delete out all of this old criteria.
61
00:04:17,200 --> 00:04:21,160
Now for this one, we are going to utilize all three of these boxes.
62
00:04:21,430 --> 00:04:25,690
Now, the first thing we want to select is the information we want to extract.
63
00:04:26,320 --> 00:04:31,390
While I want to extract the date, the sales rep, the region and the sales information.
64
00:04:31,390 --> 00:04:33,460
So basically everything in my table.
65
00:04:34,120 --> 00:04:38,290
So my list range is going to be my entire table.
66
00:04:39,040 --> 00:04:46,140
I then need to specify my criteria range and I have my criteria listed in this small table just above.
67
00:04:46,180 --> 00:04:49,390
So I need to select the entire little table.
68
00:04:50,500 --> 00:04:54,220
Then I can specify where I want to copy these results to.
69
00:04:54,910 --> 00:04:57,730
So let's click on Sell F nine.
70
00:04:58,030 --> 00:05:02,680
I don't need to select unique records this time because that's not what we're doing.
71
00:05:02,890 --> 00:05:04,360
All I need to do here is click.
72
00:05:04,430 --> 00:05:04,670
On.
73
00:05:04,940 --> 00:05:05,510
OK.
74
00:05:06,080 --> 00:05:11,900
And take a look at that, I now have a filtered list that showing me just the records where the region
75
00:05:11,900 --> 00:05:16,310
is Asia and the sales are over 7000.
76
00:05:17,030 --> 00:05:25,310
So effectively what we've done here is a filter using and criteria filter for Asia and sales greater
77
00:05:25,310 --> 00:05:26,300
than 7000.
78
00:05:26,690 --> 00:05:29,990
Now I can also filter using or criteria.
79
00:05:30,530 --> 00:05:32,990
So let's take a look at what that would look like as well.
80
00:05:33,560 --> 00:05:36,900
So I'm just going to clear everything from here.
81
00:05:37,580 --> 00:05:40,070
And let's change our filter criteria.
82
00:05:40,850 --> 00:05:45,920
Now, if you want to do in all your criteria, needs to be listed one above the other.
83
00:05:46,640 --> 00:05:55,520
So maybe I want to filter for records where the sales rep is Ben or Deb, and let's just keep our formatting
84
00:05:55,520 --> 00:05:56,240
consistent.
85
00:05:56,540 --> 00:05:57,840
Let's just apply that.
86
00:05:58,430 --> 00:06:01,850
And let's also apply that like so.
87
00:06:02,210 --> 00:06:06,410
So now I have these in here, it's going to do and or filter.
88
00:06:07,220 --> 00:06:11,510
So let's go back to our data, tap into advance.
89
00:06:11,510 --> 00:06:14,090
And we're basically going to do a very similar thing.
90
00:06:14,090 --> 00:06:18,230
We're going to copy to another location and let's remove all of these.
91
00:06:18,230 --> 00:06:24,080
I like to start from a clean slate, a list range where we want to pull back all of the information,
92
00:06:24,080 --> 00:06:25,760
so we select the entire table.
93
00:06:26,540 --> 00:06:34,970
Our criteria range is this table just here and we want to copy to this cell and click on OK.
94
00:06:35,210 --> 00:06:41,900
And now I should find that my filtered results are only for the sales reps, Ben or Deb.
95
00:06:42,260 --> 00:06:44,990
So that is how the advanced filter works.
96
00:06:45,260 --> 00:06:50,510
It's great for extracting unique lists of information, but it's also great for filtering data using
97
00:06:50,510 --> 00:06:52,040
more complex criteria.
9575
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.