Remember the chart type, chart formatting (and template) on Pivot Charts
When you're charting a pivot table and have a combo chart - say stacked bar chart with a line chart - and you filter your pivot table so that one of the stacked bar elements disappears, the chart replaces the missing element from the line chart. Remove the filter and your line chart is still part of the stacked bar chart.
I'd like the pivot chart elements to remain in their place even when there's nothing to show because of the filter.
Additionally, any formatting applied via Chart Templates to Pivot Charts is lost on Pivoting, etc. - this formatting should be maintained.
(2016-07-29 Dan [MS]: updated title and description to address full issue of Pivot Charts losing layout/formatting on filter/pivot)
Thanks for the feedback. This is a known issue which we have concrete plans to fix. However, please continue to vote on this issue.
Analista X commented
I spent years wondering how to preserve the design applied to the dynamic chart. I ended up finding how to preserve it simply and directly.
I recorded a video explaining how to preserve this formatting.
It's May 2020 when I'm leaving this comment here. Seems there is still NO solution to this known issue or bug.
I'm adding my fury and frustration to the massive wave of angry voices behind me. THIS IS UNACCEPTABLE!! The product is BROKEN, apparently has been for YEARS, and yet my company and many other are making MONTHLY PAYMENTS for the privilege!
Is this fixed? I am having the same issue.
I am using D65 and the issue still prevails. Please fix this issue asap!! This is very annoying.
I've put a lot of effort to create a dashboard which can not be used now because all my combo charts turns back to default stacked chart which make no sense to the user.
Hugo Petey commented
Have just come across this issue and I agree with everyone's comments. What is the point of putting in all of the time and effort to create pivot charts, only to refresh and lose all of the structure and formatting. What is even more disappointing is the mention of a concrete fix, when it is almost 4 years down the line, it doesn't sound concrete to me, more like custard.
surely?! Have had to result to Tableau. The whole purpose of a pivot chart is to cut and splice data easily for viewing. Why bother when it reverts to a completely different chart type!
Monica Sun commented
It's 2019 now, any updates?
Thomas Wylie commented
Wow, 3 1/2 years later and the "concrete" to fix this problem still hasn't been poured?
I found a workaround is to make sure your pivot table has the same number of columns for each filtered selection. The easiest way to achieve that is to massage your data first by pivoting on the dimension you have as columns and then un-pivoting it (I suggest using Power Query to do that). This process creates zeros for records that would otherwise be suppressed.
For example say you have columns for dimension Size with headings Large, Medium and Small.
Now if page filtering results in only records for Large and Small your pivot chart will be reset as column Medium is now suppressed from the pivot table.
You have to ensure each filtered selection will have values for each heading. Creating zeros for those records did the trick for me.
Is there a fix to this problem? I would love to know what it is.
JOHANNA PRUSSMANN commented
Count me in as one more frustrated user. Why would the formatting go back to presets after you just spent sooooo much time and effort in organizing the formatting and layout of the chart? *Sigh*
I was modeling a new process in our company just to find out all was for nothing...doesn't help to view loads of numbers in a pivot table were a chart with option to filter would do so much...
Excel team - you must take care of this issue asap!!
Acknowledging the frustration of all others, the only solution I find useful at the moment is to switch to Power BI (by importing the created Excel workbook contents) where the problem does not occur.
So I just spent two weeks modelling and building an interactive revenues data model and dashboard for a client only to find out that famed Power Pivot Charts and slicers are actually useless.
Been having the same issue on any, more complex dashboard with more than three distinct slicers as you always eventually end up with an empty chart, or few, and they all then default to the same format.
Totally unacceptable, that it's been years with no update. I rather MS removes slicers from pivot charts altogether and don't mislead re their superior functionality.
Dan Schiller commented
I just began connecting my Excel spreadsheet to Tableau (as much as I have been avoiding having to do that for this and other quirky design reasons) and I'm using that as my dashboard now instead. It's a shame though because it's is such an odd issue that's not being addressed.
known issue without any known plan to fix.. =_______=
Are you kidding me? We are in August 2018 and this is still unresolved after so many years of frustration and complaints? Yes, MSFT please fix!
We are approaching three years since we've heard anything from Scott Ruble (MSFT) on the alleged 'concrete plans to fix' this continues to be a non-starter for certain development...forcing many to remain on 2013 which was more stable with this same issue than 2016 is. Come on MSFT, give us an update!.
I found the formatting becomes more stable when you have only one label column