How to Make a Statistical Process Control Chart(SPC) in Excel- Examples and Templates

A statistical process control chart is a line chart that is primarily used to track the data flow of your project, to maintain continuity between normal limits, to determine whether the variation appears to be out of control, and for a variety of other purposes.

Briefly, the statistical process control chart graphically represents changes in data over time.

Dr. Walter A. Shewhart of Bell Laboratories created statistical process control charts in the 1920s.

In this article, we will learn how to create a statistical process control chart in Excel.

Initially, we should know the basic concepts and meaning of statistical process control chart.

Components of statistical process control chart(SPC)

Control Line (CL): 

The control line means the average or most common case among the process control data points. It is the optimum middle condition among all data points.

Upper Control Limit (UCL) Line: 

The upper limit line indicates the most extreme situation. Any data point above this line is considered to be a severe or out-of-control condition. 

Lower Control Limit (LCL) Line: 

The distance between the control line and the upper control limit (UCL), which is the same as the distance between the control line and the lower control limit (LCL), Just one difference over UCL is that LCL stays on the opposite, or lower, side of the Control Line.

Theoretical Formulas to Determine Upper Control Limit and Lower Control Limit:

Upper Control Limit = average + 3 x σ 

Lower Control Limit = average – 3 x σ

Where,  σ = Standard Deviation of the process data points.

In Excel, we will calculate the standard deviation using the following formula

=STDEV.S(range)

If you are using Excel 2007, to calculate standard deviation you have to use the following formula

=STDEV(range)

Steps to Create a Statistical Process Control Chart in Excel:

Step 01: Preparing data for the statistical process control chart

Suppose you have data on supplying quantity over 10 weeks. You want to create a statistical process control chart in Excel.

Step 1.1: Insert a new column named “Control Line.” 

Step 1.2: Input the following formula at the output cell D3 and press Enter.

Step 1.3: AutoFill the remaining  cells in column D

=AVERAGE($C$3:$C$12)

Step 1.4: Insert a new column named “Upper Limit”

Step 1.5: Insert the following formula in the output cell E3 and press Enter.

=$D$3+STDEV.S($C$3:$C$12)*3

Step 1.6: And then, AutoFill the rest of the cells in column E.

Step 1.7: Similarly, create a new column named “Lower Limit.” To calculate the lower limit, use the formula given below:

=$D$3-STDEV.S($C$3:$C$12)*3

Step 02: Import Chart to Show Process Control Data 

Step 2.1: Select the data range B2:F12

Step 2.2: Click on the “Insert” tab

Step 2.3: From the “Charts” section, click on “Insert Line or Area Chart”

Step 2.4: Select the “Line with Markers”

Your process control chart is ready

Step 03: Customizing process control chart.

Unfortunately, labels on the horizontal axis overlap with the lower control limit. To solve this problem,

Step 01: Double-click on the horizontal axis labels.

Step 02: The “Format Axis” tab will appear on the right side of the monitor.

Step 03: Click on the ‘’Axis option’’ and then click on ‘’labels’’

Step 04: Increase the “Distance from the axis” according to your preference.

You can change the chart name by clicking the “Chart Title”

The final outcome is –