Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel PivotTables display your data in as much or as little detail as you prefer. Pivoting 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. So let's take a look at this by now familiar PivotTable, where I have my rows, which are the months, and then also I have FirmA and FirmB. On the right side, I have my Grand Total column, where I have a total for all the values in a particular row, and then at the bottom I also have grand totals for the columns, which adds up all of these values here. And then you have a grand total for the entire worksheet.
Now let's see what happens when I add the Year field to the Row Labels area. To do that, I will drag the Field header from the Field name box down to the Row Labels box. It's in position, and there it is. When you have two or more fields in either the Row Labels area or the Column Labels area, Excel creates a subtotal for each one of those new divisions. So, for example, here for 2008, I have the 2008 Total. That's the first year, and then scrolling down, you'll see that I have a subtotal for 2009.
There's no subtotal for the individual months. That's the lowest level of organization, so there's no need for a subtotal. Instead, I have one for the two Years and then the grand total for each of the Columns remains the same. If you want, you can reposition your subtotals within your PivotTable. To do that, you click any cell in the PivotTable, and then on the Ribbon, you click the PivotTable contextual tab, which displays the controls that you can use to work with your PivotTable. To change the position of your subtotals, in the Design group, you click the Subtotals button, and then you can select whether to show the subtotals at the bottom or show the subtotals at the top.
I prefer the bottom, but just in case you prefer to show them at the top, you can click Show at Top, and the subtotals moved to the top of the group. Again, it's strictly a matter of personal preference; you can put them anywhere you like. You can also control the grand totals, either for the rows or the columns or both. So if I click the Totals button, I'll see that I can either show it for rows, show it for columns, or both. If an item has a check mark next to it, that means its currently being displayed. So let's say that I want to turn them off for columns. So I will click Show for Columns, and Excel gets rid of the column on the right-hand side that show the grand totals.
If I want to get rid of the one to show for rows, I can click Show for Rows, clear it, and when I scroll down, you'll see that the grand total is no longer being shown. To bring it back, I click Totals, click Show for Columns, and just to bring it back to the way it was, Show for Rows. Now, why would you turn off grand totals for either your columns or your rows? Well, one reason is that if your data almost fits on the screen, and you just need one section, just one more row, that's a great time to turn them off. Also, if you want to focus on individual values, you can turn off the grand totals as well.
You can't turn off subtotals, but you can reposition them. So decide whether you prefer to have them at the top or the bottom of a group, and put them where you like. Subtotals and grand totals provide easy- to-read summaries of the values in your PivotTable'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 reposition or hide them quite easily.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64627 Viewers
80 Video lessons · 124266 Viewers
52 Video lessons · 60223 Viewers
59 Video lessons · 46053 Viewers