Excel chart formatting lost when Refresh All or individual Right Click on Data > Refresh

@Matt - same as you, none of the solutions above worked for me either. What was more frustrating was that I had two Pivot Chart/Tables in the same file, both linked to the same Power Pivot data model, one would maintain its custom formatting but the other would not. Thus I knew it was very unlikely related to my version of excel or a specific bug.

The article linked by harrymc contains the key clue to resolving this, but not using the workaround - it's the XML. I was at the point of comparing the XML for the two Pivot Charts but then thought there may be a formatting hierarchy at work here.

My solution:

1. Delete any dependent Pivot Chart(s) (You're starting from scratch)
2. Delete ALL slicers and remove ALL filters from the Pivot Table.
3. Ensure that 'Preserve cell formatting on update' is ticked (this won't solve the issue directly but seems important)
4. Add a new Pivot Chart but DO NOT filter or slice the data in any way regardless of how bad the chart may look at this stage.
5. Apply the custom formatting.
6. Save the file (a user in another forum suggested exiting and restarting Excel - which I did out of desperation!)
7. Now add in the filters/slicers to create the desired chart.

I was then able to refresh/slice/filter the data and the custom chart formats were all preserved.

What worked for me was clicking on 'Value Field Settings' on the specific field, 'Number Format' in that popup, and then setting the formatting there. After refreshing it, it kept the formatting.

Don't use the Format Painter to paint from another table. Set up one header how you want in the new pivot table, then format paint it across the other headers.

tldr: format painter (for some reason) doesn't work across pivot tables linked to a power pivot model.

To keep the formatting when you refresh your pivot table, do with following steps:

1. Select any cell in your pivot table, and right click.

2. Then choose PivotTable Options from the context menu.

3. In the PivotTable Options dialog box, click Layout & Format tab.
4. Then check Preserve cell formatting on update item under the Format section.
5. Finish with OK to close.

Now, whenever you format your Pivot Table and refresh it, the formatting will not be disappeared any more.

Edited 1:

You may try these :

Invert if negative option must be checked for Pivot Chart Options.

Or you may write this VBA Code in Immediate Window.

Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection(1).InvertIfNegative = True

Note: Sheet, Chart & Series number are editable.

Edited 2

Another possibility is,,

1. Select the Plot Area, Right Click and select command Save as Template".

Whenever you loose the Chart Format, reach to Excel, File Select the graph.

2. Right Click and select Change Chart Type.

3. Select the Template from the Chart type poping up Menu.

You find all those lost Formats on the Selected Chart applied previously.

N.B.

Above shown process can be implemented through VBA (Macro) on a Chart or an all Charts also.