Create Dynamic charts alongside your PivotTable Using PivotChart

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Using Pivot Charts is the most intuitive and simple way of creating and managing graphs based on raw data which you want to aggregate and analyse. A normal Excel chart created using the traditional method (through the Insert tab or F11 [shortcut key]) will display each row (or column) of data as a separate chart element which is fine for very small datasets or summary data but when you have your data in raw format (Like in the Topic Example Workbook), rather than summarising it first and then producing a chart from your summary, it is much better to have a chart based off your raw data in the format of a PivotChart.

This is because it’s far more dynamic – once a Pivot chart is created, you can aggregate, slice and filter the data to view it in the way you want just like you do with the Pivot Table. This can be done on the fly – directly on the graph/Pivot table itself. And once the Pivot Chart is created you have the same options as you do for any chart such as changing the chart type, the title, Data labels and the legend.

Creating a PivotChart is exactly the same process as creating a pivot table except for when you click Insert -> Pivot Table you need to select ‘PivotChart’ from the drop-down menu instead. Note that this will create a chart in addition to the Pivot table, so any time you want to summarise your data with a Pivot Table if you think an accompanying graphical analysis will also be useful then select this option instead of just creating a pivot table by itself.

Once you have done this you will be presented with the same dialog box as when creating a pivot table – select the data range and the location of the Pivot Table/graph. All the Column headings will appear in the Field List and you can drag them into the appropriate buckets depending on what you want to analyse by. Note that when you have the graph selected (rather than the Pivot Table) the ‘Column Labels’ bucket shows as ‘Legend Fields’ and the ‘Row Labels’ bucket shows as ‘Axis Fields’, though they relate to the same thing. For more details on what these are see the topic Summarising data using a Pivot Table.

In the screen shot below, we’ve created the same analysis as we did in the Pivot table example and the Pivot table displayed is identical – it just shows the graph relating to the Pivot Table view next to it.

To change the data that the graph is based on, simply change the Pivot Table to reflect the analysis you require. For example, the Pivot table and Graph both currently show data for New Items only; to change this to show all items, either change the ‘Status ID’ drop-down filter to ‘All’ or remove it as a Report Filter. To show the maximum shipping fee that has been applied against each state (rather than the sum of all shipping fees by state), just change the ‘Value Field Settings’ to summarise value field by ‘Max’ rather than ‘Sum’. For more detail on this see [Calculating, Filtering and Drilling down in Pivot Tables]

To filter the data you can right click on the PivotChart and select ‘Sort and Filter’, or alternatively you can apply filters to the Pivot Table itself.

Clicking into the PivotChart that has been created will give you a new ribbon at the top called ‘PivotChart Tools’ which gives you all the normal chart functionality to apply to your PivotChart.

When you create a PivotChart the default type created is a bar chart but it’s easy to change this – just click on Change Chart Type (the first option in the PivotChart Tools ribbon) and select the type you want. For a description of all the available chart types and which is most suitable depending on the layout of your data click here.

To change the title of the graph, just click on the existing title, right click and select ‘Edit Text’. By Selecting ‘Font’ from the right click menu instead you can change the size and colour of the text, and by selecting ‘Format Chart Title’ you can give a fill or border to the text box containing the title.

To show the values corresponding to the bars/areas on the graph, right click on the chart itself and then select ‘Add Data Labels’. You can change what the data labels look like by right clicking on the chart again and selecting ‘Format Data Labels’. For example, to show the label as a percentage of the total instead of the actual number click on ‘Percentage’ under the ‘Label Contains’ section. A common requirement with data labels is to not show for zero values. To do this we can [Apply a custom number format] to them – click on ‘Number’, select ‘Custom’ in the Category and then in the Format Code textbox type 0;0;;@ and then click Add. Select this new number format and data labels will disappear where the value is zero.

The other formatting option with charts is the Legend – one is created for your row labels of the Pivot table by default but you can change or delete it. To delete it, right click on the legend and select ‘Delete’, to change how it looks right click on the legend and select ‘Format Legend’. Here you can give the legend a fill or a border.

In the Topic Example Workbook, Create a pivot Chart in a new worksheet(with the source as the data in the ‘Data’ worksheet) by following the steps outlined above.

The Pivot table should show the sum of shipping fees by payment type across the top and state across the left, and should only show items with a status of ‘New‘.

Change the Pivot chart to show the graph as a pie chart (for a single payment type) – practice changing the payment type filter on the Pivot Chart to show the breakdown by state for different payment types. Also add percentage data labels on the pie chart.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.