One of the most versatile methods of analysing large amounts of data in Excel has always been to insert a Pivot Table that dynamically reflects the changing status of the data.
Once you have created one you can extend its usefulness by adding a chart to make your numerical data display in a visual format that everyone can understand.
What is a Pivot Chart?
A Pivot Chart is simply a chart that is based on information from a PivotTable. It must always have an underlying Pivot Table that calculates the values for the chart, but in the same way as you can manipulate the structure of a PivotTable, you can just as easily change the properties of a PivotChart.
To create a PivotChart just select the PivotChart command on either the Insert ribbon or the Pivot Table Tools:Analyze ribbon.
The Insert Chart dialog box appears.
New Pivot Chart Dialog Box
In this new dialog box, Excel 2013 places a preview of the various types of Pivot Chart it can create in the centre. Various options for changing the type of chart are shown across the top of the dialog box, and on the left are additional options for changing it.
Each sub type of chart displayed across the top of the dialog box can be previewed by clicking on it. When you are happy with the chart, click OK to insert it on the sheet next to the Pivot Table.
The PivotChart now has a new set of controls that allow the data to be modified and filtered, just like a normal chart in Excel 2013. On the right are the two new tabs. The top one controls Chart Elements and lets you control the various elements of the chart like data labels, titles and axes directly:
The second tab controls Chart Styles:
There are two tabs on this dialog box, Styles for changing the pre-set chart styles and Colors for customising the colour palette.
Tabs on the chart itself allow you to add and remove, filter and sort the data the PivotChart is displaying in the same way as PivotTables.
The Pivot Chart Fields are displayed in a task pane on the right of the window by default but this can be detached by dragging it off when it becomes a floating palette:
Pivot Table Chart fields can also be added and removed, sorted and filtered from this area giving a far greater degree of flexibility to the Pivot Chart.