Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you want greater control over a Pivot Table when you're making a presentation or when you're simply analyzing data, you need to know about Slicers. This is a relatively new feature in Pivot Tables introduced in Excel 2010. In this particular Pivot Table we're seeing Salespersons in the row labels area, Product in the column labels area. Now, suppose we want to see just some of the products. A standard way to make this choice is to click the drop arrow for Product and unselect the items we don't want to see. So maybe we don't want to see the two tables here and we're not seeing them and the totals are adjusted.
Now, do we know which fields were not seeing? Well, if we just made the change, yes. But, we might be looking at the Pivot Table in later time. Do we know our product list that well? Do we know what's not being seen? So, one aspect of using these arrows is that we might at different times want to know not just what we are seeing but what we're not seeing. And maybe we want to focus on just some of our salespersons. And so similarly, we could go to the drop arrow for Salesperson and not select all of the names. I'm not doing that but let's say, that we certainly could do that as well.
Now, Slicers which is available on the ANALYZE tab in the ribbon when we're using the Pivot Tables is found in the Filter group. Insert Slicer what we're about to see here is all of the field names. Now, we're not using all the fields right now. We're not using for example Region, we're not using Customer but we still might want to see Slicers for our various fields here. Perhaps not all of them and you want to experiment with this; but I'm going to use Salesperson, Product, Region and Customers, a small problem here it can be, our screen is going to be a bit crowded.
Let's click OK and see what we'll see here. We've got slicers now for Customer, Region, Product and Salesperson. We can make these a bit smaller something like that, take a bit of time to do it for all of these, you don't have to do this but just a reminder, you can redesign this as you wish. As you're manipulating these two, recognize that there is a SLICER TOOLS ribbon in the menu system also with an OPTIONS tab; we can change colors, heights and so on. Products, same idea. Now, the advantage of these is two fold. One, is we can see which entries are not being shown.
So, for example, right away we can see here on Product the two items that are not showing. If we only want to look at two of our customers, say Ellington Designs and Home Emporium. We'll click Ellington Designs and we're only seeing that customer and now holding down Ctrl if we click Home Emporium and we see the two of those together. And so, as we're looking at the Pivot Table, even though customer isn't even visible anywhere we're only looking at two of our customers. And so ultimately this could be better than what we're seeing with the standard Pivot Table field list.
And the more we move these, the more you manipulate them and you'll decide what's best in terms of how many of these you want to see but that gives us a clear picture of what this Pivot Table is all about in terms of what is showing and what isn't showing. You can make the Salesperson slicer by the way, be two columns. It might be a good choice, and then possibly make it wider and that way we can see the names that way too. So at any given time when we're working with this Pivot Table, we don't necessarily need our Pivot Table field list although it's certainly is helpful to remind us of the layout.
But let's say we move that aside a little bit by dragging its title at the top. Let's drag it over here for example, and focus more on the slicers. The red X within the slicers looks like it's a way to get rid of the slicer but what it really means is clear the filter. In order words, select all of the fields. So click the red X to select all of the fields, and in effect we're not using Product now because all of the products are being shown. Once again, if we only want to look at two of these, click that one then use the Ctrl key. If there's a consecutive cluster we want to use, if we want to use the first three; click Bamboo Coffee Table and then with the Shift key click Captain Recliner, so we see those three.
So, at any given time with these slicers here you know what's showing in the Pivot Table and what's not showing.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97975 Viewers
80 Video lessons · 141248 Viewers
59 Video lessons · 59610 Viewers
52 Video lessons · 72866 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.