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*

**Instant Connection to an Excel Expert**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment