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.
Excel lets you create PivotCharts that summarize your PivotTable data visually. You can often use more than one type of chart to summarize a dataset so you might want to change your PivotChart's type. I'll show you the procedure to change a PivotChart's chart type and in doing so, I'll give you an overview of the different chart types that are available within Excel 2010. First, I should point out that you can't create an XY scatter chart, a stock chart or a bubble chart using PivotTable data. This first chart type, which is the column chart, displays data as vertical columns organized by category and these charts are useful for showing data changes over a period of time or for illustrating comparisons among items.
Now let's say that I want to change this chart to a line chart. To do that I'll click the PivotChart and then on the Design contextual tab click the Change Chart Type button, and then I can click the type of chart I want to create and I'll click the first line subtype, and with that selection in place I'll click OK, and Excel creates a new graph based on my choice. Line charts display continuous data over time which is perfect for showing trends in data as long as the data was captured at equal intervals.
Comparing a month sales to a year sales doesn't make sense within the context of a line chart. Pie charts show the share of a total contributed by individual data values in a single data series. So let's change this chart to a pie chart. So on the Design contextual tab Change Chart Type, click Pie, and I'll use the first pie subtype, and click OK. Pie charts show the share of a total contributed to by the individual data elements in the series. If you have more than one data series in your PivotTable, Excel displays just the first one in your PivotChart.
So let's say for example that I have two companies, FirmA and FirmB, and I will add those companies to the Legend Fields area. In this case, Excel only shows FirmA. If I were to add the Company field to the Axis Fields area though by dragging from the Legend Fields to the Axis Fields area, then Excel would create four different divisions: 2009 FirmA, 2009 FirmB, and the same for 2010. Now I'll remove company from the PivotChart to go back to our simple data type and I'll create a bar chart.
So click the PivotChart and the then on Design tab, Change Chart Type and then click Bar. I'll use the first type and click OK. Bar charts which display values as horizontal bars are perfect for summarizing data when the axis labels are long and the values that are shown are durations. So even though I have sales data here, most of the time you'll use bar charts for times, so for example in project management. The next type of chart I'd like to show you is what's called an area chart, so I will change the PivotChart to that type, click OK, and what you see is an area chart that instead of a line chart, the entire body of the graph is filled in.
So in this case we have the total from 2009 to 2011. Now let me show you what it looks like when I add company back to the Legend Fields area. So I'll drag the Company field there. What this area chart does is emphasize the magnitude of change over time, and also how much each data element contributes to the total for a given measurement. Now the final two chart types that I'd like to cover don't work extremely well with the data that I have in this PivotChart, so bear with me and I'll just explain the concepts. The next type of chart is what's called a surface chart.
So I'll change the data type to Surface and I'll just select the first one. Surface charts are used mostly for scientific data. You can use surface charts to find the optimum combination of two datasets such as comparing rainfall and the crop production. Now finally, radar charts enable you to compare the aggregate values of several data series. So I will change the type to a radar chart and click OK and then I will add Company to the Axis Fields area, so you can see the relative contribution for 2009 from these revenue, 2010 FirmA, FirmB in 2010, and FirmA in 2009.
Selecting the best PivotChart type to summarize your data will add clarity to your presentations. You should take the time to study the type of data you're analyzing and use that information to select the most appropriate PivotChart.
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.