Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel Pivot Tables display your data in as much or as little detail as you prefer. Pivoting and filtering the Pivot Table changes how the individual data elements are displayed, but you can also show or hide the subtotals and grand total rows and columns to summarize your data as desired. Subtotals appear at the bottom of each group. In this case, we have the year 2008. Month is the lowest level of organization in the Pivot Table, so we don't have any subtotals for it. At the bottom of 2008, we have a subtotal for all the months for FirmA and all of the months for FirmB.
To control subtotals for a field, you click any cell on the field, in this case 2008 for year, and then on the PivotTable toolbar click the Field Settings button. From here, you can calculate how Excel calculates your subtotals. For example, if you wanted to hide them entirely, you could click None. Click OK and they go away. To make another change, click the Field Settings button. And if you want to change to another calculation operation, you can click Custom. The default operation is Sum, where you find the arithmetic sum of all the values within a group.
If you wanted to change it to say Average, you could click Custom, click Average and then click OK. Now instead of the arithmetic sum, Excel calculates the average value, based on the inputs in this group and also in this group. If you want to change it back, click the Field Settings button, click Automatic, click OK and there you have it. You can also control your Pivot Table's grand totals. To do that, click any cell on the Pivot Table and then on the PivotTable toolbar, click the PivotTable button and click Table Options.
In the PivotTable Options dialog box, you can check or uncheck these two boxes so that you can show or hide grand totals for columns and or grand totals for rows. Checking the boxes mean they are displayed. Clearing them means they are not. So if I close them both and click OK, the grand totals go away. If I want to bring them back, just open the dialog box again. Check the boxes and back they come. You can also use other controls in the Table Options dialog box to determine whether Excel includes hidden values in subtotals. Usually the idea of filtering a Pivot Table is to hide values you don't want to include in your summaries, so I usually leave that box unchecked.
The box I am talking about is in Table Options. It's right here, just below the Grand totals for columns and the Grand totals for row options and the Autoformat table option. If you do want your subtotals to include hidden page items, check the box. You usually don't, so you can leave it unchecked. I didn't make any changes I want to keep, so I will click cancel. 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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 99233 Viewers
80 Video lessons · 141825 Viewers
59 Video lessons · 60201 Viewers
52 Video lessons · 73317 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.