Study smart | Excel superstars
How to make PivotCharts
Once you’ve mastered PivotTables, the next step is to make PivotCharts
Words Traci Williams, excelace.co.uk
In the last issue, we explored PivotTables and how they summarise data in a variety of different ways. The downside to them (if we had to find one) is that they can appear to be a table with lots of data and numbers, yet they still need some work to pick out the important information.
This article will show you how to create a PivotChart, which can display all the data from the PivotTable in chart form, providing perfect visibility and clarity.
Getting started
Firstly, create a PivotTable like this one below:
We can then create a PivotChart that is connected to it, as follows:
- Click anywhere on the PivotTable
- Go to the ‘PivotTable Analyze’ ribbon and select ‘PivotChart’:
- You will then see a list of chart options to select from:
NOTE
The charts in the red box cannot be created with data from a PivotTable, so we can only use the charts in the green box.
- Click on the different charts to see previews of what they will look like first. Once the selection has been made, click ‘OK’ to confirm.
- The chart will appear (as an object) within the current sheet. In this example, this is a line chart:
As the chart has been selected (notice the white dots around the object), the field list on the right has changed to ‘PivotChart Fields’, the ‘Column’ box has changed to ‘Legend (Series)’ and the ‘Row’ box has changed to ‘Axis (Categories)’.
Don’t be put off by this terminology; we can still use the simple drag and drop technique that we did with the PivotTable.
This simply means that if we want to change the chart, we can do that directly from the chart, without having to go back to the PivotTable. We can continually make changes until the chart appears the way we need it to be.
In this example, I have amended the PivotChart by removing the ‘Product’ field from the ‘Legend (Series)’ box. This is how it looks:
Although I amended the field in the ‘Legend (Series)’ box of the PivotChart, it has also applied the same change to the PivotTable (by removing the field from the ‘Columns’ box).
This is because the PivotTable and PivotChart are the same entity; they purely display the results differently (table v chart). It is impossible to have the PivotTable include something that the chart doesn’t, and vice versa.
A PivotChart must be stored in the same file as the PivotTable, but it does not have to be stored in the same sheet.
TOP TIP
Cut and paste multiple PivotCharts into their own sheet to create a Dashboard
Refreshing a PivotChart
The great thing about a PivotChart is that it will update dynamically as the data in the PivotTable changes. This is not possible with regular charts (unless they are linked to tables or dynamic named ranges).
We can either refresh the data via the PivotTable or PivotChart, and both will automatically update (as well as any others that are connected to the same source data). Do this by selecting the PivotTable or PivotChart, right-click and choose ‘Refresh Data’.
Formatting a PivotChart
With PivotCharts, there are three extra toolbars that appear at the top of the screen:
From the ‘Design’ toolbar, there are several pre-defined ‘Styles’ to choose from:
Simply hover over each to see how this will appear on your chart and left-click to select.
The ‘Change Colors’ icon will display different colour palettes that can be selected.
‘Add Chart Element’ displays a list of various items that can be added to charts:
Each option is simple to use, and you can see a preview on your PivotChart as you hover over each item.
In this example, I have removed the ‘Legend’ and added a data table (without legend keys):
The grey boxes appear within the PivotChart either to signify what the data is (Quantity) or they can be used to apply filters (Sales Person) to the data directly from the chart. If you prefer to remove these (I often do), you can do this as follows:
- ‘PivotChart Analyze’ ribbon > ‘Field Buttons’ > make relevant selection (or ‘Hide All’).
Any number formats will usually mirror the formatting from the PivotTable too.
TOP TIP
Once you have set the formatting on one PivotChart, it can be copy and pasted to another. Click the outside edge of the formatted PivotChart and press ‘Ctrl + C’ (or right-click, ‘Copy’), then select the outside edge of the second PivotChart and press ‘Ctrl + V’ (or right-click, ‘Paste’).
Other key things to note
It is also possible to create a PivotChart directly from the raw data, without creating the PivotTable first. This will automatically create the PivotTable. It’s impossible to have one without the other.
Conclusion
PivotCharts are often a simpler alternative to creating standard charts, purely due to the ability to drag and drop fields where you want them.
The downside, of course, is that we are unable to use all of the chart formats that are available with standard charts.
However, the dynamic nature of PivotTables, and how they can adapt as data expands (or constricts), is usually the winning factor, as it provides greater flexibility and simplicity.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What is a key advantage of using a PivotChart instead of a regular chart?
Note: PivotCharts dynamically update whenever the linked PivotTable data is modified.
2
Which of the following chart types is NOT available as a PivotChart in Excel?
Note: Scatter charts are not supported as PivotCharts; they require a regular chart.
3
How can you filter data in a PivotChart?
Note: PivotCharts have field buttons that allow users to filter data directly from the chart.
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).