Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this workbook we have a simple PivotTable on Sheet 1 and if you want you can take a peek here down in the Data tab, so you can see the underlying data there, but let's go back here to Sheet 1. First thing we want to do in editing the PivotTable is let's change this sheet name to something useful. Instead of Sheet 1 you can double-click it. Maybe call it PivotTable or call it Summary. Something a little bit more useful and understandable. Now let's go and do some filtering, and you see up here we have these little down arrows for our column and row labels and report filters.
Let's start by filtering the months. Click this down arrow and right now you can see we have all three months showing, but maybe we just want to see January. Perhaps this is the beginning of the year and we want to see how sales are going just for this month, or perhaps data are not complete for the other months. Click OK and now you can see were filtered just by January. Maybe we want to see two months. So click that Filter again, down here Select Multiple Items and now you can put checks in those two months and then click OK and now we see we are sorted by Multiple Items.
Maybe we want to filter by region. Perhaps we get a request from department managers or sales managers for specific data. So click that down arrow and over here it says Select All, just click that so we deselect all the regions. Maybe we only want to see the Central and Northeast regions, perhaps we have a sales special going on in those two regions so we want to look at those. And click OK, and now we have just those two regions selected. May be the sales special that's going on is only going to be for the 1 liter and 2 liter size.
So click this down arrow again, deselect that Select All and we'll check just 1 liter and 2 liter size, click OK. By the way if this 1 liter kind of annoys you there that's aligned to the left, you can simply click it and in the Home tab just click the Right Align to format it a little nicer. Now that we have it filtered, perhaps we want the underlying data for just these numbers that we have. So double-click any of them and you notice that Excel creates a brand-new worksheet down here with just the underlying data for that particular cell that we double-clicked.
Now this is not dynamic. This is simply extracting and you can click on any of these and you can see up here in the Formula bar, this is simply like a copy-and-paste. Now let's go back to the Summary tab so we get back to the PivotTable. Now if we decide that we want to unfilter these, there's a couple of ways you can do it. You could unfilter one-by-one. Click the Filter button. Now you could mess around down here by selecting/deselecting, but it's a little faster if you go over here and you click Clear Filter. Now you've cleared that filter but we still have these two that are filtered, and if you want to clear all the filters, there is faster way to do it.
Up here in the Ribbon bar we have the PivotTable Tools section, and that's visible only if you've clicked inside the PivotTable. Click the Options tab and over here click Clear and choose Clear Filters. Don't choose Clear All because if you do that you'll wipe out the PivotTable. Just click Clear Filters and now the filters are cleared out from all of those options. Okay, well this is a PivotTable. So let's do a little pivoting. Let's take the Month over here in the Report Filter section and let's drag Month down below Region, and look what happens to the PivotTable.
So now we have the months are separated by region. So maybe if you have separate sales managers for each region you could give this piece of the PivotTable to each sales manager and say, "hey, here's how your products are doing." Let's pivot it a little bit more. Let's take Size, and let's drag Size into the Report Filter. So we kept the original filtering by region but now we can filter it further by size. May be we only care about the 2 liter size, so click 2 liter, click OK, so we still have it filtered by region and sub-filtered by month but only for the 2 liter size, and if we want to see every size again click that Filter button and choose All, and click OK.
Well, what if we decide that we want to see average values instead of sum values? Because what's happening here is this is simply adding all the numbers together. You want to make sure you are still in the PivotTable Tools section and the Options tab and over here where it says Summarize Values By, click that, choose Average, and now you have average values instead of summary values. And if you want to change it back you could always go back here and change it to Sum. There is some interactive filtering that we can do and again in the Options tab, click Insert Slicer and choose Insert Slicer from the fly-out, and we get the Insert Slicer dialog box. And what this will let us do is put a little panel that will let us filter by any of these data points.
So let's choose Month and Region and click OK and we can separate these slicers here just to make it a little easier to deal with. Before we do anything let's make them look nice. Select one of the slicers and over here in the Options tab, let's click down and maybe let's give it a green olive-y look and choose the other Slicer and give this maybe a slightly different green olive-y look and while we are at it, let's format the PivotTable then we'll use the slicers. So click somewhere in the PivotTable and again in this section here go to Design and when you click that, you can roll your mouse over and before you pick one you can see what it's going to look like. I'll choose this.
So what are the slicers all about? Well, in the Region Slicer, click let's say Southeast and now you've immediately filtered by Southeast or by Central or by West. In the Month right now we see all of them. Maybe we want to see just January. Well, we can choose multiple values. For example if we want to see January and March but not February, make sure January is selected and select it if you need to and hold down the Ctrl key on your keyboard and Ctrl+Click March, and then we have January-March. Maybe we want to see just the Central and Northeast regions, so click Central and again hold down the Ctrl key on your keyboard and click Northeast.
So now have just the Central and Northeast sections. So if we have a client or a boss or somebody who wants this specific information, you can see it takes only one or two clicks to get the specific detailed information that we want. Now I hope this has given you a good idea of what PivotTables are and how they can be useful and they are a lot more ways that you can modify them. We just don't have time to go into really tremendous detail in this course. But if you do want to learn about PivotTables in depth take a look at some of the great PivotTable courses in the lynda.com library.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 81446 Viewers
80 Video lessons · 133295 Viewers
52 Video lessons · 66658 Viewers
59 Video lessons · 52482 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.
Your file was successfully uploaded.