 Hello everyone and welcome back to another tutorial in this video We're going to be looking at the new Excel filter function that we have available to us The real benefit of using this and I've wanted as I'm going to say it surpasses the V lookup But it doesn't but to give an example with women using the V lookup function obviously our purpose of using that is we have a particular Value that or variable that we want to search for in a table and return another piece of information So an example of that could be As you see an example got in the table if we wanted to see okay What was the expenses for London? We could obviously have our value of London and we might want to V lookup this range Just these four cells in this poor example to return obviously what the expense number was But V lookup as we know only will only return one Value or one piece of information But what if as per our example we're going to be looking at we want to return every example that matches our criteria So that being again sticking with London. We can see that we wouldn't want to just return In the scenario of wanting to return multiple All those that match sorry In our example of London we can see that we've got a match for the finance one for human resources and one for sales So you can see there's actually three pieces of information that we want to return and that is where the filter function Enables us to do just that so what we're going to do is just drop down dive straight in really We've got the since the syntax at the top here so we can see what this filter function looks like Very simply we need to do is enter the word filter and then we've got three parts to enter Firstly we'll look at the first two just the most important the array and the include and also it gives us a bit of error Handling could say by giving an option what to do if empty, but I'll get to that in a second So all we need to do first thing with staff by putting a drop down in the city You could just type in our values or our cities as required But we want to obviously be very you know accurate and make sure that there's going to be no mistake with the encrypt Spelling or a wrong city entered so all we're going to do here is go into Do a quick drop down so let's just go and do data data validation And we'll do a quick list Our source is going to be these first five values here because that is the total of our cities do okay You can see we're now got a drop down in there. Well, it's perfect So now to enter our formula, so we're going to go down drop down one below Where we've obviously got the city added there with our drop down and all we need to type into here is equals filter Open our brackets and then we dive straight in so the first part is our array So we just want to select the entirety of our table So that's as we selected here So we've got the department the city and the expenses columns all included The next part is our include so what is what do we basically want to filter? So for that all we need to do is select the city column because we want to filter the city column for only the city We're interested in and we're then going to do the equal symbol and we're then going to select the The reference here of g8 for us. What is where we've got our drop down for city and having done that It's going to do brackets and here enter and you can see initially It's given us a calc error because obviously we haven't provided the city But soon as we select a city so go for London you can see that the formula actually expands out for us So you can see rather than just being in this one cell. It's actually Done exactly that it's expanded and put it in as many spaces as required So to give another example if we were to try and change Cambridge over here Let's say that's London as well. You can see that it's picked up as an additional one as well So we'll add it in there, but I'm just going to change this back to Cambridge And you can see it's got back to our three The formatting will not carry across so all we just need to do is make sure that We do copy that across for as many rows as we require So I'm just going to literally just do that whole column just depending on how much data we pull across But there you go. That is the the formula basically in this entirety If I do just remove this piece of data validation. So let's go to data and let's go validation And just go clear all Okay, so if I was to put in here a city that doesn't exist in our list So I think I'll get an example. I thought it was Bristol. So go to Bristol and enter You can see we get that calc error and we'll get exactly the same if this is left blank So how do we deal with that? Well the benefit we have is the third and final piece of our syntax We can see is this optional one of if empty So what that means is if you've done the filter as required, but there's actually no matching variables What do we want to return and simply for us or we're going to do that third part is in quotes put Not found so you could put anything you want in there You could maybe even reference another value through a formula do anything that meets your particular criteria But for us we're going to put in there the text of not found We're going to enter you can see that not found so basically there's a blank here So it's obviously not going to find that if I was to put a Bristol so incorrect city or one that's not in our list Again, it's going to get not found, but if I was to instead put a value we know exists So what kind of there you can see that we've we've now returned those values correctly as required So I think that comes off quite well how to use the filter function And as mentioned you don't need to be limited to just three examples We've got here. You can obviously do as many as you need The next example that I have I'm going to scroll down to is exactly the same table But allows me just to elaborate on just a couple of maybe a better way of working What I've done here is if I go into the data I've converted this into a table and you can see that where I forget the home tab here You can see although you can't see it as one and I'm going to jump ahead to get something I didn't want to do so if you're not familiar with what you can see the table design tab actually popped up there If I click away and go into our data You can see we now have a table design and you can see I've named this table expensive If you're not sure how to insert a table all you need to do is just select all of your data Simply go into the insert tab and obviously I can't at the moment But if I go to this above example Honour all that data insert tab go to table you'll get this pop up Just make sure you've got the my table has headers ticked as long as you obviously selected the headers hit ok And then it'll come create it as a table for you by default. It'll do it's default blue Formatting on the cells But we need to do is just go into the formatting options that you can find if you go into table options The table styles so I think by default it'll do maybe this blue one or something similar to it But I've just gone on to the no format just I just prefer the format for this example But that's how they get a table in there if you haven't done already So for this example rather than obviously referencing an actual range as we did up here and just to remind you obviously You can see we reference that whole range there All we now need to do is just go into Do another filter function and sorry the port the reason I put a table in there is to show you that in this filter function We can actually reference a table rather than just a given array So for us how we need to do that now is I'm just going to put in here another city. So let's go to Edinburgh And what I could do as well is just change this and put back our piece of data validation So we've got a drop-down because it's just again better ways of working. It's good way just think to always remember Okay, so you can see we've got those drops downs now available to us So in this time, I'm going to go into equals filter and over our function go into our variables So this time for the array rather than select this range When you've got a table in there all you need to do is if you hover over the top left hand corner Just yeah, literally the top left hand corner of the first cell that contains your head You can see you get this this dark Slightly obviously different angled arrow Once you get that if you just click you can see it will select the whole table for you And you can see it's done exactly just that and populated it into our function for us So we've just got the word expenses. What is the table reference? If I now do a comma to move into the include section So again for this because we want to do a filter on the city column We don't need to select that range All we need to do is hover over city and you can see that you get this other side down It's down with our own app the thicker one. So if I just select that you can see it's gonna say okay The expenses table in brackets city column and this time always then carry on doing to our equals Select our drop down. So when obviously the city column is equal to our drop down of Cambridge in this example And then because we know how it works We're gonna do comma comma and in a third and far apart We're gonna put them up found if we see there is a value that's entered that is not present in our table All terms for the effect drop down is left blank It's blank, hit enter and then you can see obviously we've got our values as you would expect Let's just copy the formatting of this down So let's go into this one here that matters Copy that down for the whole length Perfect. So yeah, so you see if I obviously clear the drop down. It's gonna give not found But let's go back into another one. Let's go bombing him so you can see it's pulled all those values through for us The real benefit of obviously using this table is it allows us to Stay dynamic So with this first example if we wanted to start adding more pieces of information at the bottom here So if I was to do another one for Cardiff, so let's think of another department IT Cardiff And let's say we've got revenue of 10,000 copy the formatting You can see that nothing's happened because in order for me to Or in order for this new piece of information to filter into this The format up here. We'd first need to go into the filter And then we need to drag down all of these ranges to make sure it's looking at that new piece of information And once you've done that it will filter through but obviously it allows for human error So obviously not update the formula and also it's just a bit clunky and it's just not giving us the dynamic approach We want there's just one thing we don't need to worry about or be thinking about when we're adding new data The benefit of obviously adding a table is as soon as new information is added So if I do IT and this example is Birmingham You can see that it's been added in there straight away and that's the benefit of using the table and why I'd always insist using a table if you can when you're doing such references Just to ensure that your data is made being kept up to date Another example is particularly useful again for if you're popping this formula in and then the worksheet is being shared with a Collie or someone else or another team and they're going to be the ones who are updating it again It just makes it robust so that as they're going to be utilizing the form It just means that they're not going to have to be worrying about things not fooling through or again as always They'll come back to you and say oh, it's not working. It's broken. Well, actually obviously they just haven't updated But by doing the table reference, so I will for that out it just eliminates any sort of potential visual problems So I hope you enjoyed that video and it's gone into enough depth for you to start now using that in your work If you do have any questions, just drop a comment below this video and I'll get back to you as soon as I can If you did enjoy the video, please do give it a like and not only does it show me the content that you're interested in seeing more It will also help that you do algorithm What's all important for making sure that other people can find our content and enjoy the videos as well Lastly, if you haven't already or if you're new to the channel, please do subscribe Make sure you get that bell notification button as well that way you should be notified all of our future videos as they come out So thank you very much for watching and I should see you in the next video Before you go, don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information and obviously as always don't forget to subscribe and hit that bell notification button