 Hello everyone and welcome back to another video and this is probably going to be a quick and to the point video But just want to get one out this week and show you this really useful Another use shall I say of using the filter function in office 365 if you haven't already Please subscribe to the channel and if you do enjoy this video, please do hit that Like button only be appreciated by me But as always it will help that a YouTube algorithm and help more people find this channel and the content So to jump straight into today's Example we're going to be working with salaries and Probably just worth mentioning at this point all the information that's being used in this tutorial is Randomly generated information. So anything that might seem like it's actual real data or there's any Coincidental names are going to show you it's complete flu gets randomly generated data What we're going to do is we're going to be using a filter function again quite a popular function on the channel at the moment and we'll be using it to pull out the Top five largest salaries for the list of information we've got here and Also, we'll be using it to then Obviously display more than just the salary want to find out the department the name of the person and obviously what that salary is And the reason for doing this is it just gives a really dynamic Use of Excel you could use this maybe in a dashboard or even just useful to use when in a meeting or Presenting to someone such as a stakeholder who would want about to dynamically jump between different scenarios of this information So to jump straight in if you haven't seen how to use a filter function before you can find videos going more depth on our channel Else I'll you'll probably get a good understanding of it in this for today's video But I'll be trying to keep it as brief as possible because there is already a video on it So the first thing we want to do is use our filter criteria or use our filter function to give us just a Filtered list of all this name all this information over here, but it's only the top five salaries So in order to do that, we're going to go equals filter Open our brackets and this information on the left here these three columns This is already formatted into a table if I click in well if I click into here before doing that You can see we get our design design tab and you can see the table name is the salary table that I've named it Going back to our filter function if I do equals filter Open brackets and I now need to select the array because it's the table I just need to select the whole table and as we know if we go to the top corner here We get this arrow and we can select the whole table like so The next part is what is going to be our filter or what are we going to apply our filter to for us? It's going to be yearly salary So if we hover above yearly salary, we can select the whole column like this and start with all we want to do is say Okay, we only want to look at the largest Five salaries. So we're going to use our large function for this. So go large open brackets cool, and so our array for that is going to be again yearly salary and It is the fifth largest and I think I just skimmed through that. So let's just go quickly back So the first part is we're going to be filtering our salary table. The next part is we want to apply criteria to the salary column. So that's what we've selected here and Extending from that we're going to use a large to get our fifth largest salary so in all essence this whole part of highlighted here is basically just saying filter This column to only pull out values that are the fifth largest salary if I hit enter Oh, we need another little closed brackets at the end here and There we go, we can see our information has expand expanded for us and it would seem Yeah, and I nearly fooled myself in you can see what's happened here is we've got only two pieces of information And that's because what it's done is it said, okay? Find the fifth largest salary in that range and the fifth largest is One that 125 thousand we actually want to change this because we don't want the fifth largest We want everything. What's the fifth or greater? So the top five really So all we need to do is go back to our formula here and just change this so rather than being equal to the fifth largest We want it to be greater than or equal to the fifth largest So if you enter now you can see how that has now expanded and we've got a range of salaries So from one hundred forty thousand All the way to 125,000 so we've got a full range in there now Let's just change the formatting of this So let's go in here and let's just say yep change that to that maybe just expand that that Expand that down further as you'll see So the benefit of obviously using the formula as we've done And we obviously looking at this part here is if I was a person looking at this and we wanted to be dynamic And I said well actually I'm interested in seeing the top ten All they need to do is go into ten and you can see now what ten the top ten salaries Rather than just the top five and obviously it's a dynamic formula. So it's going to keep changing as we go through this What we want to do now is just apply a bit of sorting So at the moment we can see it's just all all over the place ready We don't know what the largest salary is unless we really look into the data So in order to do so I'm just going to get back into this function again I'm going to enter our sort function at the very beginning and we've got all that as our array and then right at the end here in my Sort index I need to firstly put in here column three So just index number is going to be three and it's because we're going to be filtering or sorting on the third column within our range Do comma again, and then the sort order for us is going to be descending. So you want to have The descending from so high salary down to the lowest salary shall say within obviously the criteria of our top ten in this particular example When I close the brackets hit enter you can see it's easiest that the salary is now ordered from in descending order And this is particularly beneficial and I was actually going to then talk about having duplicate values in here But I've already got one coincidentally You can see for sales and human resources again the benefit is we've got two Individuals here who are on the same salary So this is the benefit of obviously using this formula if we're using say just large on its own Then obviously this is where you'd have a problem where I'd only find the first Matching criteria in a list rather than pulling out the multiple examples as you're seeing here We're going to take this one step further and just do add some array con or use Should I say our array constants in the sort function in order to get a bit more organization with the department name So what we're going to do is want to organize a bit more So we want to have it by Department being filtered on here as well To do that we just need to go and use that say array constants and to do that we use our squiggly little brackets So you can see here So the first thing I want to do is to still sort by the salary column first And then the next thing I want to do is to sort by the department So all I need to do for that is add another comma and this time make a reference to the index what contains the department So it's for us. It's number one for column number one and Then when it comes to our ordering we're going to keep it so that our salary is in descending order first and Then the next part we want to do if we do the right bracket there you go We go into there. So this time I'm now going to reference Column number Well, no not column number one. Sorry. So the first part just to make this clear So you can see we've got three comma one in this first part That's because we're going to be filtering by column number three in essence first followed by column number one And this time when we're talking about order, we want descending for our salary What's column number three and I now want ascending for the department So I'm just going to put a number one like so and just add my squiggly lines back in there Hit enter and you can now see how that has been formatted or not formatted There's been changed for us as well this way We just make sure that we keep everything in a bit more presentable form And you could obviously extend your sorting and your ordering as much or as little as required And just to go back through that again, we can see everything is still nicely dynamic So if you want to don't look at five or three all that information is there for us The last thing was worth mentioning is our data is obviously limited We've only got so many rows and actually we've got in total Yeah, 34 rows of information 34 names in our table So what happens if someone says they want to see this the top 40 salaries? Obviously at the moment we're going to get the hashtag num error because obviously it's entering we're entering a value here What's in excess of actual the data we have? So in order to deal with this all we're going to do here is at the beginning of our formula We're going to enter if error Open our brackets and I write the end here. We're going to do Please enter a Value Or obviously you put whatever is more suitable as you're required So if we now go in here we put 34 You know, we can see the all 34 salaries in descending order if we went into top five We can see top five and if we went and put in here some silly number That's obviously not available We're now going to have a nice bit of text there rather than just showing that error message And also the benefit of having text here as well as it shows the use of what they need to do To rectify that error because often whenever a user will see an error They might not always know what it means so they'll think something's broken But obviously this is the benefit of putting the text there and obviously because it is just a free add text to what we require You can just put whatever information you need there when you feel as useful to the user So I think that was actually quite a quick whistle-stop tour video I tried to keep it short, but as always I've probably kept it longer than I intended If you do have any questions do please just leave a comment below this video Or you can reach out to me in either the Facebook or Instagram page For this channel if you did enjoy the video, please as mentioned before do give the video a like It only shows me the sort of content you'd like to see more of But it also as always helps that YouTube algorithm to help more people find the channel And lastly if it's the first time to the channel or you're a peer viewer Please do subscribe to the channel and hit that bell notification button So that you are notified of all of our future videos as they come out So thank you very much for watching and we'll see you in the next video I always keep forgetting to mention this but if you go into the description for this video below You'll find a link that will allow you to download this very workbook that I've been using in today's tutorial So I hope that will be useful for you as well if you're working through this 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