Dynamic Selection from Multiple Value Fields in Excel Pivot Chart

Question

Is there a method for dynamically choosing the value fields displayed in an Excel pivot chart?

E.g., Suppose I have the data below and produce a pivot table with multiple value fields. Each of those value fields is a different display of "Members" (1. Members themselves, 2. Percentage difference from initial, 3. Percentage difference from previous month). Notice that, effectively, only the first value field is displayed below because the second and third value fields are percentages. So I'd like to dynamically choose whether to plot value field 1, 2, or 3 rather than having them all plotted at once. I'm imagining some functionality similar to a slicer or filter for data values but, in this case, for the entire value fields themselves. Any ideas? If not, I guess I'll settle for three separate pivot tables/charts. Not desirable, but it's something.

Answer 1

Consider using a regular chart with dynamic ranges instead of a pivot chart.

Cell G2 has data validation with a list of these values Members,Initial,Month and has been named as DropDownSelect. Create two named formulas:

chtLabels   =Sheet2!$A$4:INDEX(Sheet2!$A:$A,COUNT(Sheet2!$A:$A)+4)
chtSeries   =OFFSET(chtLabels,0,MATCH(DropDownSelect,Sheet2!$B$2:$D$2,0))

Insert a blank chart, add a series and for the series values enter

='Sheet1'!chtSeries

Set the chart labels to

'Sheet1'!chtLabels

Now you can select a different value in the dropdown and the chart will show that data.

Answer 2

As well as teylyn's excellent answer, you can use VBA to achieve the same thing. See my solution at https://stackoverflow.com/questions/32761634/converting-multiple-variables-into-values-with-excel-pivot-tables-or-power-pivot