Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Most of the data lists you summarize using Pivot Tables contain some sort of financial or personnel data, such as sales or hours worked, which means that you'll usually want to find the sum of the values in the list. You are 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. To change the summary calculation used in the Pivot Table, click any cell in the PivotTable data area. And then, on the PivotTable toolbar, click the Field Settings button.
Then in the Summarize by list, click the operation that you want to use. So if I wanted to change the summary operation to average, I click Average, click OK and Excel now displays the average. For 2008, the monthly average would have been 80.25. For 2009, that number there, and so on. You'll notice that the grand total values also take on the average. So 67 and 128 averages out to 97.5. 69 and 131 averages out to 100 and so on.
I will show you one other operation, in this case, Count. The Count operation counts the number of occurrences for each data point. In other words, how many measurements we had for January of 2008 for FirmA, for 2009, same thing. So if I click Count and click OK, I get one measure for each for a grand total of two. Again, it doesn't work that well in this circumstance, but you should use Count later on in case your Pivot Tables contain data that doesn't communicate discrete elements. So I will click Undo. Another way to summarize data inside your Pivot Table is to change how Excel calculates the values, not only by operation but the way it displays the value in relation to other values in the Pivot Table.
I will give you an example. Suppose that I wanted to display the 2009 values as a percentage difference from the values from 2008. To do that, I would click any cell in the data area. Click Field Settings button and then click Options. Then in the Show data as area, instead of Normal I would display it as % Difference From. I can then select the base item from my comparison. In this case, I want to compare 2009 to the previous year, which is 2008, so I will click Previous.
If I had more data, for example, if I had a column for 2010, clicking previous would compare that data to 2009. If I were to select 2008, then the Pivot Table would compare the data in 2009 to 2008 and then it would also compare the data in 2010 to 2008. So make sure that you have your basis for comparison set properly. But in this case, I am comparing percentage different from the previous year, so I can click OK. 2008 disappears because there is no earlier year to which to compare it and 2009 is displayed as a percentage difference from 2008.
Just by way of example, I'll show you another comparison. Go back to the Field Options button. Suppose I wanted to show the data as a running total. So in this case, I would have a running total in. And let's see a running total by month. When I click OK, Excel changes the Pivot Table. And here's what's going on. You'll notice that the values in each column go up continuously. The reason that happens is because Excel adds the value for February 2008 for FirmA to the value from January of the same year.
It then does the same thing for March, for April, for May and so on. You'll notice that the subtotal row is blank. That's because the value in December for 2008 and 2009 is the subtotal. You should take the time to experiment with the summary operations and settings available for use in the data area. You will 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 · 69864 Viewers
80 Video lessons · 127307 Viewers
52 Video lessons · 62306 Viewers
59 Video lessons · 47990 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.