Hi, this is Wayne again with a topic “Using Excel Slicers to Filter Data”.
In this tutorial, I will show you how to use slicers in Excel to show the exact data that you want to see. Now what are slicers? Basically, a slicer is an alternative to a filter. So in a previous video, I showed how you can sort and filter in Excel, and I demonstrated how you can just select the row that has the column headings and you can go to the Home. Tab home ribbon go here to sort and filter and click filter, and that makes it so that you can filter out specific data.
For example. Let’S say I don’t want to see any of the pg-13 movies in this list. I just uncheck pg-13 click, OK and all of the pg-13 results are now hidden because of that filter.
So that’s one way to show just the data that you want to show in a spreadsheet, but in some cases filters are not ideal. They can end up hiding data so that other people that might use this same spreadsheet might not know that that data even exists, they might miss it, and also filters, can sometimes be a little bit clunky and difficult to use. So I want to show you slicers as an alternative to filters now, if you’re gon na use slicers the first thing you’ve got to do is clean up your spreadsheet, and this is a spreadsheet that I used way back in my very first excel video. The beginner’s guide to excel, but I need to clean some things up.
First, I want to get rid of this movie inventory heading here at the top. I don’t really need that. That’S in the name of the spreadsheet up here, so I don’t really need that. It’S actually going to get in the way of what I want to do. So I’m gon na delete that all I have to do is right-click on row number 1 and select delete. It gets rid of the entire row. Another thing I need to do to clean up this spreadsheet is notice at the bottom of the data. Here I have some extra information, but in order to create slicers and do them properly, you don’t want to have extra data underneath the main data of your spreadsheet. So I’m gon na clear that out and one way to do that is just to select the data right-click and choose clear contents, and I’m even gon na go over here on the right and do the same thing with this extra data here. Just select it right. Click clear contents and I’ll also go ahead and clear this out as well I’ll select no borders to get rid of that now. The reason for cleaning up the spreadsheet like this is that I want to turn this into a table right now.
It’S just a bunch of data all in one range. This is a range of data, but I want to make it a formal table to do that. All I have to do is click somewhere inside the data.
How about right there and then I’ll go here to the insert tab on the insert ribbon. There’S the option for table so I click table and Excel, looks at the data and tries to guess where I want the table to be. I think it guessed correctly I’ll. Just click, OK and look what it did it formatted this data as a table. Now, if I don’t like the formatting that it did, I could go up here to table design and I could make some changes. I could pick different colors and things like that, but this is fine for what I want to do all right. The next step, in order to add slicers that will help me to more easily and more visually filter. My data – all I have to do – is click inside the table and when I do that, I get the table, design, tab, I’ll click there and then here in the tools group, it says insert slicer so I’ll click that and Excel gives me the option of choosing Which slicers to use in this spreadsheet, and here what you do, is you just pick the data that you care about the most that’s the most important to you. So, for example, with movies, I think genre is pretty important. I might want to watch a fantasy movie. I might want to watch a mystery and so that’s a pretty important type of data, the location in the house where it’s stored, that’s pretty important and how about the movie rating, like PG pg-13? That kind of thing, so I’m gon na go with those three and then I’ll click, OK and look what it does. It gives me three slicers kind of a weird name for it, but these three boxes really give me a lot of control over what is shown in my spreadsheet. I’M gon na shrink them down a little bit so they’re easier to place on the screen. This one is just gon na have trouble fitting. I guess I could shrink the view down here in the lower right and that should make it easier to fit everything I’ll, just put it there and I’ll zoom in a little bit more so with these slicers now activated, all I have to do is click some Of these buttons that it created so let’s say: there’s a family coming over to visit my family there’s some friends of ours and we’re inviting them over to watch a movie with us.
And let’s say we want to watch a comedy. All I have to do. Is click comedy and look? It shows me there’s only two options now, because both of those two options are stored in the living room notice. What happened to basement basement is greyed out.
I can’t pick that because there’s no results that will happen. I could also now filter by rating. Let’S say the kids are gon na be watching and we don’t want pg-13. I could click PG. So now I know what movie we’re gon na watch again great movie, though okay, so that worked great for me. But now let’s say I want to run it again. What I have to do is clear the filters you can’t just click to deselect a living room and deselect PG. You have to click this button, which clears the filter, clears the filter, clears the filter. Now we’re back to the full list of movies. Now some of you may have noticed a little problem that I had there when I clicked on comedy it excluded comedy dramas.
Well, those are also comedies. So there’s a little button here that you need to be aware of: it’s called multi-select. If you want to be able to click more than one of these buttons, you actually need to click this button to allow you to select more than one. So if I want to see comedy dramas and comedies listed here, watch what you do first, you click one of the two.
Let’S say comedies again. It only gives me two results and then I try to click comedy drama, but that switches off of comedy. So that’s where you need to click the multi select to allow you to select a second option. Alright. So now I see the list of three movies to watch, so I hope that you see the usefulness of slicers in Excel. It’S a great way to generate a list of just the specific data that you want to see and that you want to show then for watching this tutorial. I hope that you found it to be useful if you did. Please click the like button below and consider connecting with me on my social media accounts like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe, please click the bell. Next to the subscribe button, if you do you’ll be notified whenever I post another video and watch for another video from me at least every Monday, if you’d like to support my channel, consider becoming a supporter of mine through my patreon account and you’ll, see a link To that, in the description below .