With a pivot chart, you can graphically represent the data summarized in a pivot table. If you know how to create charts in Excel, you’ll face no problem creating and customizing pivot charts. All Excel charting features are available in a pivot chart. In this article, I will show step by step how to create a pivot chart in Excel 2013 but this same method is applicable to other versions of Excel.
Pivot charts can be created in several ways
In Excel you can create pivot charts in several ways:
- Select any cell in an existing pivot table and then choose PivotTable Tools ➪ Analyze ➪ Tools ➪ PivotChart.
- Select any cell in an existing pivot table and then choose Insert ➪ Charts ➪ PivotChart.
- Choose Insert ➪ Charts ➪ PivotChart ➪ PivotChart. If your cell pointer is not selecting any cell within a pivot table, Excel will prompt you for the data source and will create a pivot table and a pivot chart skeleton. You will create a pivot chart placing the fields in proper areas in the PivotChart Fields task pane and the pivot table will be generated automatically.
- Choose Insert ➪ Charts ➪ Pivot Chart ➪ PivotChart & PivotTable. If your cell pointer is not selecting any cell within a pivot table, Excel will prompt you for the data source and will create a pivot chart and a pivot table skeleton. You will create a pivot chart placing the fields in proper areas in the PivotChart Fields task pane and pivot table will be generated automatically.
Learn how to create a pivot chart in Excel 2013 (Step by Step)
A pivot chart example
The following figure shows part of a table that contains daily sales by region. The Date field contains dates for the entire year excluding weekends, the Region field contains the region name (Eastern, Southern, or Western), and the Sales field contains the sales amount.
Although we can create a pivot chart without a pivot table, I find it easier to create a pivot table first, then creating a pivot chart from this pivot table. The following figure shows the pivot table created from this data. The Date field is in the Rows area, and the dates have been grouped into months. The Region field is placed in the Columns area. The Sales field has been placed in the Values area.
You can easily interpret a pivot table rather than raw data. But the easiest way to spot trends is using Pivot Chart.
To create a pivot chart, select any cell in the pivot table and choose PivotTable Tools ➪ Analyze ➪ Tools ➪ PivotChart. The Insert Chart dialog box will appear, from which you can choose a chart type. In our example, we have used a Line with Markers chart and then clicked OK.
Excel creates the pivot chart shown in the following figure. From the charts we can easily conclude that the Western division has an upward sale, the Southern division has a downward trend, and Eastern Division has relatively flat sales.
A pivot chart includes field buttons. Using these field options, you can filter chart data. You can remove some or all of the field buttons. To remove select the pivot chart and use the Field Buttons control in the PivotChart Tools ➪ Analyze ➪ Show/Hide ➪ Field Buttons(Upper Part).
When we select a pivot chart, the Ribbon displays a new contextual tab: PivotChart Tools. The commands in the Design and Format tabs holds all those Excel chart properties, so you can manipulate the pivot chart in many ways.
If you modify the pivot table, the pivot chart will adjust automatically to display the new summary of your data source. The following figure shows the pivot chart after I have changed the Date grouping to quarters.
More about Excel Pivot Charts
These points are important when you are using pivot charts:
- A pivot table and a pivot chart are joined in a two-way link. If you make any kind of structural or filtering changes to one, the other is also changed.
- When we activate a pivot chart, the PivotTable Fields task pane will be changed to the PivotChart Fields task pane. In the PivotChart Task pane, Legend (Series) replaces the Columns area, and Axis (Category) replaces the Rows area, and Values are the same for both task pane.
- The field buttons in a pivot chart contain the same controls as the pivot chart’s field headers. These controls allow you to filter the data that’s displayed in the pivot table and pivot chart. If you make changes to the pivot chart using these buttons, those changes are also reflected in the pivot table.
- Say you have a pivot chart linked to a pivot table and you have deleted the underlying pivot table, the pivot chart will remain. The pivot chart’s Series formulas contain the original data, stored in arrays.
- By default, pivot charts are placed in the worksheet that contains the pivot table. To move the pivot chart to a different worksheet (or to a Chart sheet), choose PivotChart Tools ➪ Analyze ➪ Actions ➪ Move Chart.
- It is possible to create multiple pivot charts from a pivot table. You can manipulate and format the charts separately. However, all the charts display the same data.
- If you select a normal chart then it will show the icons to the right: Chart Elements, Chart Styles, and Chart Filters. In contrast, a pivot chart does not display the Chart Filters icon.
- Slicers and Timelines also work with pivot charts.
- We can choose Page Layout ➪ Themes ➪ Themes to change the workbook theme. Your pivot table and pivot chart will both use the new theme.
Download this sample file to practice yourself
Happy Excelling 🙂