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
Time Observed value Upper limit Lower Limit
1 30 52.5 12.5
2 0 52.5 12.5
3 25 52.5 12.5
4 30 52.5 12.5
5 35 52.5 12.5
6 40 52.5 12.5
7 50 52.5 12.5
8 45 52.5 12.5
9 31 52.5 12.5
10 20 52.5 12.5
11 40 52.5 12.5
12 60 52.5 12.5
13 45 52.5 12.5
14 60 52.5 12.5
15 45 52.5 12.5
16 32 52.5 12.5
17 50 52.5 12.5
18 60 52.5 12.5

• 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 B2F19 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
11
(No Transcript)