Hi, this is Wayne again with a topic “Filtering in Excel: Basics and Beyond”.
This video is all about using filters in microsoft, excel we’ll look at the basics and beyond. So take a look at this spreadsheet that i have it’s a list of some of the top guards in the mba and there’s a lot of statistics here. A lot of data to digest what, if i want to simplify what i’m looking at and just focus only on the data that i’m interested in applying a filter can really help me to do that to apply a filter. I just need to click inside my data somewhere and then go either to the home tab and here in the editing group. There’S a sort and filter button and i can go down here and click filter or i could go to the data tab.
And here in the sort and filter group there’s the filter button either way just click on filter and notice that the view of my data has changed a little bit. Several buttons have been added to my spreadsheet. Each of these buttons can be clicked to apply a filter or to remove a filter to change how a filter works now remember filters in excel help us to focus in on just the data we’re interested in so let’s say i only need information about players on The denver nuggets, what i would do is i would go to column b and click on the filter for team and a filter menu appears, and here it lists all of the teams i’ll uncheck, this box that says select all so now. None of them are selected and then i’ll select, denver, click, ok and now all of the other data seems to have gone away, and all i can see is the statistics for these four denver players now, the first time you do this, it can feel like you’ve Ruined your data you’ve ruined the spreadsheet.
Where did all that? Other information go? But if you look here in the lower left, there’s a hint that the other data still exists. It says 4 of 29 records found, so there are still 29 records. 29 total players in this spreadsheet, but only four of them are being shown. You can also look here at the left and notice that the excel rows skip from 1 to 8 to 9 10 11, and then it skips to 31. in excel. You really can’t get rid of row numbers. You can’t get rid of column, letters you can hide them or filter them out, but they’re not gone forever. Now, if you look at my filter buttons in the spreadsheet, you’ll notice that this one has changed, it has a little funnel symbol indicating that a filter has been applied and if i want to, i can click that button again and i can clear the filter from Team and that brings back all of the data – it’s all visible now, let’s look at some other types of filters that you can apply and some examples of how useful that can be. So let’s say i’m interested in knowing what mba guards are particularly good at scoring, but also at rebounding. What i could do is i could apply two filters, so i’m going to go here to points per game, i’ll click, the filter button and here on the filter menu.
I could deselect all and then one by one i could select only the top scorers but there’s an easier way. Instead, what i can do is go here to number filters, and i can just say i only want to see numbers that are greater than let’s say: 20. click, ok and it filters out people that score 20 points or fewer.
Now, of course, i could have chosen greater than or equal to or between, but notice that there’s also a top 10 option and that’s actually what i want to do in this case. So top 10 items click. Ok, so these are the top 10 scoring guards in this list, and i also want a second filter and again i could just one by one deselect what i don’t want to see, but it’s easier just to go here to this menu and select greater than let’s Say four rebounds per game notice that you could put two criteria in so is greater than four and or you could say or lower than 2, or something like that.
But i’m just going to stick with this click. Ok, so now we have a list of nba guards that are particularly good at scoring points and getting rebounds. Now this menu that i’ve been showing you that’s right above the check boxes, this is a context, sensitive menu. In other words, it changes based on the column and the data.
That’S in that column that you’re working with so, for example, if i go over here to team – and i click this filter button now it says text filters and it doesn’t say between. Instead, it says the text equals does not equal begins with ends with, because it’s dealing with letters and words not numbers. The menu changes contains that’s a very powerful option, so it must contain a certain word or maybe it does not contain a certain word. So i love those context, sensitive menus that you get with these special filter options that are above the check boxes.
When i’m done working with my filters, there’s a couple of options i have. I could just clear the filters that i’ve applied by going up here on the data, ribbon, sort and filter group and just click clear. If i do it that way, i still have my filter buttons, but the filters are no longer applied. So all of the data is visible.
The other option is to click this big filter button. If you do that it not only unapplies the filter, but it removes the filter buttons from your header row. Let’S now take a quick look at some useful keyboard shortcuts that will help you as you’re filtering data in excel.
The first filter shortcut you need to know is ctrl shift, l if you hold ctrl, hold shift and then tap l. It automatically adds filter buttons to your header row. It’S just like clicking this filter button here at the top, and it is a toggle.
So if you do it again, it removes the filter buttons, you do it again, it brings it back and so on now there is another way to do a similar thing. Instead of ctrl shift l, you could hold ctrl and then tap t and what that does. Is it takes your data that you’ve clicked inside of you need to have clicked somewhere inside that data, but it turns it into a table and notice.
There’S a check box for my table has headers. If you then click ok, look what it does. The data is now formatted as a table and as part of that filter buttons have been added to the header row.
So that’s another way to quickly get your filter buttons for each column, i’m going to undo that by holding ctrl and tapping z. If you want to learn more about tables watch my video tutorial all about tables, there’s a lot more to learn about them. In this case, i’m just going to hold ctrl shift. L to add my filter buttons.
Another keyboard shortcut you may be interested in is, if you arrow up to the header row, whatever header you’re on, if you hold the alt key and tap the down arrow, it brings up the filter menu and now you can use the arrow keys to move down And then deselect or select each check box now the way i’m deselecting and selecting is by tapping the space bar on the keyboard, so i can quickly deselect the ones i don’t want and select the ones i do want. I can also use the arrow keys and go here to the number filters and then tap enter to choose the one i want to use. So it is possible to do a lot of the filtering with your hands on the keyboard, which is always nice. It’S more efficient whenever you’re using excel to try to keep your hands on the keyboard. If you’re interested in learning about other forms of filtering, please watch some of my other videos that cover those other options. In particular, you should watch my filter function. Videos, my video on excel slicers and also on excel power query, but for now thanks for watching. I hope you found this tutorial to be helpful.
If you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, consider clicking the thanks button or you could support me through my patreon account or by buying channel merch, and you should see more information about those options in the description below the video .