Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
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.
The real power of a PivotChart comes to the fore when you rearrange your data dynamically. A task that would take several minutes if done by hand takes just a few seconds when you summarize your data using a PivotChart. So now let's say that you have a PivotChart and you want to pivot it. You can pivot either the PivotChart itself or the PivotTable behind it. So let's start out by pivoting the PivotTable, which is on the left side of the screen. I currently have the data laid out by Year and Month along the rows, which is along the horizontal axis on this line chart, and then Company which provides the FirmA and FirmB lines.
So there are two separate lines. Each one represents a single company. If I were to remove Company from the Column Labels area by dragging it out then Excel creates a chart where it summarizes all of the data instead of separating it out by company. Now let's say that I want to create a separate line for each year. So in other words I want to have January, February, and March and then I want to have two separate lines representing the years 2009 and 2010. To do that I can drag Year from the Row Labels area to the Column Labels area and Excel creates a graph with those two separate lines.
If you have your PivotChart on a separate sheet within your workbook, then you can pivot the PivotChart on its own. So let's say for example that I click the PivotChart and when I do we still see the PivotTable Field List but now the names of the areas have changed. So instead of the Column area we have what's called Legend Fields and then we have Axis Fields, which was previously the Row Labels area. Every unique value in any field within the Legend Fields area will have a separate line or bar or column in the body of the chart.
So for example here we have Year and because I have data from 2009 and 2010, I have two lines, one red, one blue, summarizing data for 2009 and 2010. Now if I want to pivot the PivotChart I can do so normally. So for example, if I want to take a year and put it back in the Axis Fields area, I can do so and create a line chart which summarizes all revenue for 2009 and 2010 by month, and then I can add Company back to the Legend Fields area to create two separate lines, one for FirmA and one for FirmB.
Also as with a PivotTable you can defer the layout update so if you have a large PivotChart or a large PivotTable and it takes time to summarize all the data then you can check it Defer Layout Update and then make your changes and I'll just change back by removing Company and putting Year in the Legend Fields area. You'll notice that when I made those changes the PivotChart and the PivotTable did not change, but when I click Update, everything updates to reflect my changes. If you want to go back to live updating to have the PivotChart and PivotTable update as you make your changes then you can uncheck the Defer Layout Update box.
Changing the PivotChart's arrangement shifts the data's emphasis, enabling you to examine the data from different perspectives quickly and easily.
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.