Pivot Chart Refresh cancels all chart formatting

[Nico]

Hello folks,

I am using pivot chart with calculated fields, on which I did some quite
sophisticated formatting (combination chart stacked bar / dot plot with
secondary axis ; bar & plot area & grid colors ; labels ; axis & chart
titles).
When I click the "refresh" (!) button to update the data, the chart
formatting is lost and the chart reverts to the default formatting (stacked
bar chart with all series)
Is there any way to "memorize" the chart formatting on a pivot chart ?

TIA

 

[Debra Dalgleish]

Loss of formatting is a known problem with pivot charts. There's
information in the following MSKB article, which suggests recording a
macro as you apply the formatting:

Changing a PivotChart removes series formatting in Excel
http://support.microsoft.com/?id=215904

 

[ShaneDevenshire]

Hi Nico,

You can beat some of the formatting problems with tricks, for example, if
you color the series the color always reverts to the default - there is a way
to beat that problem without using VBA. If you want data labels for each of
the series - they are lost on refresh, but there is a way to beat that also.

Another possible way to eliminate the problem is to no use a pivot chart.
That means that you need to create a non-pivot chart from your pivot table
data, this can work in some situations depending on what you are doing.

However, maybe the best way to beat the refresh problem without using code
is to use Excel 2007 or (2004 on the MAC).