Join Dennis Taylor for an in-depth discussion in this video Using slicers to clarify and manipulate fields, part of Excel 2016 Essential Training.
- As you're working with PivotTables, it certainly makes sense at times to see less information, so we've got filters in a variety of locations, and also a relatively new feature, introduced in Excel 2010 called Slicers. We're looking at a worksheet called Slicers in our workbook 14-PivotTables. Within the PivotTable Fields List, you'll see a section called Filters. It's going to be less important in the future because of this new feature called Slicers. Let's talk about how it works. First of all, let's bring in the Product field into the filters area.
Now, as you keep your eye on the PivotTable to the left, look what happens above it. It's actually part of the PivotTable, but we haven't used it just yet. It's for Product. Let's click the drop arrow here. Maybe we only want to look at Bamboo Coffee Tables. Click there. Okay, that's all we're seeing. And we're reminded of that. We see that in the heading, and of course PivotTable gets wider at some points, but we're not seeing the total we saw before, not even close to it. We're only focused on one item, and we can certainly go back there and click other items as well.
Notice the button Select Multiple Items. We can click there. We can click other buttons as well. Captain Recliner, Bamboo End Table. Let's look at those three. Look at what happens right now. This says [Multiple Items]. Now, it's been only a minute or so ago, but do you remember which ones we are seeing and which ones, maybe, we're not seeing? One of the downsides of filtering at certain times is, you want to be reminded, or you want to know what's not being shown as well as what is, and right now we're in a situation where we're not even sure what is being shown, unless it's just very recent. It depends.
And what's not being shown? Sort of forgot, unless we really know this list well. So, I'm going to press Ctrl+Z to undo this. Before I proceed, we also have a filter arrow here. Here are the customers. Let's suppose we want to see all the customers, except Home Emporium. We'll uncheck that, and we're seeing all the others, and the total has adjusted, and similarly with Salesperson. Same general idea. We've got control over which ones we want to see at any given time. But again, there's that strong reminder.
Let's say we don't want to see John Lucas right now. We don't want to see Robert Owen, so we unselect those, but we're seeing all the others. Which ones are we not seeing again? Well, either we remember or we have to keep going back here. So, there are some downsides to filtering, and that Filters area, although it can be helpful at time, we do have this problem of not remembering what it is we're seeing and not seeing. So, Ctrl+Z again. Let's bring back all the data. No filtering taking place right now. Slicer is available on the Analyze tab.
It's a visual tool. The problem sometimes is it takes up a good deal of screen space. I'm going to insert a Slicer for some Fields here. First of all, for the Fields that are already in the PivotTable. Customer and Salesperson. Salesperson there, Customer there. Those are already in the PivotTable. I also want to show with Slicers Product and Region. So there's another element of this we're about to see. Let's click OK. Now, if you were making a presentation, ideally you'd be doing what I'm doing ahead of time, but I do want to show you how to manipulate these a little bit.
They can take up some space, depending upon the Field. Move these around different ways. Notice a Contextual tab appears in the Ribbon. I could go up there and make this one green and this one yellow, and so on. Move the Product over here, shrink it a bit also, so that it takes up less space, and also Salesperson. We can even make these two column if necessary, not a bad idea here. In the Options tab up there. Columns, to the right. Two. Then we can make this wider. Now again, emphasizing this idea.
Two of these Fields are currently present in the PivotTable. Customer and Salesperson. Region and Product are certainly part of our source data, but not in the PivotTable. And again, slight color differentiation here just to bring this out. Here's how the Slicer's going to work. Someone is asking us to see this layout, but only for the Northeast Region. I'm going to click Northeast over in the Region Slicer, and that's all we're seeing. Some of our Salespersons don't work out of the Northeast. They never sell to that region. That's why we're seeing fewer Salespersons, and the Slicer for Salespersons is reminding us of which people are not active in the Northeast, as well as those who are.
So they're working in sync. I didn't touch the Salesperson Slicer here. I made the choice on Region. Let's include another region. How about the Southeast region? I'll use the Ctrl key, and click the Region Slicer entry for Southeast. And now what do we see? Southeast and Northeast. Now as we're looking at the PivotTable itself, there's no indication whatsoever about regions, but we can clearly see from the Slicers what's going on. Let's go over to Customer. Maybe we only want to see the first three customers here.
Let's click B&B Spaces, and then with the Shift key, I'll click Fabulous Homes, and we'll be seeing those three together. Now that is being shown in the PivotTable, and so I wouldn't call it redundant, but we are reminded and we were not before when looking at filters in general. We are reminded in our Customer list which two customers are not currently being shown. So I think you can sense here there's a lot of creativity. It's almost as if you could teach someone who didn't know Excel that well to learn how to use these Slicers and actually make a presentation on this data that we see to the right.
The icon on the upper right corner of each of these shows the filter with an X, which means clear the filter. In other words, do not filter. Show all of these. At first maybe that confuses you a little bit, but this means don't filter at all. Show all the Customers, or show all the Regions. If you want to focus on just certain Salespersons, that might eliminate some of the regions, so if you choose Leonard Warren, he's active in two regions. Most of the Salesperson active in only one, but holding down the Ctrl key now, I'm gonna check Norm Treigle over here and also Dotty Kirsten, there we are, and also Sam Ramey, and we're seeing those people.
We see Salesperson over in column A, but that's one of the fields that's currently visible in the PivotTable, but we also see in the Slicers, and we're reminded of who's not being shown here, and we see what's happening in Customer and Region. Let's only focus on Bamboo Coffee Tables and End Tables, so over here in Product Bamboo Coffee Table. With the Ctrl key, Bamboo End Table. The more you work with this, the more powerful I think you'll see it is, but in larger PivotTables with screen space at a premium, remember when you click in the PivotTable, there's that PivotTable Field List.
Now, you can hide this, too, if you wish, or move it around in such a way that it's not in the way, but a powerful tool. Starting in Excel 2013 you can also use Slicers on tables if you're familiar with that concept, not just PivotTables, but it's a great feature to be using when you're making a presentation to only see the information you want. Remember, these Slicers can relate to fields that are currently in the PivotTable and also fields that are not in the PivotTable, and yet part of the source data.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros