I have 40 series of data in an excel line chart and each data point is set as na() by design so that no data actually displays initially.
A user can activate a series by clicking a checkbox (which is changing the na() to actual numbers in the reference table)
When a series becomes active, I would then like the legend to display for that series only. Currently, the legend shows all 40 series even when no data is displayed.
Any help is greatly appreciated.
To my knowledge there's no way to have the series be setup on the chart and not show in the legend. So you have two ways you can go, one being a bit more of a hack. Both require using VBA,so if you don't want to/can't use VBA you'll just have to make peace with the legend being filled.
The hack first, place rectangle shapes over each series' label in the legend and set to 0 transparency, no border, and a fill that matches whatever color your chart is. You can then have a macro fire off the check box that resizes the rectangle covering that legend entry such that its height & width = 0. Unchecking would need to resize it appropriately.
The other option is that the chart has no series in it until a box is checked. Then each check box, when checked fires a macro that adds the relevant range of data to the chart as a new series. Unchecking would need to fire a different macro that removes the series.
Each has their own benefits. The hack prevents you from having to have the macro do all the formatting work each time it runs (adding & removing the series would cause any color, thickness, etc. settings to be lost). The adding & removing series entirely prevent someone from accidentally moving the rectangles and is robust against the case that someone manually resizes the chart, which would cause the legend to grow/shrink & move correspondingly.
Take your pick and go from there. If you need help with the VBA after giving it a shot, update with what you have and someone will be able to help close the gap.
