Pivoting and filtering the PivotTable changes how the individual data elements are displayed, but you can also position the subtotals and grand totals for rows and columns to summarize your data as desired.
- [Instructor] Excel pivot tables display your data in as much or as little detail as you prefer. Pivoting your pivot table changes how the individual data elements are displayed, but you can also position the subtotals and grand totals for rows and columns to summarize your data as desired. I will demonstrate how to perform those tasks in this movie. My sample file is the Totals workbook, and you can find it in the chapter nine folder of your exercise files collection. As you can see, this workbook contains a pivot table on sheet one, and it summarizes sales first by product category, then by individual product with the sum of all sales in the data area.
A subtotal for each product category appears here at the top of each group, which is on the same line as the label. You can see this same here for B4, which is batteries and then B13, grid tie inverters. And if we scroll down all the way to the bottom, we can see a final subtotal for wind harvesters and then a grand total at the bottom. We'll scroll back up. Now let's say that I want to change the position of subtotals, or perhaps to turn them off entirely.
To do that, I will click any cell on the pivot table and then on the design contextual tab of the ribbon. I will go to the left side, and here you see I can control subtotals and grand totals and also the report layout. I'll start with subtotals. So I'll click the subtotals button, and I can either turn them off or show them at the top or bottom of the group. They are currently shown at the top of the group, but I actually prefer to have them at the bottom. So I will click show all subtotals at bottom of group.
And now I have the label for a product category, then the individual values, and then below that the subtotal. I actually prefer this a lot. And the reason is that I don't really need to see the subtotal first. I think of a column of numbers that's added from the top to the bottom, and having the subtotal at the top just somehow seems wrong to me. But do whatever it is that you're most comfortable with. As you notice, you can also turn them off or change their position so they're at the top.
You can also turn grand totals on or off. You can't reposition them cause they're always going to be either at the far right or the bottom. But let's say that I want to turn off the grand total for this particular pivot table. So I will go to the grand totals button and I will turn off for rows and columns. You see there was a slight change, and if I scroll down all the way to the bottom the grand total has been turned off but the subtotals are still there.
I'll go back to grand totals and turn it back on for rows and columns. And you can see it appears at the bottom of column B. Finally, you can change your report layout. I'll scroll back up to the top and click the report layout button. And here you see we have show in compact form, show in outline form and show in tabular form. I'll click show in tabular form. And you can see that the format has changed. We have product category, batteries total, and so on.
Well, let's say that I want to repeat the row label batteries. To do that, I can go to report layout and click repeat all item labels. And we get batteries, batteries total, and so on. Now one thing to note, if I turn off subtotals by clicking don't show subtotals then I get a list that I can use for lookup or perhaps as an Excel table, 'cause you notice I have the product category that is listed here, and if I were to add order numbers over on the left or some other summary, then I could use this as a V lookup source.
I'll turn subtotals back on and put them at the bottom of the group, and I'll show you the other layouts. So I will click report layout, and I will click don't repeat item labels just to go back to where we were. Then report layout, I will show in outline form. And you can see there's a slight change, and then report layout again in compact form and this is what we had before, a nice compact layout where we have batteries and then the individual products and the data area.
As you can see, subtotals and grand totals provide easy-to-read summaries of the values in your pivot table's rows and columns. If you find those extra rows and columns are a distraction, or if you just want to emphasize the values in the body of the data area, you can hide them quite easily. Also play around with your report layouts. You might find something you like better than what is shown by default.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks