Saving and Reapplying Pivot Chart Formatting

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.

pivot chart

However, if I so much as resize a column in the source pivot… much-less-nice formatting.

pivot chart after pivot column resize

It gets worse. Look at what happens when I add and remove a field:

pivot chart formatting loss

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:

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:

save as template dialog

Then should my changes get wiped out, I avail myself of the “Change Chart Type” command.

change chart type command

Hey presto, there’s my template with the most recent changes. Yay!

change chart type dialog

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:

Sub SaveActiveChartTemplate()
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.

5 thoughts on “Saving and Reapplying Pivot Chart Formatting

    • 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.

    • 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.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>