Excel basic filters - How to apply basic filters to your data in Excel 2010, Excel 2013, Excel 2016

on this next video I'd like to talk about how to do a filter in Microsoft Excel what a filter is is let's say you have an list of information like this and maybe you only want to see the ones whose office is Bloomington or you only want to see the ones whose destination was Canon or maybe only the orders that are over um uh $300 or maybe in a certain date range or any combination thereof so with a filter we can have one or more criteria let me show you how to set it up first of all it's very helpful that the first row contains your field names and then each row after that uh contains a record or a transaction and each column has a different field so we call this a a table or a list or a database in Excel and those terms can be used interchangeably so all you have to do to start a filter is you're going to click on any cell that's not blank and then under the data menu you're going to pick on a filter these are basic filters and then in another video I'll talk about how to do an advanced filter notice how we have pull Downs next to each field watch how easy this is I'm going to click on the pull down next to office and then uh now in this version we can actually pick more than one uh in each column whereas before I can only pick more one at a time so I'm going to uncheck where it says select all and then I'm going to pick on Adena Hibbing and maybe Brooklyn Center and when I click on okay notice that were showing Adena Brooklyn Center and Hibbing so uh that was able to show those records that we selected now it didn't hide excuse me it didn't delete the rest of the records it hid them so notice how it goes from record one to six the seven to 14 16 17 to 25 so it's not deleting those records it's just hiding those temporarily so all you do is you go to a column and you pick and choose whichever items that you like to see really that easy then you can go to other columns and add more criteria so I'm going to click on that pull down next to destination let's say in the destination I I like to see the ones from Detroit Lincoln and maybe Los Angeles again you can pick one or as many as you wanted to from each column I'm going to click on okay and now the list got a lot smaller now they have to be either Hibbing or Adena or Brooklyn Center in the office column and they also have to be either Detroit or Lincoln or Los Angeles in the destination column so we can keep on doing this and that as much criteria as we wanted to now to start to reverse the fil uh filter we can do a column by column so I'm going to click on that pull down next to destination and you can tell that these two columns have a filter because the pull down looks a little bit different so I'm going to click on that pull down and now we're just going to say clear filter from the destination uh it took the filter away from that column but the filter is still in the office column as we can see let's see how it works with number Fields let's say um I want to see all the items that are over $300 in the ount column so I'm going to click on the pull down next to amount now I can certainly pick a specific amount like we did before but here's something you can try uh with the number of filters we could say less than greater than if I want a range of numbers that would be between top 10 is going to show the highest 10 values above average it'll actually take the average of that column and only show you the ones that are above the average and below average of course is where it would take the average of that column and show you all the items that are below average so let's try greater than and then we're going to type in uh let's say 400 I want to click on okay and now the list got a lot smaller now they still have to be from Adena or Hibbing and then they have to be over $400 as we can see so that's how you can work with numbers in the in the filter very powerful uh in this case I'm just going to say clear filter from the amount what about a date range well let's say uh I only wanted the ones from the first quarter of 1999 so watch watch what you do with that you're going to click on the pull that next to your date field and then we're going to say date filters and look at them all you have uh today tomorrow last week this week so these are built in uh date ranges those could be very helpful uh so if you wanted a date range you would say between and then you would typee in a beginning date and an ending date so I want to type in 1/1 1999 and then 12 well what did I say the the first quarter so let's go with 331 1999 so if you want a range of something you use uh that between and then you put a a beginning part of the range and then an ending part I want to click on okay and if you look now it's only only shown the ones from the first quarter of 1999 and it's still the office is still in effect so we have Adena Brooklyn Setter or Hibbing as we can see so you really can add as much criteria as you wanted to to your to your filter again I'm going to reverse the one for the date and I'll say clear filter from the date and I'll even clear the one for the office and now we should have all of our records back as we can see let me show you another uh basic Bic one in this case I'm going to click on the pull down for amount and then I'm going to go into number filters and there's one that's called top 10 so that'll show the highest 10 values in the entire list for that column so now look what I did this new window comes up so you can either add the top 10 or the bottom 10 the top 10 of course would show the highest 10 values and the bottom 10 of course would show the lowest 10 values so we'll pick on top and you're not just limited to 10 there you can pick as many as you wanted to so let's even go with the top 15 now I have over 200 transactions here if I ask to the top 15 I'm going to click on okay and now it's only showing the uh the rec the 15 records with the highest amounts as we can see so that's another way to do a filter I'm going to click on the pull next to amount we're going to say clear filter filter and then we have all of our records back when you're done with your basic filter you can pick up the data menu and then pick on the filter again that'll act uh as a toggle so I'm going to click on that now we have all of our records back and you can see the filter has been turned off so that's a a great way to start to filter your data in Excel or at least that's the basic filters