We can use the statistical process control chart in Excel to study how processes or data changes occur over time. The Control chart has four lines including; a straight-line representing average, the data and a lower control limit (LCL) and an upper control limit (UCL). In this tutorial, we will learn how to create a control chart for analyzing data.

Figure 1 – How to make a statistical process control chart in excel
Creating Control Charts
- We will begin by setting up our data as shown in Figure 2
- Column A will contain the Date (will not be used in creating control chart)
- Column B contains our Sample Data
- Column C will contain the Control Line ( Mean or Average)
- Column D will contain the UCL (Upper Control Limit)
- Column E will contain the LCL (Lower Control Limit)

Figure 2 – Data to create a control chart
- Below our Table, we will set up a mini table. In Cells A14 and Cell A15, we will enter Mean and Std. Dev (standard deviation) respectively.

Figure 3 – How to create a control chart
- We will click on Cell B14. We will go to the Formula Tab and select the small Arrow beside the Autosum to view the drop-down menu

Figure 4 – How to create a control chart
- In the drop-down menu, we will select Average and highlight Cells B4:B12

Figure 5 – How to create a control chart
- We will press Enter

Figure 6 – How to make a control chart
- We will click again, this time in Cell B15, go the Formula Tab again, and click the small Arrow next to the Autosum. In the drop-down menu, we will select More Functions

Figure 7 – How to control charts in excel
- In the Insert Function dialog Window, we will select STDEV and click OK

Figure 8 – Creating control charts
- Again, we will highlight the Cells B4:B12 containing the data

Figure 9 – Making control charts
- We will press Enter

Figure 10 – How to create control chart
- In Cell C4, we will enter the cell reference for the Cell contain the average, i.e. we will enter =B$14.

Figure 11 – Making control charts in Excel
- We will press Enter. We will double-click the fill handle tool (the small box at the bottom-right as shown in Cell C5 of the figure below) and drag down the column

Figure 12 – How to create a control chart
- In Cell D4, we will add Average to STDEV multiplied by 3, which means we will have the formula below:
=B$14+(B$15*3)

Figure 13 – Making a control chart
- We will use the Fill handle tool to fill the result down the column

Figure 14 – Control charts in Excel
- In Cell E4, we will enter the formula below:
=B$14-(B$15*3)

Figure 15 – Statistical process control
- We will use the fill handle tool to enter the result down Column E

Figure 16 – Process control chart in excel
- We will highlight range B4:E12

Figure 17 – Create a control chart in Excel
- Next, we will go to the Insert Tab and select the Line chart from the Chart Group

Figure 18 – Control chart in Excel
- In the drop-down menu, we will select the first Line Chart

Figure 19 – Control chart in Excel
- We can now add a chart title, change or modify our Control chart as desired

Figure 20 – Excel Control Chart