Creating a PivotChart

A PivotTable will give you a huge amount of flexibility when analysing tables of data. When tables contain a lot of data, however, information can be overlooked. It is often easier to look at charts and visualisations than to search through rows and columns of data. A PivotChart allows you to quickly visualise the data in a PivotTable.

To insert a PivotChart, select a cell in your PivotTable and then click PivotChart from the PivotTable Analyze ribbon. Excel will open the Insert Chart window for you to select the appropriate chart for the job. Now, not all the charts listed in the window are available for PivotTables. For example, if you click on a scatter plot, sunburst, or map, you will be told that you cannot create these with from PivotTables. While charts such as column, line, radar, or combo are fine.

In this example, we want a column chart as we’re trying to compare the sales performance by product, and a column chart is perfect for that job. Select the required chart and click OK.

PivotTables are limited to making PivotCharts in particular formats, like columns

The PivotChart is connected to the PivotTable. Any updates to the data source or changes to the order or filter of a PivotTable will be carried through to the PivotChart.

Changes to the PivotTable are reflected in the PivotChart

Adding elements to a PivotChart

Excel's Chart Elements settings can be accessed by pressing the "+" button located beside the top-right corner of the PivotChart. Here you can add or remove chart elements such as the chart title, data labels, legend and more.

Tailor how your PivotChart is presented by accessing the Chart Elements

These Chart Elements options are the same as you would be given for standard Excel charts. So, if you have experience with charts in Excel, this will be familiar to you. In our chart, the legend is redundant, so we may as well remove that element from the chart.

Data labels are always popular. Let’s add data labels to the chart (outside end) and remove the gridlines and primary vertical axis.

Modifying the chart title

Our PivotChart title is pathetic and needs some improvement. To edit the title, click the title and start typing. The text will appear in the formula bar and transferred to the PivotChart title when you press Enter. The following image shows the updated PivotChart.

A titled PivotChart showing the total sales in Doncaster for each fruit nakes the data easily comparable

There is no need to have the PivotTable and PivotChart on the same worksheet (but remember, you need the PivotTable so don’t delete it.)

You can cut and paste the PivotChart, slicer and anything else onto their own worksheet to create a clean dashboard away from staging PivotTables and other calculations.

Improving the visualisation

There is much more that can be done to perfect your chart. This is your representation of your data, so don’t take the task lightly. Think about your use of colours, labels and spacing to make your data story clear.

Want to explore more about Power Pivot and other Excel data analysis tools? Check out Alan Murray’s 4-hour course, Data Analysis with Excel.