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.

Interpreting CUSUM graphs

To interpret the CUSUM graph one needs to look at the slope of the graph, and specifically where slope changes occur. A constant slope is an indication of a stable value in the underlying data despite the presence of noise. In the example given earlier, a number of relatively "constant" slope areas can be identified, and these are shown superimposed on the graph. Points at which the slope changes are the turning points and these have been denoted with vertical lines.

So what do you do with the turning points? We'll this gives you an indication of where to average values from. In the example given, the first identified period is from t=1..14s, and the average for this period is 1.0. For the second period t=15..30s, the average is 6.4, and so on. I haven't shown this but you you could add this graphically to the bottom series as straight lines between the turning points for clarity, at the appropriate y-axis average value.

There is some danger in identifying too many turning points, as you could start reading something into the data which just isn't there. The greater the change in slope, the more convincing the turning point. In this example the turning points near 48, 72 and 84 are the most convincing.

To assist in calculating the average from the graph, one can add a calibration scale/mask which shows the relationship between set slopes and average values. We'll save details on how to do that for a later post though.

Graphing (more) like a Pro

Ever noticed the differences between a stock-standard excel XY graph and graphs you see in papers published in Journals? They tend to look cleaner and professional whilst the standard ones in excel (minus tweaking) look like a high school project?

<<>>

So here are some tips to get a more professional look:


1. Get rid of the gray background (Right click & Format Plot Area)

2. Get rid of gridlines, unless they really do add value to your data (Right click & Chart Options)


Quite often a single dotted line to show a threshold value will suffice and at the same time save clutter on your graph.


3. Choose appropriate scaling and keep the axes out of the graph area by choosing where the x and y axes cross each other (Right click the axes, format, scale)


4. Put ticks whether major or minor on the inside. (right click, format, patterns)


5. Keep axes labels short and sweet and give the units in brackets, eg. time [seconds]


6. Place the legend in the graph, this gives a bigger graph view in the same space and its easier for your readership to see where the legend is.


7. Use markers and lines sizing carefully to make the data clear. You often don't need both markers and lines.


8. Don't put too many series on one graph unless it really is necessary.