Second, protect the sheet using the Review tab in the Ribbon. First, select the slicer or slicers and Disable resizing and moving in the Format Slicer task pane (and choose other options based on your preferences). You can lock the position of a slicer in an Excel worksheet using a two-step process. Slicers are a great tool in Excel to filter tables or pivot tables. 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.Lock the Position of a Slicer in an Excel Worksheet So It Can't Be Movedīy Avantix Learning Team | Updated June 14, 2021Īpplies to: Microsoft ® Excel ® 2013, 2016, 2019 and 365 (Windows) You can select individual years one by one. Let's say we want to only filter by year. In this case, it's defaulted to filtering by month. 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. 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. 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. 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. Then you can change height and width properties as well, just like you could edit any other shape in Excel. 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. So right now I've got one tall slicer with all of my genres listed out vertically. And then, one really useful option over here to the right is this Columns option. 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. Just like any other table, you can choose your styles, so your colors and fills and orders. You can choose to hide or show items that have been deleted from your source data. You can change the default sorting and ordering. 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. And within there, we've got a few options. 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. 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 within this slicer, you have a few customization options. 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. 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 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. I can choose Genre from here and press OK. Here I have access to any of the fields that I'm able to filter on. So that's one option, the second option is to go into our pivot table tool Analyze tab and insert Genre as a slicer.
I'm going down to Comedies, it looks like the USA is the leader there, kind of similar order. 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. 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. And why don't we pull Country in? So now we're looking at data by country. Okay, so the first thing I'll do is actually pull a release data out of the pivot. So let's jump into our pivot and try inserting some of these. Timelines are specifically designed for dates, so they have a bit more of a chronological layout.
Now, the only difference between a slicer and a timeline is that slicers can be used for any field. The only difference is that they add a nice little user-friendly interface to help filter your data.
So they work exactly the same way as pulling a field and dragging it into the filter box in your field list. And basically, slicers and timelines are just visual representations of filters. 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.