Hide data series on legend depending on values

[hannahbarton]

I have a line chart where the data behind uses formulas to populate the values. Sometimes there aren't values for a particular month so I've use the N/A# error to stop these plotting. However, I'd like to be able to remove certain data series from the legend if EVERY value is N/A#.

I've seen some solutions where users are suggesting text boxes as opposed to the actual chart legend, but this really doesn't work for what I am trying to produce (can explain why if necessary).

Some VBA would be brilliant! I can use the basics of VBA but am a complete beginner so please can replies be idiot-proof

Excel Version = 2013

(I've found SIMILAR questions through some extensive searching but none which quite give an adequate response. Sorry if this is counted as a repeat question though!)

Thank you in anticipation!!!

 

[Steve=True]

Hi Hannah,

I would strongly advise doing this with a pivot table or table filters/slicers:

Here is how you can do it for a pie chart, but the same principles apply:

how-to-easily-hide-zero-and-blank-values-from-an-excel-pie-chart-legend

Let me know if it helps.

Steve=True

 

[hannahbarton]

Hi Steve
Thanks for your response!
It's actually a table looking up values from a pivot table (using getpivotdata). It's pretty longwinded but it was a lot easier for me to do it like that due to the way I needed to manipulate the data from SQL. So my slicers are throwing me off... they work as normal (if the slicer option isn't selected, it doesn't appear in the pivot table, the new data table for the chart shows n/a therefore not plotted) it's just the legend that's still there!
Hannah