Log In
Register
Home
New Member
May 17, 2020
Offline
Hello,
I am attempting to create regular excel charts from pivot tables using dynamic named ranges, as I find regular charts more flexible to use than PivotCharts.
My goal is to have dynamic excel charts that change with the Pivot Table when it's filtered by a slicer.
I followed Mynda's tutorial here: Regular Excel Charts from Pivot Tables
Using the INDEX function for the dynamic ranges, I was successful in creating the chart and having the series data auto adjust when I filtered by a slicer.
However, I am running into the issue where the series title does not adjust with the slicer.
Please see the attached example.
It's a distilled example of call center data I'm working with. When I click the slicer to filter the chart by call language type (English or Other), the line values change accordingly, but series title always says "Calls Rcvd," instead of "English" or "Other."
I am at a loss how to fix this so the series titles also adjust accordingly. Is the formula for the dynamic ranges incorrect?
Any assistance would be much appreciated.
Thank you!
Gene
July 16, 2010
Offline
Hi,
Welcome to our forum.
The dynamic named range formulas should all count a column that will always contain data, in your example that would be the dates in column A. i.e. they shouldn't count the values in columns B:D because there could be instances where there are empty cells.
I'm not sure why you don't just use a Pivot Chart for this. There is nothing special about this data that requires a regular chart and it's only causing complications because you want to turn on/off series in the chart.
However, if you must use a regular chart then you should think of each series as 1, 2 and 3 as opposed to specific names (all calls, English & Other) because the first series (in column B) will depend on what is selected in the Slicer. You need to edit the series names so they reference the column labels in the PivotTable, rather than being hard-keyed. This way they will update as the Slicer filters the PivotTable.
That said, you will have blank series in the chart legend when you don't have all 'Types' selected in the Slicer. There is no workaround for this.
Mynda
New Member
May 17, 2020
Offline
Thank you so much Mynda.
I appreciate your response and help.
The data I posted was just a simplified example of the issue I was having.
My actual data is really lengthy, which is why I need pivot tables to quickly summarize it, but there are a myriad of different charts (ie. scatter plot, waterfall, etc.) I'd like to create to visualize it, and Pivot Charts are proving to be really rigid and tedious to use.
However, I need these charts to dynamically update with the pivot tables, but once the slicers are selected and the pivot table adjusts, I noticed my charts all went awry.
I will follow your suggestion to prevent this.
Thank you again for sharing your expertise and knowledge with us.
Gene
1 Guest(s)
