Shewhart p Charting in Microsoft® Excel

Chapter 7, Example 11

Note: Because Excel's data analysis tools have no built-in control chart tools, this example shows how to create a p chart from the "fraction nonconforming" data in Table 7.18 (p. 520) and the standard value for p using Excel's Chart feature.
1. Launch Excel.

2. Enter the values to be plotted.

• In cell A1, type "# Nonconforming"

• In cell B1, type "p"

• In cell C1, type "Center Line"

• In cell D1, type "LCL"

• In cell E1, type "UCL"

• In cells A2-A26, enter the number of nonconforming pellets for each sample from the "Number Nonconforming" column of Table 7.18 (p. 520).

• In cell B2, enter "=A2/30"

• Select cells B2-B26.

• Select "Edit --> Fill --> Down."

• In cell C2, enter "0.61"

• Select cells C2-C26.

• Select "Edit --> Fill --> Down."

• In cell D2, enter "=0.61-3*SQRT(0.61*(1-0.61)/30)"

• Select cells D2-D26.

• Select "Edit --> Fill --> Down."

• In cell E2, enter "=0.61+3*SQRT(0.61*(1-0.61)/30)"

• Select cells E2-E26.

• Select "Edit --> Fill --> Down."

Screenshot:

3. Create the p chart.

• Select "Insert --> Chart."

• In the dialog box that follows, select "Line" from the "Chart type" list; then, under "Chart sub-type, select the sub-type whose label reads, "Line with markers displayed at each data value."

Screenshot:

• Click "Next."

• Fill out the "Data Range" pane of the next window as follows:

Input Type Label Value
Data range: =Sheet1!\$B\$1:\$E\$26
Columns selected

Screenshot:

• Click "Next."

• Fill out the "Titles" pane of the next window as follows:

Input Type Label Value
Chart title:
Category (X) axis: Sample
Value (Y) axis: p

Screenshot:

• Click the "Gridlines" tab.

• Make sure all checkboxes in the "Gridlines" pane are unchecked.

Screenshot:

• Click "Next."

• Fill out the resulting dialog box as follows:

Input Type Label Value
As object in: selected
As object in: Sheet1

Screenshot:

• Click "Finish."

Excel generates the chart and places it in Sheet1.

Screenshot:

Note: This example has listed only the steps needed to generate a very crude control chart in Excel; the chart will most likely need to be adjusted for aesthetics and readability. Most notably, the markers on the center and control limit lines are confusing and should be removed. Chart elements in Excel are easily edited by double-clicking the element and making customizations in the resulting dialog box. For more help, see where some of these steps are detailed in step 4.