Instructor: All right, next up we have
one of the most powerful dynamic array functions out there,
the filter function.
And what this does is it filters an array of data
based on specified criteria
and then only returns the matching results or records.
Syntax wise, pretty straightforward.
You've got two required parameters or arguments here.
The first is the array of cells that you want to filter.
Keep in mind this is gonna dictate the size
of your spill range.
And then the second required parameter here
is the include argument.
This is where you drop in any logical test
that can be used to determine the filter criteria.
So any kind of test that evaluates to true or false.
And what Excel is gonna do is it's gonna run through
that logical test for every row in the array,
and it's only gonna keep the records that evaluate to true.
So that's essentially how it's filtering out
any rows that don't meet the filter criteria.
And then last optional argument here
you can specify some text string to return
in case no values pass the filter.
So quick example here.
Here we're looking at product data in columns A through C.
We've got product names, category names, and sales.
And what we're doing in this case is we're filtering
that entire source range, A2 through C10,
but only for cases where the category from column B
is equal to Clothing.
And you could do that a couple ways.
You could type B2 through B10 equals Clothing in quotes,
or like we're showing here, you could point to a cell
containing that value, which in this case is F1.
And here we're using that optional argument as well
so that if no products pass the filter
or if you've entered an invalid category
or a category name that doesn't exist,
Excel is gonna return no results as a text string
as opposed to a calculation error.
So the results of that filter function,
it's a filtered down array of values from A2 through C10
only where the Category equals Clothing.
Now that's powerful in itself,
but where filter becomes really dynamic and really flexible
is when you start integrating multiple logical tests
to combine multiple filter criteria.
And let me show you an example of what I mean by that.
Let's look at this variation on the example we just showed.
Now instead of that one logical test, B2 through B10,
equaling F1 or Clothing.
Now we have a second test as well.
We're checking if the values in column C, C2 through C10,
those Sales figures are greater than 5,000,
the value in cell F2.
And you'll notice that those two logical tests
are multiplied together.
And that's how we create an and condition.
In other words, we're telling Excel
only return filtered values
if they meet both of these criteria
or if they pass both of these logical tests.
So the result would be something like this.
This array is returning values, again, from A2 through C10,
but in this case, only where the Category equals Clothing
and the Sales are greater than 5,000.
So, again, that multiplication operation
means that both criteria must be met.
Now if you want to create or logic instead of and,
you could use something like this,
the exact same logical tests, exact same filter criteria,
Category equals Clothing, Sales are greater than 5,000,
the only difference here is that we're adding
those two filter criteria together
and that allows us to create an or condition
between those logical tests.
So the result from this function would return values,
again, from our raw array, A2 through C10,
where the Category equals Clothing
or the Sales are greater than 5,000.
Either criteria can be met.
Now I'm gonna pause there
'cause you may be kinda scratching your head,
wondering what things like addition and multiplication
have to do with logical tests.
And don't worry, I'm gonna jump into Excel.
I'm gonna show you first how these filter functions work.
We're gonna practice applying them to our Glassdoor data,
and then I'm gonna actually give you a peek
behind the scenes and show you how Excel
is actually processing these filter criteria arguments
to create these and and or conditions
like we're talking about,
'cause it's actually pretty fascinating
what's going on under the hood.
So with that, let's jump back into our Excel workbook
and give this a shot.
All right, so as always, if you'd like to follow along,
jump into your Section9 workbook, head to the FILTER tab,
and here's the raw data that we're working with now.
So now, instead of just looking at industries,
we have industries and job titles as well here in column B
along with average salary and year-over-year growth rates
here in column C and D.
So our goal here is to use filter functions
to basically pull out filtered lists
from that raw source array based on certain criteria.
And the first case here, what we'd like to do,
is simply return a filtered job list for a given industry.
And we've got a data validation dropdown cell here
containing each of the unique industries,
and down here, starting in row 7,
this is where I wanna return that filtered list.
So let's go ahead and give this filter function a shot.
The array that we're referencing
is the entire array of data.
We wanna pull in all of these columns into our spill range.
So I'm gonna actually select A3,
do my favorite Control + Shift + Arrow Right, Arrow Down,
that's gonna grab the entire reference out to D86.
Comma over to the include argument,
and remember, this is where I put my logical test
or my filter criteria to tell Excel
which of those rows or records I want to keep in my results.
So just in plain English, I want to keep any row
where the Industry equals, right now Manufacturing,
or whatever Industry is selected there in cell G3.
So to tell Excel to do that,
we're gonna select the industries in A3 through A86
and we're gonna say =G3.
And now Excel is gonna iterate through each row in column A.
It's gonna check, does that Industry name
equal the Industry selected in G3?
If yes, that's gonna equate to a true.
If no, that equates to a false
and all the falses will be excluded
and I'll return only the rows that produce a true outcome
in that filter criteria.
Last but not least, the optional argument if_empty.
We could do something like,
"No jobs for the selected industry"
or anything you want, right?
Close it off, press Enter, and check it out.
We've got all of our Manufacturing jobs:
Production Manager, Process Engineer, Material Handler,
and Manufacturing Engineer.
And as you might expect, this is all dynamic.
We got Media jobs, Operations jobs, Sales jobs,
Technology jobs, very simple using a filter function.
But of course we're not gonna stop there.
Now suppose we want to account
for multiple filter criteria, okay?
Now we want the job list for a given industry,
just like we did, but we also wanna add a criteria
for Salary.
So we wanna see all of the Technology jobs, in this case,
with an average salary greater than 100,000,
or whatever value is entered here in cell L4.
And not only that, but we actually just wanna return
the Job Title with the Salary and the Growth.
We actually don't wanna repeat the Industry name here
again and again, like we did in this case.
So let's give this a shot here.
=FILTER, and remember, the array that we select here
is gonna dictate our spill range.
And in this case, we only want Title, Salary, and Growth.
So I only wanna grab B3 down to D86, okay?
I'm gonna comma over.
This is where I start putting my filter criteria in.
In this case, we have two criteria, not just one.
The first is gonna be that Industry equals
whatever's selected in L3.
And check it out, just like Group by,
we can include logical tests that reference cells
outside of our array reference, which is really great.
So here we can select A3 through A86,
and say we wanna return records from our array
only where those industries equal L3, Technology.
And instead of stopping here,
this is where we're gonna add a second logical test.
And what I can do is just surround this first one
in parentheses.
And because we're creating an and condition here,
we want only records that meet both criteria.
I'm gonna use the multiplication operator here
and then get started with my second logical test,
which in this case is my Salary, C3 through C86,
is greater than whatever minimum salary we've entered
right there in L4.
Close it off.
Go to my last optional argument here,
which again could be anything you want.
"No jobs meet those criteria."
Close the final parenthesis, press Enter,
and check this out.
We've got four jobs here, which are all Technology jobs,
and all four have an average salary greater than 100,000.
I change this to 120,000.
Now we only have one job returned.
So I told you I was gonna dig into
how the and and or logic actually works
and why using things like addition and multiplication
with those logical tests allows us to create
that type of logic.
And I'm actually gonna show you
exactly how it's working here.
I'm gonna insert a bunch of columns here.
Let's insert, you know, like, five columns.
Give us some room to breathe and check it out.
What I'm gonna do here is actually just
extract the filter criteria arguments from this function
and paste them as their own formulas here.
And watch what happens when I do that.
So this is the first criteria
where Industry equals Technology.
Press Enter.
You can see that Excel translates that
into a series of TRUE and FALSE values.
Now I'm gonna grab the second criteria here
which is Salary greater than 120,000.
I'm gonna paste that one here, add an equal sign,
and I get a second series of trues and falses.
Now, what's important to keep in mind is that
when Excel compares or performs mathematical operations
against two arrays like this, trues get treated as a 1,
falses get treated as a 0.
And if you actually want to see that conversion
here in the cells, you can use something
called a double unary, which is two dashes,
and that's gonna convert the trues to ones,
the falses to zeros.
We do the same thing here in column G.
And now I've got numerical representation
of those two logical tests.
So now if we wanna create an and condition,
remember we're multiplying those two tests together,
in this case, F times G, drop it down,
and watch what I'm gonna do here.
I'm just gonna apply a conditional format
to make this very clear.
I'm gonna format any cell that's not 0.
And in this case, scrolling top to bottom,
there's only one non-zero because based on my values
in column F and G, anytime there's a 0,
because we're multiplying, that row will be zeroed out
here in column H.
And in fact, there's only one row in our array
that doesn't get zeroed out.
And that's because there's only one job
that meets both criteria.
It's that Solutions Architect job
that earns just about 123,000.
And look, that's the one job that's shown right here.
If we change this back to 100,000,
we get four jobs returned.
And look, we have four non-zero values
which result from multiplying our criteria together.
So that's the and criteria.
If we change this to an or criteria
by adding the results together and applying this down,
watch what happens.
We're gonna change our dynamic array formula as well.
Now we get...
Let's see how many.
17 results which are jobs that are either a Technology job
or a job that's greater than $100,000
regardless of what Industry it falls in.
And here you can see that by adding the two criteria
together in F and G, now we get many more non-zero results,
because all we need is a 1 in column F or column G
to pass our criteria.
And if we look through, you'll see that we actually do have
those 17 Job Title results,
which are either greater than $100,000 in salary
or they are a Technology job.
So hopefully that's helpful.
A little glimpse under the hood into how Excel
is actually working through and processing
these logical arguments to create that filter criteria.
But hopefully that helps you understand
exactly how the filter function is working
and how you can accommodate different types
of filter criteria.
So let's go ahead and delete these columns that we created,
(computer mouse clicking)
and we are back to square one.
So there you go, dynamic array filter functions.