Statistical Process Control Chart In Excel

In this lesson you will learn how to create statistical process control chart.

First we are going to find the mean and standard deviation. To find the mean click on the Formula tab, click on More Function select Statistical and then Average from the dropdown menu. Select cells B2 to B20 and press okay.

 

 

ribbon average function

Click on the Formula tab, click on More Function select Statistical and then STDEV.S from the dropdown menu. Select cells B2 to B20 and press okay.

STDEV.S function

Select cell C1 and type “Mean (CL)” in it. Select C2 and type “=I$1”. Move your mouse to the bottom right of the cell until a black plus sign appear. Drag the plus sign all the way to cell C20 to copy the mean.

Select cell D1 and type “UCL” in it. Select D2 and type “=I$1+ (I$2*3)”. Move your mouse to the bottom right of the cell until a black plus sign appear. Drag the plus sign all the way to cell D20 to copy the mean.

Note: UCL= upper control limit and is mean+3 times the standard deviation

Select cell D1 and type “LCL” in it. Select E2 and type “=I$1+ (I$2*3)”. Move your mouse to the bottom right of the cell until a black plus sign appear. Drag the plus sign all the way to cell E20 to copy the mean.

Note: LCL= lower control limit and is mean-3 times the standard deviation

SELECT Sample Measures, Mean(CL), LCL and UCL.

data table

Click on Insert tab, click on Line Chart and then Click on Line.

insert line chart

You have created your chart. Resize it. Remove the small black lines by double clicking on them and pressing Delete. That’s it, you’re done.

created chart

This is what your final chart will look like.

ready chart

Your Statistical process control chart is ready.

Template

You can download the Template here – Download
Previous articleHow To Link Objects In Excel
Next articleGoogle Classroom Tutorial