Master the FILTER Formula in Excel (Beginner to Pro)

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 and we want to find out those that are in the country of France and 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 and going to home sort and filter and clicking on filter so now for example we could F 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 is well what are we looking for here we're looking for the countries so we'll select the whole country column contrl shift down arrow to get to the bottom there and then we want this two equals to the country of France so we'll put equals and select fronts as we have it right here close up parenthesis and hit enter and now you can see that we have all of

those that are in the country of France and 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 madeup 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 much close the quotations and hit enter And now when the country is X or Y which is obviously not a country

it simply says no much 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 country one industry so what we'll do

is we'll put them in parenthesis and the first one is going to say that the country column so this one right here is equals to the country of the United States close the parenthesis and 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 equals 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 then that means it matches both conditions so both are true meaning it is a country that that's the us and it is an industry that's technology 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 they stable 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 and right in front of the filter function we'll use the V stock 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 and now you can see 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 up further with another formula which is the choose calls hit the top key there and then at the very end we're just going to put a comma key and as column number one we want their name which from our tables point of

view it's 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 powerbi 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 so control shift down comma and as the include or the filtering tool well we want to filter by the countries and we need those to be equals 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 so you can see we have Elon Musk who's us Jeff Bezos and 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 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 and this gets a bit tricky because they don't just have the word family they also have Bernard Arno in front for instance or Carlos Slim in front which makes it a bit difficult to search for and we need to use some kind of approximate search instead of an exact match that's where first we'll use the search function and what do we want to find as the text well we want to find the family comma and we want to find

the family within all of this area right here so we'll select all of the names close up parenthesis and hit enter and 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 AR no 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 is number 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 a parenthesis and hit enter now that's looking quite good this person is true because that's berau then we've got this one right here as true cuz that's Carlos Slim and so forth and 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 and 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 and right now I have family but I could also just have Ma so those that have the word ma in their names we have Amanti Orga and

Mark zucken Berg 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