From the course: Excel PivotTables: Mastering PivotTables and PivotCharts

Filtering with slicers and timelines - Microsoft Excel Tutorial

From the course: Excel PivotTables: Mastering PivotTables and PivotCharts

Start my 1-month free trial

Filtering with slicers and timelines

“

- [Instructor] Now, one of the coolest options when it comes to filtering data with pivot tables is the ability to use things called slicers and timelines. And basically, slicers and timelines are just visual representations of filters. So they work exactly the same way as pulling a field and dragging it into the filter box in your field list. The only difference is that they add a nice little user-friendly interface to help filter your data. Now, the only difference between a slicer and a timeline is that slicers can be used for any field. Timelines are specifically designed for dates, so they have a bit more of a chronological layout. So let's jump into our pivot and try inserting some of these. Okay, so the first thing I'll do is actually pull a release data out of the pivot. And why don't we pull Country in? So now we're looking at data by country. And I'm just going to apply a sort option here, sort it descending by revenue so that we lead off with the USA, then UK, Germany, France, Canada, etc. What if we want to see how this list of countries changes, based on different genres, specifically? So, one way to do that would be to pull a genre right here into the filter list and just kind of make adjustments here, and say, okay, among Westerns, that really only two countries in the data set that produce westerns. I'm going down to Comedies, it looks like the USA is the leader there, kind of similar order. So that's one option, the second option is to go into our pivot table tool Analyze tab and insert Genre as a slicer. So I'm going to Insert Slicer. Here I have access to any of the fields that I'm able to filter on. I can choose Genre from here and press OK. And as you can see, it dropped in this nice little, kind of standalone filter preset to Comedy, since that's what I had Genre filtered to. And now that I have this slicer version, I can actually pull Genre out because I don't need it to exist in my filter list as well. And so, now, as I interact with this box, I'm changing the data based on the selections that I'm making, so now I'm only looking at adventure movies, only animation movies, only comedies, and so on and so forth. So within this slicer, you have a few customization options. The first is to enable multi-select. So if you want a user to be able to select more than one genre at a time, that setting allows you to do that. So now we're looking at a combination of comedies and documentaries, for example, and you may have noticed that a new menu is created called Slicer Tools. And within there, we've got a few options. I won't cover every single one of these, but you have some really helpful settings here where you can change the name or display name of your header. You can change the default sorting and ordering. You can choose to hide or show items that have been deleted from your source data. So a lot of good tools there. Just like any other table, you can choose your styles, so your colors and fills and orders. You've got alignment tools that will help you kind of snap these to grids, if you want align multiple slicers and timelines together to create a dashboard. And then, one really useful option over here to the right is this Columns option. So right now I've got one tall slicer with all of my genres listed out vertically. I can change that to two, and basically create a slicer that's two columns wide, and that's really just a matter of personal style, or look and feel. It's a really good option to have. Then you can change height and width properties as well, just like you could edit any other shape in Excel. So there you go, that's my slicer. Now let's go ahead and select one of the pivot table fields, go into our tools again, and this time let's insert a timeline. Now, when I insert a timeline, the only field that populates is Release Date, since that's the only field in my data set that's a date field. So I can select Release Date and press OK, and it drops in another little visual interface where I can now filter down based on this date field, so you can drag, you can select individual values, and so on and so forth. One thing you'll want to set right off the bat is this drop-down option here, so this determines at what level you want the user to be able to filter your dates. In this case, it's defaulted to filtering by month. Let's say we want to only filter by year. Now you can drag to create ranges. You can select individual years one by one. It just creates a really nice, really intuitive user-friendly interface to help filter the data that you're viewing right here in your table.

Contents