Pivot Chart Formatting Changes When Filtered

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.

Pivot Chart Formatting Changes by Filter Slicer Excel

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.

Excel Pivot Chart Source Range Unchanged on Filter

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.

Excel PivotChart Cache XML

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.

Excel PivotChart Formulas Linked to PivotTable

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.

Excel Create Regular Chart to PivotTable Linked Formulas

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.

PivotChart vs Regular Chart Formatting Changes Slicer Excel

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

Download PivotChart Formatting Changes On Filter Slicer.xlsx PivotChart Formatting Changes On Filter Slicer.xlsx (34.7 KiB)

About the Author

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

 

Peltier Tech Charts for Excel

Comments

  1. Elazier Barbosa says:

    Hi Peltier, I liked very much
    Regards

  2. 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!

  3. 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…? :-)

  4. Somehow this e-mail came to my inbox right when I was trying to figure this out. Thanks

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

  6. Those screen shots look like 2013 to me.

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

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

  9. 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!!!

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

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

  12. 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!

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

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

  15. Carly –

    Sounds great. Do you have to keep reapplying the custom template, or is applying it one time sufficient?

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

  17. Carly –

    Let us know, because this is a pain to many people.

  18. So far so good! I’d love to have someone else test this on their machine as well.

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

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

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

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

  23. I would like to download file excel but can’t download why?

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

  25. 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 ?

  26. Reid Farrington says:

    Jon,

    Thank you providing a solution to this problem. I was able to solve my issue using solution #1.

    Regards,
    Reid

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

  28. Thanks for sharing. This was useful in understanding the reason for this chart behavior.

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

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

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

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

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

  34. 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?

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

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

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

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

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

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

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

  42. Download file seems corrupted, unless it’s my PC

  43. Jeff –

    It downloaded and opened fine for me, but I don’t know, New Zealand is a lot farther away.

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

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

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

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

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

Trackbacks

  1. […] Acampora explains how to keep consistent formatting in a pivot chart, when filtering it with […]

  2. […] document.write(''); Zeptune – You might also check out this post, on my blog by my colleague Jon Acampora: Pivot Chart Formatting Changes When Filtered […]

  3. […] con raggruppamento nel report https://www.youtube.com/watch?v=OMrHN1tUb8U filtro Pivot Chart Pivot Chart Formatting Changes When Filtered – Peltier Tech Blog VBA per grafici e chart di Excel The VBA Coding Guide For Excel Charts & Graphs Willy […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0