Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
When most users visualize a PivotTable, they see it with a single data set in the data area. While that's certainly the most common data area configuration, you can summarize more than one data field at a time. So let's say for example that I have a monthly revenue worksheet for two firms and I already have the Revenue field in the data area. So if you look in the PivotTable Field List, you can see the Revenue field there and then you can also see the same data in the body of the PivotTable. Now let's say that I want to create a PivotTable that displays the revenue data for each month and I also want to show the same data but as a comparison.
Say for example, if I wanted to compare the values between FirmA and FirmB as a percentage. To do that I can add a second copy of the Revenue field to my data area, and to do that click any cell on the PivotTable and then on the PivotTable Field List drag the Revenue field header down to the Values area. Again, when I drag it down, even though the Revenue field is already there, if I drag the Revenue field header down to the Values area and release, then Excel creates a second copy of the Revenue field in that area, and you'll notice that the PivotTable changes.
I have Sum of Revenue, and then I have Sum of Revenue2. And then also in the Column Labels area in the PivotTable Field List task pane, you'll see that I have a Sigma values header. What that means is that for the column labels to create room for that second data set, they had to create a second values column. Having two copies of the same data summarized in the same way doesn't really help us, so what we need to do is change how one copy of that data is summarized. To do that you can right-click any cell in either of the two data areas.
So in this case I have Sum of Revenue2. I'll right-click a cell in one of those columns, point to Show Values As, and then I'll do percentage of, and then we'll make it a percentage of the previous month. So right now our Base field is Month. That's correct, and then I'll change the Base Item to (previous) and then click OK. So the value in January is 100% because we are comparing it to itself, but then the value in each successive month is compared as a percentage to the value from the previous month.
So for example, 69 is 102.99% of 67, 128 is 185.51% of 69, and so on. Now you don't have to have two copies of the same field in your data area. So for example, if I were to remove the second Sum of Revenue field, dragging it back to the Field area, I can also add another numerical value such as, for example, the number of sales and that data is in the Sales column. So I'll drag it down to the Values area.
And now I have the revenue for each month, and then I also have the number of sales. So for example, I could now determine that I had $67,000 of revenue based on this number of sales. That information gives me a lot more insight into my sales performance and also it allows me to perform other calculations that I'll show you how to do in a movie later on in this chapter.
There are currently no FAQs about Excel 2010: Pivot Tables in Depth.
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.