Transcription
When used correctly, the filter function is one of the most powerful functions in Excel. So let me show you how to use it with four examples, from easy to hard, plus one final bonus example you might find surprising.
First up in level one, we have this table over here that has the list of all of the billionaires up until number 30. We want to find out those that are in the country of France. To follow along, you can download this exact same Excel file in the video description.
For this, you might just think of using the filter tool. So heading up to the table here, under the header, go to Home, Sort and Filter, and click on Filter.
Now, for example, we could filter for the country of France. Only the problem here is that once you do the filter, it's no longer dynamic. You need to redo the whole filter again, and secondly, we can't see all of the original table values. That's why the filter function is usually a better option for this.
Here, we would just type equals filter, hit the up key, and the array is essentially the whole table for us. So we just need to select it: Control + Shift + Down, Control + Shift + Right. Let me scroll back up here.
Comma, and under include, well, what are we looking for here? We're looking for the countries, so we'll select the whole country column: Control + Shift + Down arrow to get to the bottom there.
Then we want this to equal the country of France, so we'll put equals and select France as we have it right here. Close up parenthesis and hit enter.
Now you can see that we have all of those that are in the country of France. Because we've kept this dynamic, we can change the country here to Spain, and you can see everything changes as well.
One part that we didn't quite include is if we put a made-up country like X, you'll notice that we get an error. But there is a way to work around this, and that's the if empty part. Well, if it's empty, we can put in quotations "no match," close the quotations, and hit enter.
Now, when the country is X or Y, which is obviously not a country, it simply says "no match."
Moving on to level two, and it's going to get progressively harder. Here we have two criteria. As you can see over here, we have the country of the US, but we also want to filter by those that are in the tech industry.
So like before, we'll go to equals filter, hit the top key there. The array is the whole table for us, so Control + Shift + Down, Control + Shift + Right. Now we have it all selected.
Comma, and as the include, we need two conditions: one for country and one for industry. So what we'll do is we'll put them in parenthesis. The first one is going to say that the country column, so this one right here, is equal to the country of the United States. Close the parenthesis.
Then we'll put an asterisk, and after that, we want the second condition, which is that it has to be in the industry. So we'll select all of this, and we have to make that equal to the technology industry. Close up parenthesis, and now close it again, this time for the filter, and hit enter.
So now we have those that are from the United States but only in technology. We see Elon Musk, for example, here under Automotive. He's no longer in there because he only meets one of the two criteria.
To explain how this function works further, if we only select this part right here, you'll notice that we'll get a true/false on it. It's essentially saying if it's true, then it's going to put a one, and if it's false, it's going to give us zero.
So if we multiply that side by this other side as well, which also has ones and zeros, then whenever it's a one on each side, that means it matches both conditions. So both are true, meaning it is a country that's the US, and it is an industry that's technology.
This disaster risk is essentially equivalent to an AND function, but there's also an OR function. So what if we want it to be the US or in technology? It could be in a different country, though.
Well, for that, instead of an asterisk, we're just going to put a plus sign. That means that if it's a one on this side or a one on that side, they're both going to count.
So you can see the list is now longer. We have people like Elon Musk who are not in the technology industry as of this table, but they're still in the United States, so they start to fill in as well.
Moving up to level three, and now it's going to get more interesting as we'll be combining different functions.
So over here, we have the same one as before with the filter, where we did the country of the US and the tech industry, and we have the asterisk right now, so both conditions have to be true.
That said, we don't really have a header at all, which is somewhat annoying. So let's try to see how we can add one. Right in front of the filter function, we'll use the VSTACK function, which is one of the newer ones in Excel.
In here, we're going to select all of the header columns, so all of these right here from rank to worth. We'll hit a comma there, then we'll add all of the filter function, and finally close it at the end with a parenthesis.
Now you can see we have the exact same data, but we also get the headers up top. That said, what if we don't want to see all of the columns? As you can see over here, we have all five columns, but what if we only want to see the name, the country, and their worth?
Well, we can customize it further with another formula, which is the CHOOSECOLS function. Hit the top key there, and then at the very end, we're just going to put a comma key.
As column number one, we want their name, which from our table's point of view is number two, comma. Country is number three, comma, and then their worth is number five. We'll close the parenthesis and hit enter.
Now you can see it's filtered even further to only have three columns.
Before we move on to level four, if you're finding this a bit too fast but you still want to level up your Excel skills, I definitely recommend you check out our Excel for Business and Finance course.
There, we'll go over all of the essentials step by step, ranging from formatting best practices and shortcuts to building awesome visual dashboards, creating large dynamic financial models, and much more.
This is basically the course I wish I had before I started to work at an Excel-heavy corporate job. If all of that sounds interesting, check out the link in the description below.
And if you want more than just Excel, we also offer a ton of other courses in SQL, Power BI, Fin Accounting, and much more.
All right, back to the video. Moving to level four, and here we're going to get a bit more creative.
So suppose we want to find out, out of the American billionaires, what's their average net worth. For this, we'll first use a filter function, so equals filter, but we don't want all the columns anymore. We really only want the net worth one.
So what we'll do is select that, as that's the one we want as our answer: Control + Shift + Down, comma. And as the include or the filtering tool, we want to filter by the countries, and we need those to be equal to the United States, which we have here.
We'll close the parenthesis and hit enter. So what this gives us is all of the values here that are in the US. You can see we have Elon Musk, Jeff Bezos, Larry Ellison, and so forth.
The problem is this doesn't quite give us the average, and we could make an average function over to the side, but that doesn't look that neat. Instead, what we can do is just go back inside of the filter function, and at the very beginning, I'm going to put the AVERAGE function, open up the parenthesis, and at the end here, we're just going to close it and hit enter.
So you can see the average net worth is 81 billion for these American billionaires. In this case, we've used the average function, but this can easily be changed as well, just by going in here and let's say going for the SUM, or we could even change it to the COUNT, and you can see that we have 17 billion that are American out of this list of 30.
Finally, looking at the bonus feature, which I think might surprise many of you. So let's take a look over here, and you might have noticed under the names that a lot of them have the word "family" in them.
So suppose we want to filter by those that have the word "family" included. This gets a bit tricky because they don't just have the word "family." They also have Bernard Arnault in front, for instance, or Carlos Slim in front, which makes it a bit difficult to search for.
We need to use some kind of approximate search instead of an exact match. That's where first we'll use the SEARCH function. What do we want to find as the text? Well, we want to find the word "family," comma, and we want to find the word "family" within all of this area right here.
So we'll select all of the names, close up parenthesis, and hit enter. You'll get a ton of errors like this, but don't worry. What we need to know is that the ones that have a value, like Bernard Arnault here, which is the second one, have the word "family" in them.
Same thing down lower here. This is for Carlos Slim; he has the word "family." That's all looking correct.
The next step for us to filter it is to be able to write a true or a false. So we'll select this area again, and we're going to put the ISNUMBER function up front.
So whenever there is a number, like this one that has "family" in it, then it's going to return true. If not, it's going to return false. We'll close the parenthesis and hit enter.
Now that's looking quite good. This person is true because that's Bernard Arnault. Then we've got this one right here as true because that's Carlos Slim, and so forth.
Really, the next step for us is just using the filter function up front. We'll hit the filter there, and the array is the whole area, so the entire table. We'll just go ahead and select that, comma, and as the include, we're happy with everything that's already in here.
We just need to close this parenthesis and hit enter. Right now, you can see that we have all of the people that have the word "family" in their name included.
So this is what's known as an approximate search. Right now, I have "family," but I could also just have "ma." So those that have the word "ma" in their names, we have Amancio Ortega and Mark Zuckerberg.
I can even change this to a full name like "Warren," and we're just going to get Warren Buffett in there. So you can see how flexible this is when we use an approximate match with other functions alongside the filter function.
Another super underrated feature in Excel is conditional formatting, especially when done right, which you can learn how to do in this video over here or by taking our Excel course over here.
Hit that like and that subscribe, and I'll catch you in the next one.