Pivot Chart Legend/values Slicer?

[moose79]

Ive got a pivot line chart, with multiple values represented through lines. Is there a way to make a slicer for the values/legend? Id like to turn some lines on/off without having to remove/add them in the values area.

 

[moose79]

I don't think it is possible. But for anyone in the future here is a handy VBA workaround:

Use Slicer to select value fields for Pivot table or PowerPivot! - E90E50fx

Use Slicer to select value field, measure for Pivot table or PowerPivot

sites.google.com

 

[Jon Peltier]

It depends on the original data layout. Below are two layouts that product the same pivot chart.

Layout 1 is already cross-tabbed (also called unstacked), so it has each plotted series in its own column or field. Since a slicer can only allow selection between elements within a field and not between fields, you cannot have a slicer for the series (legend entries) in the chart. This matches the layout of your original data, so you can't have a slicer for your legend entries.

Layout 2 is stacked. All values are in a single column (field) and are identified by elements in another column (field), called "groups" in my example. Since the values are all on one field, and the legend entry names are all in another, you can create a slicer for group, which will allow filtering of the series within the chart. You could rearrange your data into this layout to provide the slicer you want.

 

[moose79]

It depends on the original data layout. Below are two layouts that product the same pivot chart.

Layout 1 is already cross-tabbed (also called unstacked), so it has each plotted series in its own column or field. Since a slicer can only allow selection between elements within a field and not between fields, you cannot have a slicer for the series (legend entries) in the chart. This matches the layout of your original data, so you can't have a slicer for your legend entries.

View attachment 66264

Layout 2 is stacked. All values are in a single column (field) and are identified by elements in another column (field), called "groups" in my example. Since the values are all on one field, and the legend entry names are all in another, you can create a slicer for group, which will allow filtering of the series within the chart. You could rearrange your data into this layout to provide the slicer you want.

View attachment 66267

Thanks for the response Jon. Its a decent workaround that I may try for my next data set. Ill just have to structure my data source differently I suppose.

I did find a VBA workaround too, if anyone else runs into a similar issue.

Use Slicer to select value fields for Pivot table or PowerPivot! - E90E50fx

Use Slicer to select value field, measure for Pivot table or PowerPivot

sites.google.com