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

Question

I have 4 pivot charts that rely on data that is refreshed from a connection.

When I click refresh all I lose all the formatting I set up (colours/borders/line and bar and 2nd axis choices)

• I have already unchecked Properties Follow Chart Data Point for Current Workbook.
• I have also tried Right Click on Data > Refresh per data table but I get the same issue.
• Preserve cell formatting on update is ticked for all charts.
• Invert if negative option ticked/unticked doesn't make a difference
• Preserve cell formatting on update I have tried unticking, then ok, then right click options and re tick, still didnt work..
• I have saved the chart format as a template then after refresh re applied but formatting is still lost.

Version:

Excel 2016 MSO (16.0.4738.1000) 32-bit

Solution 1

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

Solution 2

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.

Solution 3

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.

Solution 4

I just found a solution to the issue of the Pivot Table changing formatting even if "preserve formatting on update" is selected.

I got rid of all subtotals, then selected the cells I wanted to format, formatted them, then put the subtotals back in. Voilà ça marche. Will see if it continues to do so.

Solution 5

The article 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.

Step 1

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 2

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.

Conclusion

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

View more solutions

Comments

[harrymc]

Is Making Regular Charts from Pivot Tables a useful solution?

[Matt]

@harrymc Thank you for your input, unfortunately this would only benefit not having pivot tables and a pivot chart from data and just the chart, but this is not the issue.

[harrymc]

I found some report that says that it helps to open the pivot table options and unticking "preserve formatting", closing the option box, then reopening it and selecting again "preserve formatting" (link). There is also the complicated Solution #2 from this article.

[Matt]

@harrymc Let me try that now

[Matt]

The tick and untick didnt work

[harrymc]

There is left Solution #2 above.

[Matt]

Its a pivot chart, not a pivot table, nevertheless this is ticked on everything.

[Rajesh Sinha]

@Matt,, Preserve formatting on update setting somewhat resolves the issue, as that is what I see as well in my tests.

[Rajesh Sinha]

@Matt,, invert if negative option must be checked in a pivot chart. Or write this VBA Code in Immediate Window. Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection(1).InvertIfNegative = True

[Rajesh Sinha]

@Matt,, check the post now I've edited the Answer so far will work !!

[Matt]

Invert if negative option doesn't work

[Rajesh Sinha]

@Matt, it was the possible and tested solution keeps the Format for the Chart ,,, otherwise i think no solution I can find for it !! Better once you try with VBA Code

[Rajesh Sinha]

@Matt, I can suggest you two options. 1st is save the Chart as TEMPLATE & Then after if you loose the Format. Open the File, select the chart, Right Click and Select Command Change Chart Type & from menu Select the TEMPLATE.

[Rajesh Sinha]

Cont,, 2nd is for the above shown method I can suggest you VBA (Macro),,, confirm which one works for you !!

[Matt]

Cant recreate the combo chart stacked bar and line i have with this method

[harrymc]

Have a look at the example presentation.

[Matt]

Yeh i can create a line/bar chart, but not a combo

[Matt]

I have saved the chart format as a template then after refresh re applied but formatting is still lost.

[Rajesh Sinha]

@Matt,, please follow the steps from EDITED 2 properly it will click, I've already tested it.

[Matt]

I will try this now

[Matt]

Thanks for the extra information, I ended up just using a BI tool.

[Daniel]

I just wanted to add the part in parenthesis in step six were crucial for me. These steps did not work without the step of reopening Excel after saving, but it worked wonderfully with that step. This is in Excel 2016.

[Aspiring Developer]

Thanks, I encountered this same issue with format painter, was driving me insane. Format painter does NOT work on pivot tables for retaining the format.