 Hello everyone and welcome back to another video. So we've recently posted a short video showing you how to do the filter function as an overview and this video is going to go into a little bit more depth and of course talking you through it as we go. So in the example we have here we've got two columns, we've got a name and also a job title column and of course before we jump into this you can obviously expand this logic to cater to whatever scenario you are currently dealing with. But what we want to do here is use the job title we've got in our second column and split our names out as you can see in this left box. So I want a list of all my managers, all supervisors and then of course all assistants. Now that's what we say boring and manual way you might do this is of course to say okay what job title I want first manager okay let's go down and find manager so we can see we've got Cynthia and then we can also see we've got Nigel and paste them into there as well and what other managers do we have? Oh and we've also got Dean here as well and then just paste them in like so. This obviously works but of course it is quite labor intensive and as soon as you get onto things such as the supervisor or at least the assistant where there are a lot more names it becomes a little less practical to keep doing this copy and paste let alone if your data set is significantly larger than the two columns you see I have here. But however we can do this a lot quicker and cleaner using the Excel filter function. So what we'll do is we'll just jump straight into it and talk you through what we're doing as we do it. So the first thing of course we need to do is our equals as type of word filter and you can see the requirements that we have in our filter function. So for us the first part we need to provide is the list containing the values we wish to filter so which is the list of names that contain my manager names. So for me I'm going to simply click and drag to select this range here so you can see all of my names from Jesse down to Dylan and what I'm going to do is also hit F4 and you can see upon hitting F4 it's given these dollar symbols around both the column and the cell row or yeah it is the cell row sorry of our cell references and what this does it just means that as we drag this formula along horizontally to supervise your assistant later on it's always going to be still referencing this range. If you don't do this F4 selection then as you drag your formula it's going to be looking at a different range which is not applicable to the logic we're going to be providing. So this is our first piece of information which is the list or what is the list of names we wish to filter comma and now we need to provide our criteria so we want to filter this list of names where everything in this second range which I'm now selecting and once again doing F4 to make sure that we are locked on this range so we want to filter all of our names where the job title is equal to the value in the header of that list so if we now do close brackets we can see and again I'm sorry I'm repeating myself now but yeah we can see we're going to filter all of our names where the job title is equal to v6 which for us is manager and once we can enter you can see it's now given us our four managers in that column now rather than having to retype this formula every time you want to use it so it also typing it in for supervisor and assistant all we're going to simply do is go to our symphia so because that's the first value in the range as you can see if you go to another or the second value it's grayed out so it obviously it's not going to work but what we want to do is click our first range or first salary and then using this little icon in the bottom right corner click and drag that across and you can see it's now populated with all our other names and what we'll just do is this little box popped up is go fill without formatting just so we don't mess up any of our borders that we had now the great benefit of doing this is everything is now going to remain dynamic so it should someone get promoted so let's say Quentin who's currently an assistant let's put him as a supervisor and hit enter you can see how that's now automatically updated here as well it's worth noting that as we looked at we referenced our job title based on where it currently sits so currently in v6 however if you don't want to make a reference and you just want to type in the title here or type in the job title you could simply put manager in quotations like so and the same for supervisor and assistant if you didn't have this reference at the top here and just to prove it's working if i delete manager you can see it's still been retained in there so if this is the first time you've watched one of our videos and stumbled across our channel or you've watched our videos in the past and you've still yet to subscribe please could i ask you to hit that subscribe button and also that bell notification button so that you are notified of all of our videos as they come out in the future and lastly if you did enjoy this video could i please also ask you to hit that like button not only is it greatly appreciated by me because it helps me to see the content that you'd like to see more of but it does help that all-important youtube algorithm so once again thank you very much for watching and we'll see you in the next video