Useful Excel Charting Tip - Useful Script

So you have lot of data variables in a spreadsheet, and would like to eyeball it in a chart and get a quick idea of relationships between variables etc, but it is too cluttered because of so many series if you tried to put them all one on chart. Wouldn't it be great if there was a quick way to toggle individual series "on" and "off" while looking at your chart?

The usual method is to limit the number of series, delete series or change line properties manually to make them disappear one by one, but thats a lot of work. Besides, to undo the change is just as much work.

Here's a script I put together that lets you toggle series on and off just by selecting and then double clicking the series legend text!

It works by toggling each line series linestyle property between a solid line and no-line, giving the illusion of switching off the line series. It won't remove markers but you could easily adapt it to do that. One limitation- it only works on a chart thats on its own sheet (i.e. not embedded inside a worksheet.)

Instructions:

1. Create your Line or XY chart as you desire, but located as its own sheet. (NB not embedded in another worksheet). Make sure you have a legend (i.e. list of series names displayed), and this works best with line only chart types, although you could adapt it for markers as well.
2. On the menu, Click Tools -> Macro -> Visual Basic Editor
3. When visual basic edit loads, explore and find VBAproject ("Yourfile name")
4. Expand the Microsoft Excel Objects items and find the name of your chart (usually "Chart1")
5. Doubleclick on the name of your chart- it will put the cursor in the code window.
6. Copy and paste the following code into the code window:

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)

If ElementID = xlLegendEntry Then
If SeriesCollection(Arg1).Border.LineStyle = xlLineStyleNone Then
SeriesCollection(Arg1).Border.LineStyle = xlSolid
Else
SeriesCollection(Arg1).Border.LineStyle = xlLineStyleNone
End If
End If
Cancel = True
End Sub

7. Go to the menu File -> Close and Return to Excel
8. Voila... it should work. To test click on one of the legend texts in the legend box, then double click individual legends to toggle them off and on.
9. Remember to save.
10. Repeat for any additional charts you wish to make.

New to Excel VBA? This might help: