# Shewhart x-bar Charting in Microsoft® Excel

## Chapter 7, Example 9

Note: Because Excel's data analysis tools have no built-in control chart tools, this example shows how to create an x-bar chart from the summary statistics given in Table 7.17 (p. 503) and the standard values for the mean and standard deviation using Excel's Chart feature. If necessary, it would be possible to create an x-bar chart starting with the raw data, but this would require the extra step of calculating the summary statistics.
1. Launch Excel.

2. Enter the values to be plotted.

• In cell A1, type "xbar"

• In cell B1, type "Center Line"

• In cell C1, type "LCL"

• In cell D1, type "UCL"

• In cells A2-A23, enter the sample means from the x-bar column of Table 7.17 (p. 503).

• In cells B2-B23, enter "10"

• In cell C2, enter "=10-3*1.9/SQRT(5)"

• Select cells C2-C23.

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

• In cell D2, enter "=10+3*1.9/SQRT(5)"

• Select cells D2-D23.

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

Screenshot:

3. Create the x-bar 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!\$A\$1:\$D\$23
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: x-bar

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:

4. Adjust the chart for better display.

• Select the chart by clicking on a blank space within its border.

• Double-click a marker on the "LCL" line.

• In the "Patterns" tab of the resulting dialog box, set the line style to dotted and the line color to black. Under the heading "Marker," select "None."

Screenshot:

• Click "OK."

Excel changes the line style and color and removes the markers from the "LCL" line.

• By the same method, change the color and line styles of the UCL and Center Line to something more aesthetic, and turn off the markers on these lines.

• Double-click the Y axis.

• Fill out the "Scale" pane of the resulting dialog box as follows:

Input Type Label Value
Minimum: unchecked
Minimum: 6.5
Maximum: unchecked
Maximum: 13
Major unit: unchecked
Major unit: 1
Minor unit: checked
Minor unit: 1
Category (X) axis: checked
Crosses at: 0
Display units: None
Logarithmic scale unchecked
Values in reverse order unchecked
Category (X) axis crosses at maximum value unchecked

Screenshot:

• Click "OK."

Screenshot: