This is a guest post written by my colleague Jon Acampora.
The Problem
When you apply a filter or slicer to a PivotChart the custom formatting can change with each change in the filter. This can be very frustrating and require you to apply the custom formatting to each possible view or slice of the data.
The screencast below shows this problem. I customized the colors of the lines to green and grey for the Year 2011, but the line colors change back to the default colors when I filter the data for 2012. This happens when the data is filtered by a slicer or manually filtered it in the PivotTable.
This post will explain why this happens and present an alternative solution that will save you time when creating PivotCharts with customized formatting. VBA or macros will NOT be needed for this to work, so it should be easy for anyone to implement.
Why Does This Happen?
It’s all about the cache…
Any changes to the formatting of a data series in a PivotChart are stored in a cache inside the Excel file. In the example below I made a few changes to the formatting of the lines in the chart from their default settings. The colors of the lines and the shapes of the markers were changed when the PivotTable was filtered for the Year 2011. As you can see in the screencast, when the PivotTable is filtered for the Year 2012 the line colors and markers change back to the default settings.
Even though the source range of the PivotChart remains the same, the formatting of the chart changes. This is an odd behavior until you understand that the formatting properties are saved and remembered by Excel.
Excel actually stores this data in a cache with all the other chart properties. This means that it remembers the exact formatting I applied for the Year 2011 only, and that is why the colors and formatting change back to my customization when the PivotTable is filtered for 2011.
To see this you have to dive under the hood of the Excel file. I included the XML code for the PivotChart in the sample file (download below). If you search through the code you can see a reference to “2011 – Coffee” and “2011 – Tea”. Below each of those lines are rows of code that contain the custom formatting settings I applied.
Since there is no reference to 2012 or 2013 in the chart’s cache, the default formatting for the chart is applied. I think it’s cool to be able to see the inner workings of the file and try to understand what is happening with the application. You can see the components that make up any Excel 2007+ file by changing the file extension to “.zip”, then opening the folder.
Why does this matter?
This behavior is annoying for the most part. If you click on a slicer or filter, you usually want to see the same custom formatting you applied for the other slices.
But understanding the behavior allows you to either use it to your advantage, or avoid it at design time. I will explain a solution below that allows you to avoid the behavior, but still use the PivotTable filtering and slicers.
It should also be noted that this behavior does not always happen. It depends on the setup of the PivotTable and the order of the row and column fields. If you have a very simple pivot table that only has one field in the column area and one field in the row area, then you might not experience this change in formatting at all. I am still trying to determine the exact parameters of the PivotTable that cause this to happen. Please let us know if you have any insights on this.
Pros and Cons
The advantage of this unique formatting behavior is that it allows you to apply customized formatting for each slice of your data. This could be useful if you want a series to stand out from the rest when the user is clicking through the slices. You could use it as an attention grabber to highlight some important trends.
The disadvantage is that a lot of the time you want all slices to display the same custom formatting. If you have a lot of slices then it becomes very tedious work to go through each slice and reapply the customized formatting. And it will also require maintenance in future periods. For example, when I get next year’s data for 2014 I will have to create custom formatting for that slice of the data.
Simple Workarounds
One way to prevent the formatting from changing is to create a regular chart that references the PivotTable data. With either of the following solutions we want to avoid the PivotChart all together, and instead create a regular chart. Again, the regular charts do NOT suffer from the formatting issues when filters are applied.
Solution #1
This can be accomplished by applying the technique in Jon’s article Making Regular Charts from PivotTables. With that technique you basically create a regular chart and then carefully add the data series to the chart by referencing areas within the PivotTable. You just have to be careful with the areas you select or reference because Excel has a tendency to want to convert the regular chart to a PivotChart.
Solution #2
The other option is to create a new area on the worksheet that contains a replica of the PivotTable. This area just contains formulas that reference the PivotTable. You can either use the direct cell references like (=C9) or the GETPIVOTDATA() function to point to the PivotTable. You might want to use the GETPIVOTDATA function if you only want to display a subset of your PivotTable data in the chart.
The advantage of this technique is that once you have the formula based PivotTable setup, you can reference the entire area of the source data range to quickly create a chart that includes all your series. It might save you time over having to add the series individually as you do in solution #1.
The sample file (download below) contains an example of the technique that I will explain here.
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.
That’s it! You should now have a chart that retains it’s formatting when a slicer or filter is applied to a PivotTable. An example of this behavior can be seen in the screencast below.
This solution works well for simple cases where the size of the data area stays the same every time the chart is updated. If the number of rows/columns in your PivotTable is increasing/decreasing every time you update the data, then this solution might take more maintenance or tweaking to work for you. But the general principle will help get you started and thinking about more advanced solutions if needed.
Jon has a great article on using VBA to create dynamic charts that explains a more advance approach to this issue.
Conclusion
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.
The example file can be downloaded below. Please leave a comment with any questions or suggestions. Thanks!
Download
PivotChart Formatting Changes On Filter Slicer.xlsx (34.7 KiB)
About the Author
Jon Acampora writes the blog at ExcelCampus.com where he is focused on helping users improve their Excel and VBA skills. His contributions include in-depth articles, video tutorials, and free add-ins to help save time and improve productivity. Jon is a newly-minted Microsoft Excel Most Valuable Professional (MVP).
Elazier Barbosa says
Hi Peltier, I liked very much
Regards
Mike Alexander says
I had no idea that the PivotChart formatting was stored in the unerlying XML file. Now that I think about it, I have to smack my forehead. Why wouldn’t it?
Excellent article Acampora!
Jon Acampora says
Thanks Mike! I smacked my forehead more than a few times trying to figure this out. I still don’t fully understand the logic of how the information gets stored.
It depends on how many fields are in each area of the PivotTable, and the order of those fields. I believe that if the child field contains multiple items under a parent field, then the formatting information is stored in the cache. In the example above, the child field (Category) contains multiple items (Coffee & Tea) under the parent field (Year) in the column area of the pivot.
It seems that if this type of relationship does NOT exist, then the formatting is not stored in the cache. This is just a hunch though. I have not done enough investigation (trial & error) to fully figure it out yet. But maybe someone out there knows…? :-)
Edward says
Somehow this e-mail came to my inbox right when I was trying to figure this out. Thanks
Edward says
It looks like this is fixed in excel 2013. You need to be sure to change the line color from automatic to a set color.
Jon Peltier says
Those screen shots look like 2013 to me.
Jon Acampora says
@Edward – I still get the same behavior in 2013, even when setting the line color to “Solid line” instead of automatic. As I mentioned above, the behavior does not always happen and I believe it is based on the order of the fields in the column or row area.
I did some further testing and the formatting can be controlled if the PivotChart is built in the right order. I can get the formatting to remain the same if I remove the Year field from the column area, then format the lines, then move the Year back into the column area. The changes don’t seem to be stored in the pivot cache though, so it seems like the colors could change back to their defaults.
Without knowing the exact logic it is tough to plan for every scenario that could cause changes in the formatting. Thanks
PC says
I would appreciate any advice you can give me in relation to the following.
My final year Thesis is based on the frequency analysis of gunshot sounds. Recordings were taken from a range of weapons and ammunition, with recordings captured left, right and behind the shooter and transformed using ‘Audacity’ Audio Software. The ultimate aim was to determine from an unknown shot the weapon, ammunition and position of shooter.The data was then exported to excel (as attached) in order to be visually charted and used to compare to other gunshot patterns. However, the nature of the noisy data, as well as 3 shot repetitions, the resulting charts for comparison is unsatisfactory. I have tried polynomial and moving average and attempted to express some correlation using error bars, but ultimately I am lost in this area of using this data to obtain confident comparison/identification.
Dawn says
Love this information! I didnt really care how or where the underlying data was stored, I just need a way around it and you showed me! Now my charts are looking beautiful and the Managers I created this for are VERY PLEASED with the end result!!!
Now if I could just figure out how to protect just a handful of cells instead of the whole darn worksheet, life would be PREFECT with my new charts!!!
Jon Peltier says
Dawn –
You could put the data you don’t want changed into separate rows or columns which you then hide, so people won’t know they can edit them. Or you can stick them in another worksheet, either hidden, or visible but protected.
Jon Acampora says
Thanks Dawn! You can apply protection to specific cells in your worksheet.
1. Select any cell and press Ctrl+1 on the keyboard (or right-click > Format Cells with the mouse).
2. Select the Protection tab in the Format Cells window.
3. You will see a checkbox named Locked. If you uncheck this box then the cell will not be protected when the sheet protection is applied.
You can apply this to multiple cells by first selecting all of the cells you want to unlock, then repeating steps 1-3 above.
If there are only a few cells you want to lock, then you can also select all the cells in the workbook and unlock all of them. Then select the cells you want to lock and apply the lock. I hope this answers your question.
Dawn says
Jon and Jon,
Thank you both for your comments…I ended up just hjiding the sheet with the pivot tables on them!!! Works like a dream now!
Thank you so very very much!
A Developer and heavy Excel User says
With respect, this is a bug. It is not a feature. The definition of caching in software development is that it should not impact on the functionality, just increase the throughput. The excel pivot cache fails to do this because now the user has lost the nicest feature of the pivot table which is the amazingly good pivot chart. I would rather not have a chance at all even if it takes a minute to render my chart, rather than have to spend that minute manually copying cell references and suffering from the inherent bugs I would introduce due to human error.
The real solution is very simple: Microsoft should fix the bug.
Carly Kaufman says
I think I was able to find a way around this in Excel 2013. Once you get your custom formatting set, save the chart as a template (http://www.dailymotion.com/video/xzizxs_excel-2013-tutorial-saving-custom-chart-templates-microsoft-training-lesson-28-18_tech). It seems like as long as a custom template is applied to the chart, it doesn’t lose it’s formatting when sliced.
Jon Peltier says
Carly –
Sounds great. Do you have to keep reapplying the custom template, or is applying it one time sufficient?
Carly Kaufman says
John,
It seems to be sticking by just applying it the initial time. It’s only been a few minutes though so I can’t say I’ve fully tested it. I’m using Power BI so I need to see if the formatting holds up when the scheduled refresh occurs.
Jon Peltier says
Carly –
Let us know, because this is a pain to many people.
Carly Fay Kaufman says
So far so good! I’d love to have someone else test this on their machine as well.
Excelisapain says
Hey Carly,
I’ve tested your method, but I still have to reapply the template to the graph. I’m still looking for an easy way around this bug, because I work a lot with secondary axis as well. Those values get reset as well and they’re a real pain to restore to the required format.
Rachel says
This explains yesterday’s struggle. Thanks.
Today’s struggle:
I have a pivot chart based on data collected from a customer survey for 5 different customer bases (EUROPE, SOUTH AMERICA, ASIA, etc).
The pivot chart will reference the same 5 bases each time, but with a different subject. For example:
Satisfaction with Promotional Material, Satisfaction with Innovation of Products, etc.
HOW DO I DEFINE THAT EACH DATA SERIES WILL BE THE SAME COLOR IN EACH CHART!!
Thanks
Jon Peltier says
As well as Carly’s approach with custom chart templates may work in some circumstances, I’ve always found that if I need certain formats, I’m better of writing some VBA to reapply the needed formats directly. No hoping a secondary axis will be faithfully restored by a template. It may require some fine tuning, and a few trips back to the macro recorder, but in the end it’s more predictable.
Jon Peltier says
Rachel –
You could sort the pivot table by some criteria besides values, so the items are always in the same order. Or you could use an approach that reformats an updated chart to assign the right colors based on category names, as described in VBA Conditional Formatting of Charts by Category Label.
Chak Chen says
I would like to download file excel but can’t download why?
Jon Peltier says
When I click on the link (in Chrome), the file starts downloading immediately. In Internet Explorer I am given the choice to open or save the file. Check your Firewall and AV settings.
Max says
Hi,
I was wondering if yo ucould help with the following:
I need to use the pivot chart, for the sake of presentation and also maintenance, since it’s much easier to add/remove data with a push of a “refresh” button.
How can i make the slicers NOT show data that i didn’t select in the filter.
Normally, the slicer shows all data available, and that is fine. But i have 20 people, of whom only 6 need graphs, so i need the slicer to only show those 6, as opposed to making me look through all 20 every time.
Is there any way to do that ?
Reid Farrington says
Jon,
Thank you providing a solution to this problem. I was able to solve my issue using solution #1.
Regards,
Reid
Rain Flanagan says
I think I found a solution to this (at least for me). If you go to Field Settings for all your row labels in your pivot table and select “show items with no data”, the formatting sticks. The problem is that when you use the slicer, you may have row labels that don’t exist for that intersection, then when you change the slicer, they exist again and Excel puts in the default formatting. If they always exist, Excel doesn’t have to use the default.
indzara says
Thanks for sharing. This was useful in understanding the reason for this chart behavior.
Robert says
I can appreciate the time and effort everyone has put into this thread—–
However, none of this happened in 2010. Once you selected a pivot chart and customized it, —- no matter what you selected with a filter, the chart would hold its custom formatting. I am pleased to see the “solutions”, however when you have developed sheets and spent enormous amounts of time to get them set up properly, — building workarounds is not a solution. This is a bug and needs to be corrected.
Jon Peltier says
Robert –
Are you using 2013? Because I think you have a nostalgic but incorrect view of how things used to work in 2010. This pivot reformatting has been the case for as long as Excel has had pivot tables and charts.
erez says
A Really great and helpful article.
Still I’m facing a problem. My data is monthly updated. Some dimentions I have not necessarily hold data of each month. The original pivot table/ charts hides those empty data dimentions.
When I prepare a duplicate set of data to which a new grapg corresponds I get messes up because the data and columns not shown in the original pivot data set still present in the duplicated set od data.
Jon Peltier says
Erez –
In your pivot table, right click on a field with missing values, choose Field Settings, click the Layout & Print tab, and check the box for Show Items with No Data.
Sebastien says
A little late on the discussion, but if you clear the filter on the slicer, the graph has 6 series instead of two.
Alternatively, I changed the chart to have the “Year” field as a page filter in the pivot table instead of in the columns since the intent is to always show one year at a time. Once that’s done, the formatting doesn’t change.
The problem reminded me how in older versions of Excel, if you applied formatting while a page filter was applied, that formatting was problematic when you changed or cleared the page filters.
Nick Watkins says
Thanks for the explanation. I tried it out, and understand this, but it did not solve the problem I am having. I have created Pivot Charts to show the top 10 departments and have them setup to show largest to smallest, and for some reason once I’ve used a slicer it overwrites these preferences and makes my dashboard look messy. The regular chart did the same exact thing. Any suggestions?
Poorni says
Hi , I am facing a trouble in same use case. In my chart , I have a slicer for the Category and when I select only Tea , the series color changes to Green. Can I fix the color for Tea to Grey all the time? Is this possible? I also have a constraint – no VBA. Kindly help!
Regards
Poorni
Ashleigh says
Hi Jon,
Useful article, thanks :) but I’m still haveing trouble. My custom chart type includes a mix of bars and lines. I have actual values in bar charts and targets as lines. Filtering out one of the target fields (which is a line), and then adding it back in to the graph results in the value bbeing added as a bar, not a line. Any suggestions?
Thanks
Jeff Weir says
Good article, Jon A. I’m struggling with a different problem…I’ve used Jon P’s Label Last Point macro on a bunch of PivotCharts, and changed the macro so that the label font adopts the same color as the underlying series. Works fine. Until that is you reopen the file, and the labels are black and white again. So datalabel font color is NOT being saved for PivotCharts it seems.
I’m either going to convert em to regular charts, or simply write some code to cycle through all charts and adopt the series color on workbook open.
matt says
There is an extremely easy way to do this, better than both of your solutions.
Simply by designing the pivotchart with the largest possible dataset you have in the table, when you filter something from your selection the format will stay. Try it out
Eric says
Jon,
I’ve found sucess using Rain’s solution with one additional note. It appears that, as she mentioned, formatting is lost if you filter in a way that no data exists for a particular row/column label.
However, I found that by adding a column in my data source that simply mirrors exactly whichever column I’m trying to slice the chart based on (year in your example), and then basing the slicer off of that mirror column instead of the original, the formatting stays intact. So essentially the chart data should be based off of the original field, while the slicer is based off the mirrored field. Not sure why this works, but it seems pretty consistent.
Kamil says
Hi, is there something else that doesn’t require additional tables. I have big data base and i’m using regular charts with OFFSET (shape of database is also report that ppl will look at so from that reason i don’t have typical dimension that can be used in pivot.
Claude Pelletier says
Thanks Jon for your great presentations and useful solutions, now I can get around this bug from Microsoft. I usually use many various formatting in my diagrams but with Pivot chart it was not possible.
Thanks again.
Jeff Weir says
Download file seems corrupted, unless it’s my PC
Jon Peltier says
Jeff –
It downloaded and opened fine for me, but I don’t know, New Zealand is a lot farther away.
Kamil says
It’s not working in my case. I’m already using normal chart on pivot table. What is it changing for me are labels. I’m missing labels when using slicers (i have created dynamic ranges based on pivot table no of entries displayed after applying filters), only for some entries.. not all of them. I can’t reapply them as it sees for series that we have enabled labels, but if you click on that one entry check box is not marked, but checking it agin does nothing. You have to remove labels completely and add them once again. Seems it’s not pivot itself causing problems but chart freaks out when ranges are changing.
Jon Peltier says
How are the labels applied? The chart may not be able to keep track of which labels go with which points.
In some cases, you can fix this: go to File tab > Options > Advanced. Under Chart, change the “Properties follow chart data point for current workbook” setting. I believe this is checked by default, and unchecking may cause problems with other charts.
John McCreary says
Has anyone tried clearing the pivot cache by cutting and pasting the pivot table to a new workbook and then cutting and pasting it back? This seems to have solved my issue with chart formatting changing when filters are applied via slicers.
John McCreary says
Earlier I posted asking about clearing cache by pasting to another workbook. Initially that work for me but it was not consistent so not a workable solution. But I did come up with another potential solution that seems to be working and consistent. Create the pivot chart and edit with the formatting you want. Save the pivot chart as a template. Now recreate the pivot chart using the template instead of selecting a standard chart (the saved template should be in the Templates folder). Make sure everything matches the original chart (not all formatting gets saved to template). If it does, delete the original.
Jon Peltier says
John –
I didn’t have time to try it, but I didn’t think just manipulating the cache would really help the chart. But using a customized template, without too much default formatting that the chart can revert to unexpectedly, makes more sense to me.
Shanu Singh says
Hello Everyone,
Pivot Chart Formatting Changes When Filtered:
The solution of this problem without VBA code just select the title of pivot table and right click then select the “Field Setting then click on the Layout & Print and click on the check box “Show item with no data last (Do it for all the title one by one) last step just create the new line chart with slicer.
One example : Just right click on the Month (Title) click on “Field Setting” click on the Layout & Print and click on the check box “Show Items with no data” last step create the line chart based on pivot table.
Hope so its easy to do.
Shanu Singh
Hope so this problem solved
Jon Peltier says
I don’t think that addresses the formatting issue.
shillz says
Another way to address this issue if you don’t have any chart and the formatting needs to be done for the Pivot table itself and the slicer just needs to be updated with the format.
First select something from the slicer and click anywhere on the pivot table field ->Now Go to Conditional Formatting->New Rule or Manage Rules->It opens up the window as
Edit Formatting Rule dialog window:
Apply rule to :
1)Selected cells
2)All cells showing “Field” (I used this)
3)All cells showing “Field values” with ……
Now select your condition
For ex: Format only cells that contain greater than “2” Format to Orange.
The slicer format will still remain because you have selected Field instead of specific values.
*If you want to add more conditions simply follow the above and create new rule.
The difference I see is the edit formatting window which doesn’t open up usually if I directly select a field and go to Conditional Formatting.Only by clicking on the slicer and then selecting the field brings this feature.
I will try to put screenshots if this allows me to so you get what i am trying to explain.
Ivan Schiessl says
@Shanu Singh – Perfect solution!!!!
Thank you very much.
Ivan
bs0d says
Bummer, after all these years and new versions of Excel, it would seem we’re stuck with a VBA routine to reapply colors. “Properties follow chart data point for current workbook” seemed to fix the changing chart colors, but would also break my axis labels.
My setup is a clustered stack column chart, referencing a table that refers to cells from a defined table (kind of like a PivotTable). I use a slicer to filter through the data I wish to plot. I didn’t have this problem until I converted the chats to the clustered stack… really weird.
Jon Peltier says
bs0d –
I don’t know how your data or chart are structured. Do you have some categories that are not always present in the output? Is there a way to make them appear, even with blank or zero values?
bs0d says
Jon,
Since many of my charts followed the same coloring scheme, the amount of code was minimal to re-apply colors. Just included some if conditions for the few exceptions.I just tied the code to the event thats triggered when the table filter changes via slicer; so to the user, they don’t even know that colors are being reapplied behind the scenes. I’m executing a lot of other code anyway, like aligning series labels as well. When you have the faux stack going on, Excel doesn’t have the option of displaying labels above the series. My chart design followed your clustered stack column chart article. So I just set to inside end, then move them up X amount. For categories without data, I just use NA().
Steve Halley says
My workaround was to save the format I wanted as a chart template.
Then, I recorded a macro to select the chart template, which is called on Worksheet PivotTableUpdate.
One line of quick and dirty, but works.
I know that I don’t know enough about chart templates to know if it’ll work for someone else if I send them the file ;)
Jon Peltier says
bs0d –
There’s an easy trick to put labels above a stack of columns. See Label Totals on Stacked Column Charts.
Jon Peltier says
Steve –
I’m not a big fan of chart templates, because they never quite do all I expect them to do. But if your chart isn’t too complicated, a template is fine.
And if you can be sure that the person you send the template file to can be relied upon to store it in the correct directory, then the template and your VBA should work for them as well.
bs0d says
Jon –
Indeed a nice trick; would require me to add three additional chart series. I’ll need to test it out and see what the overlapping looks like. I have series: budget, actual +, and actual -. In the code, for example, I can set the actual + to be slightly higher than budget and so on, to help keep labels separate. Though I suppose you could do the same thing by adding a buffer to each hidden dummy series, then tell the chart to show labels from the actual values.
Jon Peltier says
I play all kinds of tricks with labels. This chart below overlays waterfall charts for “Before” and “After”. The labels are attached to hidden series, and the labels use the Value From Cells option, pointing to cells where these labels are constructed. To avoid overlapping labels, the visible text in the labels (the numerical values) are padded at the beginning or end with line feed characters, CHAR(10), to move the text up or down. It took me numerous hours to write formulas that get the padding right.
Peter C says
Thanks for this really useful article and comments Jon et al. I have the same extremely very irritating issue with non-Pivot charts where I’ve spent ages creating a custom chart to display exactly as I want (lots of tedious formatting with error bars etc well away from MS defaults). I then want to duplicate the same chart to display with exactly the same formatting; just pointing to different plot ranges. However, with each series edit to point to a different range all the custom formatting gets undone on that series. The template suggestion helped a little, but it still stuffs up a bunch of formatting. MS *really* need to add a “don’t apply default formatting” option to series edits as it is incredibly annoying and such a huge time waster. I’ve been doing similar for years and don’t ever remember having such a problem in pre-Excel 2013 versions so I reckon it’s a relatively new annoyance.
Jon Peltier says
Peter –
I just published a new tutorial today, Properties Follow Chart Data Point, which describes your problem and presents a solution. Long story short, go to File tab > Options > Advanced, scroll down to Chart, and uncheck “Properties Follow Chart Data Point for Current Workbook”. Now when you switch around your data, your custom formatting won’t move around or move off the chart. When you’re done you can go back and check that box; there are good reasons for the setting to be checked.
Simon Kerr says
Hi Guys
great tutorial and solved my problems….sort of
I think the answer lies in this tutorial on Dashboards and how she inserts the slices. It works better for me
please check it out because it’s beyond my simple mind
Nikita Volkov says
The following solution worked for me:
I make format changes to the pivot chart then, without clicking on slicer or filtering anything, save the file. Close it. Then open it and after that when clicking on the slicers or filtering the format does not change.
Hope that helps.
Yves Seybel says
5 years later and MS Office has not come up with a solution to maintain formatting on Pivot Chart when using slicers. Thanks to all above for the creative work-arounds. Wouldn’t you think that MS could put an end to the insanity?
Danny says
I have a similar issue whereby I have a several pivot charts shows a %score as a result of an inspection these %s have a different colour depending on the %threshold for less than 70% is red, 70-89 is amber red. I apply all these manually save the workbook, when I come to reopen the formatting has changed, I can close it and reopen it straight away and it has changed again. There doesn’t seem to be a common them to why it’s behaving like this????
Jon Peltier says
Danny –
I can’t really say much, other than, “that’s another way it doesn’t work.”
P Cardon says
Bit late to add to this thread, but since this is hairpulling concentrated M$ irritation I thought it might help. Just ran a test based on a tip from a JB ONE on another forum … proceed as follow (expanded on his answer with my test results):
1 – Delete all the pivotcharts – you’ll have to redo them anyway with the proper formatting
2 – Reset ALL your slicers with the button in the top left to make sure your pivottables contain all the data, remove ALL filters on the pivottables
3 – Delete ALL your slicers (if you did not reset the slicers before deleting them pivottables might still be sliced)
4 – Make sure “Preserve cell formatting on update” is ticked in the “Layout & Format” tab of the “Pivottable options” (rightclick on the pivottable) – seems to be important
5 – Make a new pivotchart from your pivottable – DO NOT FILTER or SLICE the data regardless of how crap the pivotcharts look at this point
6 – Modify your pivotcharts with your own formatting – make sure to include everything you want, you will not be able to modify them later on when slicers are put back
7 – When you’re completely satified with your pivotcharts, save your file (they suggested to also restart Excel but it seems to work fine without restarting in my testfile)
8 – Then and only then add the slicers back and link them to your pivottables
9 – If you now slice the data the formatting will be preserved on your pivotcharts
DRAWBACK 1: this forces you to think well about how to present your tabledata and charts beforehand, as any changes made after putting the slicers in will -again- not be taken into account when slicing.
DRAWBACK 2: means you will have to start from scratch again (delete slicers and charts) if you do want/need to change the pivotcharts after all – might still be less work then trying to solve it any other way (VBA)
It might be linked to the XML but as some other people elsewehere suggested it might also be linked to the hierarchy or order in which changes are applied to the objects.
ADDED TIP: if you want to make sure the amount of lines/bars/etc. remain the same in your pivottables/pivotcharts so as not to force Excel to change formatting depending on the amount of data, you have to make sure empty data is also displayed. Right-click a label (column or row header) in your pivottable, select “Field settings”, go to the “Layout & Print” tab and check the “Show items with no data” option. If you used labels in your pivotcharts they will show up as 0 which is handy.
Hope this will help you as it did me. Let us know in the comments…
Marc says
What do you think of this solution? https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/excel-2013-chart-series-formatting-changes-when/f6a3e0d7-e8cb-4a62-9cf1-e5f1728bcb57
Can it apply?
Jon Peltier says
Marc –
That solution won’t help, since it applied to cases when the address of the range being plotted is changed, not the data within the same range.
However, I just discovered a workaround.
Guest author Jon Acampora’s pivot table has the year field in the columns area of the pivot table. The unfiltered pivot chart has six series of data: 2011-Coffee, 2011-Tea, 2012-Coffee, 2012-Tea, 2013-Coffee, and 2013-Tea. When Jon applied custom formatting, he applied it to 2011-Coffee and 2011-Tea. When the slicer selects 2011, these two series are shown, with their custom formatting. When the slicer selects a different year, the series displayed have no custom formatting, so they display the default formatting for the first two series of a chart. But if those series are now given custom formatting, they will display that formatting whenever the slicer returns to their year.
I made a pivot table with the year field up in the filters area of the pivot table. The unfiltered pivot chart only has two series of data: Coffee and Tea. Changing the slicer does not change which two series are displayed. Any custom formatting of the Coffee and Tea series are always displayed since those two series are always displayed.
Long eplanation…
Here is Jon A’s original unfiltered pivot table on the left and mine (Jon P’s) on the right. His has six columns of values, mine has two. There are several pivot charts below each pivot table. The first chart under each pivot table has only default formatting applied: blue for series 1, orange for series two, gray for series three, etc. The second chart has default formatting applied to the first two series: Green for series 1 and red for series 2. The third chart below Jon A’s pivot table has custom formatting applied to all six series: green, red, turquoise, darker orange, purple, and yellow.
Now I’ve selected 2011 in the slicer. Only the 2011 series from Jon A’s pivot table are shown; his pivot table has changed its configuration while mine has not. The two default charts look the same: two series, so the first two default formats are used, blue and orange. Jon’s upper custom formatted chart shows the custom green and red formats, since the slicer selected the year for the first two series which were custom formatted green and red. Same with his second custom formatted chart: the slicer selected the green and red series. My custom formatted chart still shows the green and red formats.
Now, 2012. Only the 2012 series from Jon A’s pivot table appear. The default formatted charts look the same, using blue and orange, the first two default colors. The charts with custom formatting applied to the first two series no longer look the same. The first two series in Jon A’s first custom chart are no longer the ones he applied custom formatting to. Instead, they show have default formatting, blue and orange. Jon’s second custom formatted chart are turquoise and orange, since the slicer selected the year for the third and fourth series. The two series in my custom formatted chart are still custom formatted with green and red.
Finally 2013. The default charts in the first row are the same as ever. The custom charts in the second row are explained the same way as the 2012 view above. The last custom formatted chart shows the custom formatting for 2013, purple and yellow.
Tom says
Hi Jon,
I struggled with this one a lot in Office 2013, I didn’t really want to use separate PivotTables or similar, as I am using a Timeline Slicer and need 10 different charts on the same page.
For me the issue presented itself when changing locations; one location did not contain any data in the category the graph is filtered by and thus presented a blank chart, losing the formatting when data was available again.
My solution was to Check the “Show items with no data” settings under Display in the PivotChart settings. After that, the Charts never went blank, and I stopped losing formatting
I wrote a Macro to make it easier to apply:
I know this is an old thread, and maybe others have figured this out, but I found this thread and I didn’t find a solution, so hopefully this can help someone!
David says
I solved it on my end without having to sacrificing to much!
What i found out was that if the file was saved without a slicer-filter applied, the colors on the charts changed when i opened it again. So i selected each filter that had a representing color in the graph and then saved with one filter applied. To prevent people saving the document with no filter applied i turned on read-only on the document by right clicking on it and under the general tab chose read-only.