PivotTables are a great way to summarise your data, but PivotCharts can be a pain in the, um, neck.
PivotChart Restrictions
- 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:
- chart_actual
- chart_budget
- chart_axis
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.
Please Share
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.
stephanrs says
Hello Mynda, love these blog articles, especially your inspirational XL dashboards.
1 Question:
Dynamic Chart Defined Name/Formula to display “2 ROW” HEADER from Dynamic Table, instead of the conventional 1 row hdr, in Excel 2003? Hence just in formula not Pivot Table/List, the purpose is to exclude dynamic combo box selection with ‘field blanks’ as pictured here:
http://www.hkrebs63.karoo.net/files/SIC.jpg.
File ZIP: http://www.hkrebs63.karoo.net/files/DynamicTable&Chart_EDIT.zip
Files within ZIP:
Dynamic/Interactive Dash: UKSOC2016##.xls. Dyn Data Table Tab: Table1. Dyn Range: D289:F571
Dynamic Chart: DynamicChartRanges#VERT.xls.
OccupationsSubGroupUK Tab shows Dynamic Chart that includes column spaces, hence how to edit seperate dynamic chart file to exclude column blanks within already established formula Dynamic Table1 D289:F571
Oddly thought this would be easy, but Range/Axis edits don’t work for desired results and can’t find any 2 ROW HEADER Dynamic Chart examples.
Devised successful formula method for 1 ROW HDR as Pivot Tables/Lists seem incoherent in Excel 2003, full details & file attached in this post:
https://www.myonlinetraininghub.com/excel-forum/dashboards-charts/dynamic-chart-from-dynamic-table-2-row-header#spEditFormAnchor
Catalin Bombea says
Hi Stephan,
The best solution is to merge the headers into a single row, multiple headers are simply not right, no excel tool will work with multiple headers: Power Query, Power Pivot, Pivot Tables, defined tables.
Catalin
Carlos Sanchez says
Hi Mynda,
Thank you for the great hint.
Could you please give me some directions?
I frequently prepare charts for different customers, each Excel file may contain 12 to 30 charts. Currently I manually type the customer’s name in the header of each chart, how can I build a macro or to automate this process so and have all headers done automatically once I do the first?
Thank you in advance
Carlos Sanchez
Mynda Treacy says
Hi Carlos,
I’d use a dynamic text label and link all labels to one cell in your workbook.
Mynda
Paul S. says
Love the idea of doing a chart off a “regular” table based on a pivot table. Gives a lot more options of what you can do.
I also love the way you did the budget part of the chart – the solid border and no fill. I tried it on one of my charts. I get the side borders, but I cannot get the top border to show over top the actual numbers. What is the secret to getting the top border to show for the budget?
Thanks!
Mynda Treacy says
Hi Paul,
Glad you found this post useful.
To get the budget border to show on top of the actual columns you need to make sure the Actual series is at the top of the list and budget is below. In step 6 above you can see in the image that Actual is first, then budget.
To change the order of your series simply right-click the chart > Select Data. That will open the dialog box you see in step 6. Then use the up/down arrows to rearrange the series order.
Let me know if you get stuck.
Mynda