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:

Quick Tip: Zooming in and out of Excel / Openoffice

If you have a mouse with a scroll wheel, the chances are good that you can take advantage of this tip if you don't already know:

Hold down the CONTROL button whilst rotating the scroll wheel on your mouse -

and you should find that the view zooms in or out of your sheet or graph, centered on whatever the moise is pointing at.

Quick Tip: Adding Vertical (or Horizontal) lines to an Excel chart

When using CUSUM's or other charts it may be useful to highlight a period or point through the use of vertical lines. You could draw it on manually, but what if you need to change the graph or the value? With an XY chart it is fairly simple to add a vertical line. Lets say you need to add a vertical dotted line at x=7 and your y-axis scale extends from 0 to 10. Here's how you do it:

1. Add a news series to your graph (right click, "source data", series tab, add)
2. For the x-values enter this: ={7,7}
3. For the y-values enter this: ={0,10}
4. Right click one of the new points eg. x=7, y=10 and format data series
5. Click "auto" for Pattern > line and "none" for Pattern > Marker
6. Change line style to dotted (if you prefer, or keep for solid).
7. Voila!

If you need to make the values variable then just enter the cell reference to pick up:
eg. instead of ={7,7} you would like to use the value in cell B2
You guessed it, just enter ={B2,B2} for the x-values.

Quick Tip: Absolute references fast with F4

This might be old hat to some, but its pretty useful if you don't know. Change from relative references (like "=A1") to absolute (like $A$1, $a1, a$1) in formulas just by clicking on the reference whilst editing and pressing F4 (excel) or Shift F4 (openoffice).