Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Most of the data that you summarize using PivotTables will have some sort of financial and personal aspect, such as sales or hours worked, which means that you'll usually want to find the sum of the values in the list. You're not limited to adding values together though. You can choose from several summary calculations and also have the ability to change the type of value shown in the data area. For example, you can express individual values as a percentage of the column or row total or compare values to those of the previous year. If you want to change the summary calculation used in a PivotTable, right-click any cell in the PivotTable's data area and that means any cell that contains an individual value.
So, for example, not a grand total or a subtotal and not any of the labels either here in the columns or here for the rows. So instead, right-click any cell in the data area, point to Summarize Values By, and then select the operation that you want to use. In this case, I'll change it to Average. Now note that the values in the body of the data area didn't appear to change and that's because each individual value is its own average. There is only one value for 2008 in the month of January so the average will be 67.
However, here in the Grand Total area you'll see that the values have changed. So for example, let's look at March. We have the value of 128 and then add the value of 34, which is 162, and then add the value of 138 in cell D8. That's a total of 300. There are three values, the average of which is 100, which is displayed here in the Grand Total column. In addition to changing the summary operation you can change how the values are displayed within the body of the PivotTable. To do that, first I will switch the aummary operation back to Sum, so I'll right-click a cell point to Summarize Values By and then click Sum.
And now I'm going to change how the values are calculated. To do that right-click any cell in the data area and then point to Show Values As and then select the type of operation that you want to do. In this case, I want to compare the values in 2009 to those of 2008 and the values in 2010 to those in 2009. In other words, I want to compare each year to its previous year. Click % Of. Doing so displays a dialog box that you can use to set your Base field and the Base Item.
So in this case, I want the Base field to be Year, so I am comparing year on year, and I want to compare each year to its previous year and the Base Item is already selected as (previous) so I can click OK, and Excel updates the PivotTable with those calculations. So for example, the value from 2009 was 191% roughly of the same value for January 2008 and in 2010 that value was only 65% of the value from 2009.
Now let's say that I want to compare 2009 and 2010 to 2008. To do that, I can right-click any cell in the data area, point to Show Values As and then again click % Of to display the dialog box. We're still comparing Years but now I can click the Base Item box's down arrow and instead of clicking (previous) I can click 2008. And when I click OK, the PivotTable updates its calculations and now 2009 and 2010 are both compared to the values for the same month from 2008.
There is one other summary operation I'd like to show you and that is creating a running total in each column. To do that right-click any cell in the data area, point to Show Values As, and then click Running Total In. Then in the dialog box you can select your Base field and in this case I do want it to be Month, and you can click OK and the PivotTable now contains the running total for each year by month. So for example, January of 2008 had a value of 67 and then at the end of February we had a total of 136. So the value for February isn't 136 but the sum of all revenue for January and February is 136 and so on.
Now notice that there is not a subtotal at the bottom of this group and the reason is because that subtotal actually appears in December, because of that is the running total of all 12 months in an individual year. You should take some time to experiment with the summary operations and settings available for use in the data area. You'll probably find one or more operations other than addition that provide meaningful information about your data.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64698 Viewers
80 Video lessons · 124318 Viewers
52 Video lessons · 60253 Viewers
59 Video lessons · 46084 Viewers