Pivot Chart Formatting Changes When Filtered
treats the subject in depth.
It explains that Excel actually stores formatting data in a cache with all
the other chart properties. This means that it remembers the exact formatting.
When the data is refreshed, Excel invalidates this cache, so that the
default formatting for the chart is applied.
It offers a solution where a new area on the worksheet is created that
contains a replica of the PivotTable, containing formulas that reference the
PivotTable, using either the direct cell references like (=C9) or the
GETPIVOTDATA() function to point to the PivotTable. The GETPIVOTDATA function
is preferable for the display of a subset of the PivotTable data in the chart.
This is done in two steps.
The first step is to recreate the PivotTable data by creating formulas that reference the pivot. This can be done in cell adjacent to your PivotTable or on a separate worksheet. Just remember to leave enough blank rows/columns between your PivotTable and formula based table in case your PivotTable expands when filters are applied/removed.
Step two is to create a regular chart using the new formula driven table as the source of the chart. When the PivotTable is filtered or sliced, the formulas will automatically be updated and display the new numbers from the PivotTable. The chart will also be updated and display the new data.
The article concludes with :
Adding slicers to your PivotCharts and PivotTables is a great way to make your presentation interactive. PivotCharts allow you to link your chart to a data source so they can be refreshed dynamically with little maintenance. However, PivotCharts display some odd behavior when filtering charts with custom formatting. Understanding this behavior and planning for it at design time will save you time and frustration.
An example presentation can be downloaded from
PivotChart Formatting Changes On Filter Slicer.xlsx.
The author also recommends the article
Dynamic Chart using Pivot Table and VBA
with the approach of using VBA to create dynamic charts using a more advanced approach (too long to include here).