PivotTables are a great way to summarise your data, but PivotCharts can be a pain in the, um, neck.
- They’re not as customisable as a regular Excel chart
- They only play nice with data from one PivotTable
- If you spend considerable time formatting them they’re likely to remove all your hard work when you refresh the PivotTable/Chart (BTW this is a known bug)
Download the Workbook
How to Create Regular Excel Charts from PivotTables
A while ago I showed you how to create Excel charts from Multiple PivotTables. And this is great if your data needs arranging into contiguous cells so it can be plotted as one series.
For example, the Actual series in the chart below consists of values from both PivotTable1 and PivotTable2, likewise the Budget series:
However, if your data is in two separate PivotTables (because each PivotTable has a separate data source), and each PivotTable represents an entire series in your chart, then you can skip the Manual Chart Table and simply reference the PivotTables using Dynamic Named Ranges.
Note: I don’t recommend using the chart colours above. I simply used yellow and blue so you could more easily follow the data trail.
Tip: remove the Grand Total columns and rows from your PivotTable as you don’t need them in your chart and they will only interfere with your dynamic named ranges.
Dynamic Named Ranges in Charts
Once you’ve set up your Dynamic Named Ranges you need to insert them in your chart. I’ve set up the following dynamic named ranges:
Set up your chart:
- Insert an empty chart by selecting any empty cell > Insert tab > Column Chart (or whatever chart type you want)
- Right-click the chart > Select Data
- In the legend entries side of the dialog box click ‘Add’:
- I’ll add the Actual series first. So, in the Edit series dialog box cell H7 contains my series name and my series values are the dynamic named range chart_actual. Note: you must prefix the dynamic named range with the sheet name enclosed in apostrophes and an exclamation mark on the end e.g. ‘Dynamic Ranges’!
- Repeat for the Budget series
- Now add the dynamic named range for the axis labels by clicking ‘Edit’ under Horizontal Axis Labels:
Now you’re ready to format your chart as you wish.
And the best part is when you refresh your PivotTable and it expands/contracts your chart will automatically adjust, just like a Pivot Chart only better.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.