Getting great results from your data with Filters in Microsoft Excel 2010, 2013, 2016 tutorial

in this lesson I'm going to show you how to make Excel filters using uh your Excel data what a filter is when we take a look at this list of transactions maybe you just wanted to see the dinner items only or maybe you just wanted to see the ones from Austria that's what a filter is uh in this lesson I'll show you the simple filters and in another lesson we'll do the advanced filters now it's really important that the first row contains your field names as you can see each row after that is a different record or a transaction each column is a different field now if you scroll down you're going to notice that there's no blank rows and there's no blank columns all the way down it's one continuous block of data so your data starts off at this you're going to click on any of the cells and are not blank now you're going to pick in the data menu up top data then you'll pick on the word filter over here and this will start the simple filter notice how the pull Downs are in the top of each column watch how s watch how simple this this can be I'm going to click on the pull down for uh column F which is expense type now you can actually pick and choose as many as you wanted to so I'm going to uncheck where it says select all then I'll pick on breakfast lunch and dinner you really can pick one or as many as you wanted to here which is really great I'm going to click on okay now if you'll notice the list got smaller because now we're only showing the breakfast lunch and dinner items and that's exactly what a filter is now the records didn't disappear they're just being hidden temporarily notice how it goes from record 4 to 16 or record 19 to 32 so we didn't delete the records they're just hiding temporarily now you can keep on adding more criteria as much as you wanted to so for example I'm going to pick in the pull down where it says country then even here I can uh uncheck select all and pick as many as I wanted to so I'll pick on France Germany and Spain um click on okay now the list even got smaller because they have to be from France Germany and Spain and they have to be either breakfast lunch and dinner at the same time so you can keep on going and add as much criteria as you wanted to uh now um notice that columns that have a filter you can tell because the pull down looks a little bit different than the other columns now you can also remove the filter one column at a time just like you can put it on one column at a time so I'm going to click on the pull down where it says contr and I'm going to say clear filter from the country or of course you can pick and choose different ones if you wanted to so now the filter is off of the country column but it's still on the expense type column I'm going to remove that as well and we'll say clear filter from the expense type now you have all the records back let's see how it works for number Fields I would like to see all the ones that are over $30 so I'll click on the pull down where it says Us doll amount and we'll say number filter and in this case you can have greater than less than all of your comparison operators let's say greater than and then I'll type in 30 here and click on okay now we have just the ones that are over 30 all right so that's a nice thing to do with a number field we could do less than or greater than or those kind of comparison operators let's try something else with the number field I'm going to clear the filter from that column I'm going to click in the pull in where it says US dollar amount then we'll go back to number filters and let me show you a couple that are really interesting here we have top 10 that'll actually show you the top 10 values above averag is brand new for 2010 that'll show you the ones that are above the average for that list and we also have one that's called below average that'll show the ones that are below average for that list so let's check on top 10 then you get this other screen now even though it said top 10 we can even show the bottom 10 the top 10 of course are the highest SI and the bottom 10 of course are the lowest values I'm going to pick on top 10 pick on top now you can pick a different number let's go 15 so I want to see the top 15 items from the entire list I'll click on okay and notice how the list got smaller notice how it's only showing you the 15 items and these are the highest values in the entire list so that's a really great one that's called the top 10 that works really well with a number field we got it in there with number filters as you can see now I'm going to clear the filter from that list you let's see how it works with date fields here we have a date and column a I want to click on the pull L for date and uh in this case I'll say date filters now look what you have you have all these buil-in date ranges I think this started in Excel 2007 so I can see this week next month next year um based on your computer system clock now you can also do before a certain date or equals a date after a date or between two dates but I love the fact that we have all the build-in date ranges there as well so let's do a specific date range in my case I know the records are from July or August 1998 so I'm going to say between 71 1998 and 715 1998 let's see how it says after that date and before equal to that date I'm going to click on okay notice how the list got smaller and now we're only looking at the ones between J July 1st and July 15th of 1998 of course any date range would work there uh I'm going to go back and go back to the date filters I really like the fact that we have all these built-in date ranges but of course you have your your specific date ranges here as well in this case I'm going to say clear filter from the day column and when you're completely done with your filter you can pick in the data menu then either pick on the word clear and that that'll get all the records back but still put the pull Downs on there if you want to take the whole thing off pick in the data menu and pick on the word filter that'll remove the pull downs and return all of your data for you uh by the way that's your toggle I can click on that again and toggle it back on and click on it again and toggle it back off so that's how we can do filters a very powerful feature in Microsoft Excel