Using Excel to Plot

1. Using Excel to Plot Farrokh Alemi, Ph.D.

2. Objectives Given a set of data plot a control chart using Excel software

3. Data • Observations over time (see first two columns) • Upper and Lower control limits (see columns 3 and 4) • Pre (yellow) & post (white) Intervention period • Plot a chart with three lines designating the observed, the upper and the lower limits. Distinguish between pre- and post intervention periods

4. Distinguish between before and after intervention periods • Create a series for the time period before intervention • Select the range from C2 to D8 and cut and paste it in two new columns E2 to F8. • Label E1 as upper limit before and F1 as lower limit before • Label C1 as upper limit after and D1 as lower limit after

5. Select chart options 1. Click on chart logo 2. Select first option of line charts 3. Enter data range B2:F19 or drag cursor from B2 to F19 to pick up this range

6. Title, X-axis, Y-axis & legend • Enter plot title, X-axis title and Y-axis titles • Select legend folder and turn off the legend

7. Select plot area • Select where to make the plot • See plot. The limits are confusing and need to be redone

8. Redo limits 1. Right click on the line until selected 2. Select format data series 3. Set marker to none. Set line color to red 4. Repeat for the after portion but this time set style to dashed

9. Redo observation line 1. Right click on the line for observed values. Select format data series 2. Set markers to automatic, press ok 3. Click on grid line until selected 4. Delete gridline

10. Final output