PivotTables help you summarize large datasets efficiently. But it can be difficult to interpret data when all you have to go on are the raw numbers. Charts summarize data visually, making it easier to distinguish groupings and trends in your data. Just as you can create charts based on regular worksheet datasets, you can create dynamic charts called PivotCharts from the data contained in PivotTables. There are two ways to create a PivotChart. You can either create a PivotTable and a PivotChart at the same time or you can create a PivotChart from an existing PivotTable.
To create a PivotTable and a PivotChart at the same time you need to make sure that your source data such as what I have here is laid out as a data list or preferably an Excel table. Then on the Insert tab, click the PivotTable button's down arrow and click PivotChart. When you do, the Create PivotTable with PivotChart dialog box opens. You can then verify that Excel has identified the range properly and in this case it is a table named Table 1 and you can choose where to create the PivotTable and PivotChart and I will create it on a new worksheet.
With those selections in place, click OK. After you create your PivotChart, you can arrange the fields using the controls in the PivotTable Field List task pane just like you would for a PivotTable. So for example if I want to display yearly revenues, I can create a column chart doing exactly that. Now I'll show you how to create a PivotChart based on an existing PivotTable. To do that, you display a sheet that contains a PivotTable. So I go to Sheet3, click any cell in the PivotTable, and then you simply create a chart as you would normally in Excel.
You click the Insert tab and then select the type of chart you want to create. In this case I will create a column chart, so I'll click the Column button and then just create a simple clustered column, and when I do Excel creates a chart that reflects the organization of the PivotTable. And again you can pivot the PivotTable to change the PivotChart. So for example, if I pulled the Month field out of the Axis category's area then I would have a column chart with data for FirmA and FirmB in 2009 and 2010.
Now there are some differences between regular Excel charts and PivotCharts. The most important ones are that you can't switch the row and column orientation of a PivotChart by using the Select Data Source dialog box. That so much of a problem though because you can always rearrange your data by pivoting the PivotChart. Second, you can't create xy scatter charts, stock charts or bubble charts, and finally refreshing a PivotChart removes trend lines, data labels, error bars, and a few other less common settings.
If you'd rather have your PivotChart reside on a separate worksheet from the PivotTable, you can click the PivotChart and then on the Design contextual tab click the Move Chart button and then select where you want the chart to go. In this case we'll put it on a new chart sheet and we'll just leave the names Chart1 and click OK. I personally prefer a charge sheet because the PivotChart takes up an entire sheet and the larger area makes the chart easier to understand. Pivot charts enable you to summarize your data visually providing an overview of your data and opening the door to insights you might not discover from looking at the raw numbers.
You'll find that Pivot charts are powerful tools that help you analyze your enterprise's data effectively.
Author
Released
4/19/2011- Formatting data for use in a PivotTable
- Connecting to an external data source
- Refreshing a data source
- Adding, removing, and positioning subtotals and grand totals
- Creating a calculated field
- Grouping PivotTable fields
- Clearing and reapplying PivotTable filters
- Applying field styles
- Formatting cells
- Creating a PivotChart
- Printing PivotTables and PivotCharts
- Creating relationships between tables in a PowerPivot model
- Using the DAX language for advanced summaries in PowerPivot
Skill Level Intermediate
Duration
Views
Related Courses
-
Excel 2010: Tips, Tricks, and Shortcuts
with Dennis Taylor3h 43m Intermediate -
Excel 2010: Macros
with Dennis Taylor2h 44m Intermediate -
Excel 2010: Charts
with Dennis Taylor3h 38m Intermediate -
Excel 2010: Financial Functions
with Curt Frye2h 18m Intermediate
-
Introduction
-
Welcome52s
-
-
1. Creating and Pivoting PivotTables
-
Introducing PivotTables4m 2s
-
Creating a PivotTable4m 20s
-
Pivoting a PivotTable3m 47s
-
Configuring a PivotTable3m 22s
-
Managing PivotTables3m 26s
-
-
2. Summarizing PivotTable Data
-
Creating a calculated field2m 27s
-
Grouping PivotTable fields3m 17s
-
3. Sorting and Filtering PivotTable Data
-
Creating a custom sort order2m 48s
-
Formatting slicers3m 43s
-
4. Formatting PivotTables
-
Creating a PivotTable style4m 37s
-
5. Applying Conditional Formatting to PivotTables
-
6. Creating and Manipulating PivotCharts
-
Creating a PivotChart3m 29s
-
Pivoting a PivotChart3m 5s
-
Filtering a PivotChart3m 45s
-
Formatting a PivotChart3m 35s
-
Changing a PivotChart layout3m 14s
-
-
7. Printing PivotTables and PivotCharts
-
Printing a PivotTable4m 2s
-
Printing a PivotChart1m 55s
-
-
8. Manipulating PivotTables Using Macros
-
Running a macro5m 57s
-
9. Getting Started with PowerPivot
-
Introducing PowerPivot2m 8s
-
Importing PowerPivot data3m 14s
-
Managing table columns4m 1s
-
-
10. Working with DAX Expressions
-
Introducing the DAX language2m 58s
-
Using DAX operators4m 44s
-
Surveying DAX functions2m 40s
-
Using aggregate functions4m 24s
-
-
Conclusion
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating a PivotChart