I don’t use filters in Excel anymore. I use this other function instead 😎

Hey Chantu, did you hear about this amazing filter? Yeah man, I just used it to make some tea. Not that one you idiot, I'm talking about Excel filter. So you mean like the ones on Instagram? No man, I'm talking about the amazing Excel filter function. Come on, I'll show it to you. What? There is a filter now? I want to see it. So filter is the new Excel function introduced in Excel 365. Let's understand this at a very basic level first. Here I have got some data. This is the employee data in a table named staff. I also have another data set here that is having the sales data. We will switch between these two data sets to understand the power of filter. Let's start by first extracting a filtered list of all our female employees here. So we could use the filter function like this. The first input for filter would be the data set that you want to filter. So in this case, our staff data. Then the second input for filter is the criteria. So here we want to filter anybody who have gender is equal to female. So we will say staff gender is equal to and then within double quotes, this is because gender is the text value. So it needs to be in double quotes female and then close the bracket. When you press enter, you will see the magic happen. Excel will filter a subset of the data that is here that is matching the gender as female. Notice that it will only return the data portion. It will not return the header of the table and it will not also apply any formatting. As filter outputs a big range instead of a single cell, Excel also highlights that entire range in a blue colored box to indicate to you that this is how much the data has been returned. That blue color highlighting is only for your convenience. It will not appear when you click away. But when you select any cell inside the blue box around, it shows you that that is the range in which the filter results set. This is a dynamic formula. So what happens is if any of your employees data changes, then this will automatically change. So for example, Hussain Agar here, we have wrongly coded them as female. So let's turn that gender to male and then you see that instantly that row disappears from this data and this box shrinks. If you undo the step, it will again print Hussain Agar's results here. Let's look at a few more examples of how to use the filter. Let us say you are not really keen on printing the entire table. You just want to print the female staff names alone. So we will write names here and then here. I just want to print all our female employee names. So this time we will say filter instead of the entire table. We want to filter the staff table name column alone based on the condition staff gender is equal to female. This will also work and this will now return just the names of those female employees. Let's take it one step further and link the criteria of gender is equal to female to an input cell. So we will say gender to filter here and then I'll just type female for now. This will be our input cell. Now let's go and link up this input value to the formula here. So here we are saying staff gender is equal to double quotes female. I'll remove all of that and then simply say staff gender is equal to and then point to E5 and then close the bracket. When you press enter, it will still give you the same values, but now it is even more dynamic. If I change this, it will filter the male employees as well. So I can just say male here and then I'll get all my male staff results. Let us say as you filter the values, you would like to print the count of how many values have returned here. So I just want to count how much is the size of that blue box. This is how you can do it. You can simply say count because these are text values. We should be using count A and then what do we want to count? We want to count the blue box values, but because the blue box can kind of be from here to here, but it can be even more cells depending on how many values are returned. So we don't know how big that would be, but we do know that the results begin from C5. So we'll simply say count a C5 and then type hash. This is a special operator that is introduced in Excel 365 and you can use the hash or the spill operator to capture the entire range returned by dynamic formulas such as filter. So this will give you the answer as eight. How would we do the end condition? Now that you understood the basic concept of filter, let's go ahead and see some advanced examples of how to use the filter formula. Let's understand how to specify an end condition. I want to filter all the male employees in the website department. So this is how you can do it. Filter staff, staff department is equal to website. Since we need to also consider the gender condition, we will go to the front of this condition here, open a bracket, close this bracket, multiply this with the next condition staff gender is equal to male. This is how you can specify multiple conditions for end operation. The star is the special operator that you can use. And when you specify it like this staff department is equal to website, multiply that with staff gender is equal to male. You now only get all the male employees within the website department. Let's try this again. But this time I want to see all the staff in the sales department that have more than 90,000 salary. If you look at the data, there is only two sales employees. So we really expect to see Raffalita's data alone because she has 109,000 salary and Gigi has 74,000 salary. Let us parameterize this by having some input cells as well. So we'll write the filter condition like this filter staff, open bracket, staff department is equal to this one. Multiply that with staff salary greater than that one. And when you press enter, you will just get Raffalita's detail as we expect. But because this is parameter, I can also look at the website people. There's two of them, Denison and Kane. You might be wondering, this is all good Chandu, but wouldn't it be cool if I can actually print the heading of the table as well on the top? You can do that. All you have to do is equal to, and then just select the entire table header. A shortcut for that is table name within square brackets, hash headers. When you do this, Excel will get the table headers and it will, because the table header has multiple values, it will spill them across on the screen. Okay, that's pretty good. But what about the R condition? Let us now look at how to do the same, but with R condition. That means I would like to get anybody who is either in website department or having a salary of more than 90,000. The filter formula for this would be filter staff, staff department is equal to website. But because we want to do an R condition, again, we'll do the same. Open a bracket in the front, close this first condition. For the second condition to be included as an R criteria, you have to use the plus operator and then open the next bracket, staff salary greater than 90,000. And then when you press enter, you will get a bigger set of people. You can see that all the website people are included and anyone else who have salary more than 90,000 are also included. Again, because these are parameters, we can change any of this. So for example, I can change this to 80,000, I can change this to sales and our data output will change accordingly. Let us test the R condition for these criteria. We would like to look at anybody who joined after this particular date or who have a salary less than 65,000. We can use the filter formula like this, filter staff, staff date joined. Technically Excel dates are numbers. So when I say joined after, we simply can use the greater than condition. So greater than that date. And then again, we'll open a bracket, close the bracket plus and then staff salary under 65,000. This will give all such people. One problem with this formula is because the dates are showing up as numbers, we cannot really verify this result. Normally when I use these formulas, I select a range large enough and I apply the date formatting beforehand so that we can verify the results. You could see that we have returned everybody in 2019 and these other people that have joined before 2019 all have salary less than 65,000. I'm pretty sure we cannot do the not condition though. Can we? Let us practice the next set of filter functions on this dataset, which has multiple rows against each person with their sales information. The next condition that we will try is the not condition. Just a quick reminder. This table here is named as sales. I want to print any details where the geography is not USA. We can use the filter function like this filter sales sales geography not equal to, so that's the operator less than greater than USA. This will give every other record except the USA records. Let us say you got your filter formula result here and you don't like it here. You want to move it further down. This is how you can do it. You don't have to select the entire range. You can select the top left cell because that is really where the formula is control X and then place it wherever you want and control V that will move the entire box down or elsewhere wherever you want to copy paste it. So for this example here, what I want to do is I don't really want to print the results. I want to really see what is the total amount where the country is not USA. So how do I add up the total amount? So this is how you can do it. We'll first filter the amount column. So we'll say filter sales amount based on the sales geography not equal to USA. This will give all the amounts where the country is not USA. We don't really want the individual amounts. We just want to add them up. So we now then send this to our good old sum function and that will add up the values. Let's quickly verify this with the SUMIFS formula. We can say SUMIFS sales amount sales geography not equal to USA. You can see that it adds up nicely. Let us apply a complex condition. We would like to figure out what is the total amount and total units where the sale is either from USA or UK but the amount must be greater than $2,000. If you have to do this manually, this is how you would do it. You would go to the geography, select USA and UK and then you would go to the amount, go to number filters greater than and then say $2,000 and then this is the total that we are looking for. The total amounts add up to $124,957 and total units add up to $4,134. Let's clear away those filters and figure out the filter function to get me those answers. So this is how I can write the formula. We'll write the formula for amount first and then do the same for units. So we'll say filter sales amount. The first condition is country needs to be either USA or UK. So we'll open a bracket sales geography is equal to USA. Close the bracket plus sales geography is equal to UK. This criteria here geography is USA plus geography is UK is essentially giving you all the amounts that are either in USA or UK but we need to add the extra condition which is we only want the amounts above $2,000. So we'll put one more big bracket in the front and after and then multiply this with star the third condition which is sales amount greater than 2,000. Close this bracket and close the other bracket. Writing these sort of conditions is somewhat tricky. So if you are a bit confused, I suggest taking it slow. Develop smaller formulas first and then eventually you will be able to understand how to write these kind of things. When you press enter, we will get all the amounts in either USA or UK that are more than $2,000. We can double check the total of this range and then see that it is 124957 indeed. Let's do the same formula for units. We don't have to write the whole thing. Instead, I'm just going to copy this formula control C, paste it here and then just change the initial value to units. We'll get all the units. Now the total of this value will be sum of this number F10 and any dynamic range from there. This would be another sum of G10 hash. The best part of this approach is because it is all dynamic, I can change this. For example, I can look at USA and Canada where the amounts are more than $3,000. I'll get a different result here. Now let's see what happens if there is an error with the filter formula. Let's just say the amount above needs to be 50,000. We know for a fact that there is no amount more than 50,000. So this is how the error message will appear. It will say hash calc error. If you don't like to see the errors, which is the case for most of us, you can also avoid printing that message using the third option of the filter function. So there is an if empty option. We'll simply print double quotes. This will take away the error and just give it a blank value. So what next? I suggest that you spend a little bit of time practicing the filter function. Please download the example workbook and play with both staff data and the sales data. Try to recreate the examples that I have shown. If you need more problems, go to the filter problems tab where I have given you five more problems. Try to solve them. If you end up having a challenge or if you need some help, post a comment on this video so that I can guide you. I wish you all the best with learning the filter formula. So if what my brother showed you impressed you, you got to try the newly introduced XLOOKUP function as well. It is another awesome addition to Excel. Check it out. Here is a video.