I’m still wrestling with pivot charts, and boy are my metaphorical forearms big! Seriously though, I just recently became aware of the crazy problem of pivot charts losing their formatting. I’ve got a bunch of pivot charts with two x axes and other embellishments, and pretty much any change to the chart or the source pivot can erase all the carefully applied formatting. In this post I’ll outline a couple of ways to decrease, but by no means eliminate, the pain of losing your pivot chart formatting.
The Problem
Here’s an example – a chart with two axes, different chart types and non-standard colors. I’m quite pleased with its looks.
However, if I so much as resize a column in the source pivot… much-less-nice formatting.
It gets worse. Look at what happens when I add and remove a field:
One axis is eliminated without so much as a “by your leave,” the line graphs revert to columns and the colors regress to garish defaults. It’s a mess. Unchecking the field doesn’t undo the changes.
A Partial Solution
When I first encountered this issue my hopes were raised by the presence of a long Jon Acampora post on Jon Peltier’s blog. However the two solutions listed there have a huge drawback: they eliminate the use of pivot charts. Talk about throwing the baby out with the bath water! In the post’s comments a couple of people think they’ve found ways to make the formatting stick, but these didn’t work for me.
Looking around the web some more, I found two commands that help me as the chart developer. The first is the “Save as Template” command:
The dialog saves to Excel’s Templates>Charts folder by default. My practice is to save early and often to the same distinctively named file:
Then should my changes get wiped out, I avail myself of the “Change Chart Type” command.
Hey presto, there’s my template with the most recent changes. Yay!
VBA Automation
I wrote some VBA to automate this stuff. One of the routines below saves every template in the active workbook to the templates folder. It names the template with the worksheet and chart name to avoid errors from having charts with the same names on different sheets. Another routine applies a template to the active chart, assuming it can find one that meets the same SheetName_ChartName convention. Of course even if you rename or move a chart you can figure out what its template was saved at and apply it using the Change Chart Type command.
Here’s the code:
Dim chtActive As Excel.Chart
If Not ActiveChart Is Nothing Then
Set chtActive = ActiveChart
SaveChartTemplate chtActive
Else
MsgBox "No Chart Selected"
End If
End Sub
Sub SaveAllChartTemplates()
Dim ws As Excel.Worksheet
Dim chtObject As Excel.ChartObject
For Each ws In ActiveWorkbook.Worksheets
For Each chtObject In ws.ChartObjects
SaveChartTemplate chtObject.Chart
Next chtObject
Next ws
End Sub
Sub SaveChartTemplate(cht As Excel.Chart)
'if no path specified then default folder: C:\Users\yourumbuddy\AppData\Roaming\Microsoft\Templates\Charts
cht.SaveChartTemplate Replace(cht.Parent.Parent.Name & "_" & cht.Parent.Name & ".crtx", " ", "_")
End Sub
Sub ApplySavedTemplateToActiveChart()
Dim chtActive As Excel.Chart
If Not ActiveChart Is Nothing Then
Set chtActive = ActiveChart
chtActive.ApplyChartTemplate Replace(chtActive.Parent.Parent.Name & "_" & chtActive.Parent.Name & ".crtx", " ", "_")
Else
MsgBox "No Chart Selected"
End If
End Sub
Does this work for End Users?
Only the very motivated and patient ones, I’d say. If needed though, I think you could attach code like the above to events and maybe create something that would help them retain formatting as they pivot the charts.
I say if the baby is the spawn of evil (as pivot charts are), then it’s OK to throw it out with the bath water.
Hey Dick. I used to hate pivot charts because they were so clunky, but when I got this project which used them they seemed so much better. However, it is evil to have something with two wonderful features, very flexible pivoting and almost endless formatting possibilities, AND THEN HAVE ONE THING BREAK THE OTHER. Oh well.
Even the bathwater is dirty, when it comes to PivotCharts, which is why I use my code at http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/ to base charts off. I see you have similar: https://yoursumbuddy.com/create-pivot-table-named-ranges/
I might go crawl down a rat hole and look for a smarter solution.
What happens if you email the file to someone else? I’m guessing this breaks down. Can you email me the file to weir.jeff@gmail.com so I can test this?
Hi Jeff. What is it that breaks down? If you mean the formatting, I haven’t seen that. If you mean the saved templates, then yes they’re not there, but you can generate them. If you mean something else entirely, well then maybe. At any rate I’ll send the file I based the screenshots on.