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. Only the very motivated and patient ones, I’d say. 'if no path specified then default folder: C:\Users\yourumbuddy\AppData\Roaming\Microsoft\Templates\ChartsĬht.SaveChartTemplate Replace( & "_" & & ".crtx", " ", "_")ĬhtActive.Appl圜hartTemplate Replace( & "_" & & ".crtx", " ", "_") Sub SaveChartTemplate(cht As Excel.Chart) 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. Another routine applies a template to the active chart, assuming it can find one that meets the same SheetName_ChartName convention. It names the template with the worksheet and chart name to avoid errors from having charts with the same names on different sheets. One of the routines below saves every template in the active workbook to the templates folder. Hey presto, there’s my template with the most recent changes. Then should my changes get wiped out, I avail myself of the “Change Chart Type” command. My practice is to save early and often to the same distinctively named file: The dialog saves to Excel’s Templates>Charts folder by default. The pivot table uses all three fields in the source data: Date, Sales, and. Change can be displayed as the numeric difference (this example) or as a percentage. In the example shown, a pivot table is used to show the year over year change in sales across 4 categories (colors). The first is the “Save as Template” command: Pivot tables have many options for displaying value fields.
I'm on a Mac and using excel 2011, any idea how to change this text: right click on the graph, go to select data source, then click on whichever series you want to change. This is driving me crazy, but I need to change the series names on a pivot table chart. Looking around the web some more, I found two commands that help me as the chart developer. Rename Series1 values in Pivot Table Chart. 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. However the two solutions listed there have a huge drawback: they eliminate the use of pivot charts. When I first encountered this issue my hopes were raised by the presence of a long Jon Acampora post on Jon Peltier’s blog. Unchecking the field doesn’t undo the changes. 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. Look at what happens when I add and remove a field: However, if I so much as resize a column in the source pivot… much-less-nice formatting. Here’s an example – a chart with two axes, different chart types and non-standard colors. 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. 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. 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.