A PivotChart is a graphical representation of data in a PivotTable. A PivotChart can help users make some sense of the data. Most elements, such as categories, data series, and axes, work the same as in standard charts, but a PivotChart has interactive filtering controls in the chart that help users to quickly filter and analyze the data.
The PivotChart and its associated PivotTable must reside in the same workbook, and changes made to one will affect the other. You can create different types of PivotCharts from a PivotTable, but filters applied to one will affect the others. You can format the data in each individual chart, but remember that applied themes will affect the entire workbook.
NOTE for MacOS Users: PivotCharts are not currently available in Excel 2016 for Mac. Mac users should move ahead in the materials to Using the Yes/No Field to Create Subtotals.
Creating a PivotChart
We will create a column PivotChart that compares the number of passing scores to the number of failing scores. We will first collapse the rows to see only the totals. Then we will create a chart based on these totals.
Step1. Switch to the Analyze tab on the Ribbon, if necessary,
Step2. To select a cell in the PivotTable,
Click in cell E5
Step3. To collapse the rows, in the Active Field group,
Step4. To begin creating a PivotChart, in the Tools group,
Step5. To choose the default Clustered Column chart,
Step6. Switch to the Design tab.
Step7. To begin moving the chart to a new worksheet, in the Location group,
Step8. To move the chart to a new sheet,
Click the New sheet radio button
Step9. To rename the worksheet, in the New sheet field, type:
PF Chart Enter
NOTE: You can convert a PivotChart to a standard chart by deleting the associated PivotTable.
Updating a PivotChart Using the Filters
Our current PivotTable and PivotChart are showing the total of passing and failing grades. To illustrate the link between the PivotTable and PivotChart, let's see what happens when we expand one of our totals to see the individual scores.
We will switch to the PivotTable.
Step1. To switch to the PivotTable,
Click the TestScores tab
Step2. To expand the Fail label,
Step3. To switch to the PivotChart,
Click the PF Chart tab
Step4. To disable the passing scores, in the lower left corner of the PivotChart,
Click, Click the Pass checkbox
Step5. To confirm this change,
Step6. To view the changes in the PivotTable,
Click the TestScores worksheet tab
Step7. To see all of the test scores, in the Row Labels field,
Click, Click (Select All), Click
NOTE: When PivotCharts are linked to PivotTables, the links (and filtering) will transcend different programs; for example, if you have linked a Pivot Chart into Word or PowerPoint, it will still be linked to the Excel Pivot Table.
Step8. To save the workbook, press:
Step9. To close the workbook,
Click the File tab, Click Close