Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When most users think of a Pivot Table, they usually visualize it with a single data set in the data area. While that's certainly the most common data area configuration, you can also summarize more than one data field at a time. In this worksheet, for example, I have Company and Month in the row area and Year in the column area. There is a single data field and that is Revenue. If I want, I can add a second copy of the Revenue data field to the data area. To do that, I go to the PivotTable toolbar and drag the Revenue field to the data area. And Excel adds a second version of the Revenue field.
It's titled Revenue2. If I want to make any changes to that second Revenue field, I can do so by clicking any cell in that field and then, on the PivotTable toolbar, clicking the Field Settings button. I can then click Options and use the second data field as a means to compare the value in the first data field to other values in the Pivot Table. So, for example, if I wanted to show the difference between 2009 and 2008, I could go into Show data as, click Difference From and than click (previous).
When I click OK, Excel displays the difference between 2008 and 2009 for each month. In this case the difference between 67 and 128 is, in fact, 61. Difference between 69 and 131 is 62, and so on. That just provides a visual reference so that you can make informed decisions about your data. If you were to show the difference as a percentage, which you can do by clicking Field Settings, and using % Difference From. Click OK and the difference appears as a percentage. Another way to use multiple data fields would be to have a column in the data source that contains the number of sales and display it next to the Revenue for each month.
Pivot Tables can get a bit crowded when you display more than one data field in the data area, but if you use the space wisely you can gain a lot of insights by viewing two or more data status simultaneously.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86682 Viewers
80 Video lessons · 135989 Viewers
59 Video lessons · 54724 Viewers
52 Video lessons · 68620 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.