Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:00
this lesson will be about another very useful function off data tab in ribbon. And those are filters
00:09
and the filters can do similar things, like sorting data, but
00:17
they actually are abused for a rather different purpose.
00:22
So, um,
00:23
you have again this same table
00:27
and using filters is quite simple. We'll just click inside this stable
00:32
in logistical filters
00:34
and not Excel does, in this case is automatically finds the portion off the worksheet that is filled with
00:44
datum
00:46
and adds this dropdown list in every
00:51
Coleman.
00:53
Uh, and, uh,
00:56
it is necessary or makes absolute sense for your table toe. Have Heather
01:02
because thes dropdown list will be added to the heather row off the
01:07
that table.
01:08
And there you will be able to select,
01:14
uh, what's happening with the rest of the table. So what are filters doing? They're shutting or turning on, viewing off certain parts of the stable.
01:26
So in this table we have only one cone that makes sense to be used
01:32
on, and this is the shift part. So
01:34
when you hope when you click on the drop down list, you have the options to sort,
01:40
you can sort it, too. That's that to a or by color.
01:44
And you can
01:46
also filter out certain
01:49
content off that column.
01:53
So if you click on morning and be selected
01:57
everything that has morning in cell, every road that has morning in income see will be will not be displayed. So
02:07
this is it.
02:08
And ah,
02:10
Then if we click here
02:14
and, uh,
02:16
the select
02:17
everything
02:20
and just want the top three and minded, these three thes entries here automatically sorted. So you, if you want
02:30
three highest, you just go to the button,
02:35
click, okay, and you get displayed. Only these.
02:38
If you want to turn it back,
02:43
you just select all.
02:45
You look okay.
02:46
And this is how the filtering works.
02:51
Um,
02:53
so ah, you can clear the filtering. So you did the filtering. You don't need it anymore. You don't want to save your fire with it. You just get there and you get back.
03:05
And also you can do some advanced filtering
03:08
in which you can use ah rather complex criteria.
03:14
So ah, here's the list.
03:19
Use your selecting. So this this is the one
03:22
and you can then choose the criteria
03:25
range for this table.
03:34
And this range doesn't exist on our table. So we're going to cancel this for a moment.
03:44
And what we're going to do is we're going to turn the filters off.
03:51
And, uh
03:55
So, uh,
03:58
we're going to copy this.
04:01
I think this role
04:03
and we'll put going to put it here just for the
04:08
this demonstration.
04:10
And ah. Then we're going to,
04:13
uh, copy morning
04:16
here in a shift.
04:21
And let's go now to advanced.
04:26
So XlV automatically select this
04:29
and then we click on criteria range, and then we select this
04:33
range here.
04:34
So what Excel will do is it will look here
04:40
and look where we have something entered, which is shift.
04:44
And in this shift, it will find morning. So if you click here,
04:49
it will have the same effect. Like we have gone too.
04:54
Ah, uh,
04:55
the extended regular filters
04:59
and selected only morning in the shift column.
05:02
So let's clear it, clear it.
05:04
And there's something else which you cannot do with regular filtering. So let's hear. Enter the new column, which is going to be
05:15
above
05:16
quota.
05:20
And let's say that
05:24
all sense people have to make quota above 30,000
05:30
two
05:31
to to do what they're supposed to do.
05:33
So we're going to hear put some
05:36
if
05:40
function
05:42
and you guys say revenue generated
05:45
is greater than 30,000.
05:49
And then what we want in this case,
05:54
true.
05:56
Sorry, it has to be true.
05:59
Otherwise. Fools.
06:02
So, um, if,
06:05
ah,
06:08
the cell is about 30,000 it's going to return through.
06:12
And then that will tell that this filter, advanced filter and excel to display that role.
06:19
If it's smaller than 30,000
06:24
it's going to be false that the value of that this, uh, this function is going to be false, in which case that row won't be this plate.
06:33
So let's percent there.
06:35
Let's now click here. Let's go to the advanced
06:40
and then we go to criteria range again. We delete the old one and we just select these two.
06:48
And now what we have here are on Lee
06:53
those that their greater than 30,000 all the others have bean removed by this filtering advanced filtering function.
07:02
So this is how filter works. And this concludes this lesson

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor