in this video we're looking at awesome techniques that you can use with excel's Filter function may be the best function inside Excel now make sure you watch to the end because the techniques just keep getting better and better and by the end of this video you're going to be a filter function ninja so if you're ready let's get started let's start by understanding the filter function it has three arguments but the second argument is the most important so let's start there so here in Excel if we type equals and then I'll select my type Cod and I want to check where that is equal to the value in cell I4 I press return and we get trues and falses everywhere where it has the value of true that is where our value in the type column matches with the value in cell I4 and it's the items that return true which will then be returned by the filter function so back to this cell equals filter Open Bracket and we want to filter our entire data set but we only want to return the items where it Returns the value of true then our final argument is what we want to display if there are no values so let's have no values close that bracket press return and you can see it only Returns the items where that item is commercial if we change it to residential our filter function then updates if we change it to something else you can see see we now return no values because nothing matches so that's a quick overview of filter the most important argument is that second argument the true or the false determines which value is returned what about the scenario where we want multiple conditions how can we handle that well it's all about that same true or false value so in here we can type equals we can select our type where it equals commercial we know that that will give us a true or false value so what we need to do is if we want an and condition we can multiply that by another condition so for example where our annual rent is bigger than 5,000 we will also wrap this in Brackets and when we press return we now get ones and zeros and ones and zeros are equivalent to true or false so if we now put this inside the filter function filter Open Bracket our table that's our condition and then we want to have no values close that bracket it now returns where it's commercial and it's more than 5,000 in rent so that's an and condition we can also use an or condition to do that what we do is rather than a multiplication we use a plus symbol and when we press return we then get the value where it's commercial or it's greater than 5,000 so that's how we can do multiple conditions the last argument of the filter function determines what value is returned if there are no values to return here in this example we don't have a final argument so that means that if I change this to something else that we get a hash Cal error so what we can do is provide a value that we want to return instead no values so that just gives us a single value in that column but what if we want to display something across that entire row well instead of a single value we can provide an array so in curly brackets we can provide a value for each column so the First Column will say no values then not applicable not applicable zero and then zero so now that is the value that we see as our result when there are no values to return unfortunately the filter function doesn't allow Wild Card characters but we can still search on a partial t text string here's how equals search Open Bracket the first argument of search is the text to find we want to find the road from I4 and we want to find that within our property column we'll close that bracket and press return now where it says 10 that means the word Road starts at the 10th position where it doesn't find the word Road it returns an error so what we do is that we wrap this in the is number function and when we use is number it shows true if it contains the word Road or false if it doesn't so if we then add our filter function so we want to filter our entire table based on that partial text match and then at the end we want no values close that bracket press return and you can see that we now have it where it contains the word road if we change that to Street it works if we change it to Avenue you can see we get no values returned so that is a partial text match using this concept of true and false we can filter based on another list so for example equals count ifs Open Bracket and we want to count our list and we want to do that on our property name when we close that bracket and press return it returns one or zero so for example Main Street is in our list so it contains one 23 Park Lane is not in the list so it returns zero well this is equivalent to a true or false result so we can add filter on our entire data set counting the items which are in that list then at the end no values close up bracket press return and we now just get the items which are contained in that list if we add another item to the list so 23 Park Lane that now appears in that data set too so that's how we can filter base on a list if we add slices to a table and use the right formulas we can work out which items have been selected by that slicer so let's go and see how we can do this I have a cell selected inside my table I'm going to go to insert and select slicer I want a slicer for the property and also the type column and then I'll click okay so now when we click on these slices it will reveal various items inside our table but what we want to do is to capture the items that have been selected by those slicers with a true or false value to do that we can use the by row function Open Bracket and we want the by row of our property column for example and then we're going to use a Lambda function and we want to use R so this means that R represents each row within that column and then we're going to use the subtotal function now subtotal will return values if those cells are visible so if we use count a as the function it means it will count the number of cells that happen to be visible and we want that on a row by row basis so if a row is visible it will count as one if a row is not visible it will count as zero so we'll close our subtotal we'll close the Lambda and we'll close the by row we'll press return all of our rows are currently visible so they display one if I click residential you can see that we have ones and zeros well these are just true and false results aren't they so now we can use our filter function so filter open bracket on our table and we want no values so so now we can use our slices to reveal which items we want inside our filter function now this works best when our table and our function are on different tabs but this example here shows us how we can use a slicer with the filter function hi there I'm Mark from Excel Off the Grid and we're on a mission to help people automate Excel because you shouldn't have to work late just because you have rubbish systems at work so let us help you to spend less time at work and spend more time doing what you head over to Excel ofthe grid.com and check out our training courses and that's where we'll show you how you can automate Excel the filter function reduces the result down to the selected items but sometimes we want to provide the user the ability to select all the items so how can we do that well we can do it with the sequence function equal sequence now we want the rows and we want the number of rows from our table so we'll select any column and we want one column starting at one and stepping by zero we'll close that bracket and press return so we now have a one for every single Row in our table which means because 1 equals true we can say that if Open Bracket I4 is equal to an asterisk display that sequence function otherwise we want the value where the type is equal to I4 we'll close that bracket and press return so now if we change this to commercial we get a true or false if we have an asterisk we get a one so that means we can wrap this in the filter function for our entire table and then at the end we want no values so now if we have an asterisk it displays all the items or if we have a specific value it then filters by that value so that's how we can have a filter all option the filter function returns all the columns from the array used in the first argument but what if we want to select specific columns well for that we can use the choose calls function here I have a filter function and we're going to choose so that we have columns one which is property five which is annual rent and type which is column three so we use choose calls Open Bracket so we're going to use example eight that's the name of our table and then we want column 1 5 and three we close that bracket we press return and now we only return the property the annual rent and the type now choose calls has two formats we can have this format here where we separate each column with a comma or we can provide all of those columns in an array so 1 comma 5 comma 3 and that's placed within curly brackets and that gives us exactly the same result so that's how we can return specific columns but also change the order of those columns rather than us selecting which columns should be displayed let's say we want a user to decide which columns they want to see how can we do that well we can do it with the match function equals match Open Bracket I'll just move the tool tip and we want to match our column headers from our table headers and we want an exact match so I'll close that bracket and press return and it now calculates 1 5 and three the first Fifth and Third columns and that is returned as an array so we can take that formula I'll copy that and then we come back to our filter function and rather than our hardcoded array we can paste in our match function press return and now because these are data validation lists we can select any column that we like and that will return that value into that column using the filter function we can create Dynamic dependent data validation lists in Excel I've got a data validation list that displays commercial or residential and what we want is in cell I5 to display a data validation list of those matching items how can we achieve that so over here I'm going to type equals filter and we want to return the list of properties where the type is equal to the value in cell I4 and let's return no values close that bracket and press return so now when we change our data validation list it changes that filter in that list so all we need to do is to select our cell from the data ribbon go to data validation change this to list then select our cell so 04 hash click okay and we now have a dependent data validation list so residential only shows the Residential Properties and when we select commercial it only shows the commercial properties so that's how we can create a dynamic dependent data validation list and that's it we've looked at 10 examples of the filter function and you are now a filter function ninja so if you want to see our future videos click there to subscribe and click there for more Excel goodness thanks for watching I'll catch you next time