Control charts in Excel

Overview of Excel Control Charts

Control charts were statistical visual tools for tracking the performance of a process over time. Whether everything is operating as it should or whether there are some problems. Under statistical process control (SPC), key tools assess how well any system or process is performing and whether it is operating smoothly or not. It is possible to reset the procedures if there are any problems. Control charts are usually utilised in manufacturing processes to determine whether or not the processes are under control.

A description of a control chart

Just a line chart can be called a control chart. Its generation aims to determine when the points of control fall outside the real upper and lower bounds or whether they are there for the data and fall between them. The process is under control if the control points are well inside the limitations. The process is deemed to be out of control if a portion of the points falls outside the control range. Microsoft Excel can construct control charts and make the process easier even if other Statistical Process Control (SPC) programs are available.

Excel Control Chart Example

Assume the following: 12 observations of data from a manufacturing company. We wish to check if the process stays well inside the bounds of control. If the process is out of control, we will create a control chart to determine that. View the partial data picture shown below.

Step 1: The function "AVERAGE(B2:B13)" is used in the cell by B14, which calculates the Average of 12 Samples.

• The solution is displayed below after using the formula mentioned above.

Step 2: To find the sample's standard deviation based on the given data, enter the formula "STDEV.S(B2:B13)" in cell B15. A sample standard deviation is computed using this formula. We use a different formula to find the population standard deviation using Excel.

• The solution is displayed below after using the formula mentioned above.

Step 3: Go to cell C2 in column C, called the CONTROL LINE, and enter a formula as =$B$14. The $ symbol makes the columns and rows in this formula constants. The formula imputed in cell C2 will be the same in every cell when you drag and populate the remaining rows for column C. Fill the remaining cells in column C by dragging and dropping. The result can be seen below.

• The solution is displayed below after using the formula mentioned above.
  
• Drag and fill column C's remaining cell.
• We are using the Average as the value for the Control Line since it is nothing more than the control chart's centre line, which remains constant over observations.

Step 4: The formula is for the Upper Limit. Consequently, enter the formula in cell D2 as =$B$14+(3*$B$15). Once more, the maximum is set for the entire sample's observations. As a result, we have made rows and columns constant by using the $ symbol. You can see the result below by dragging and filling the last cell in column D.

• The solution is displayed below after using the formula mentioned above.
  
• Drag and fill column D's remaining cell.
  

Step 5: The control chart's lower limit can be expressed as shown in cell E2. To solve for this, enter =$B$14-(3*$B$15). The $ symbol in this formula allows it to compute the bottom limit, which is fixed across all weekly observations. You can see the result below by dragging and adding a formula to the remaining cells.

• The solution is displayed below after using the previously mentioned formula.
  
• The last cell in column E should be filled by dragging.
  

Explanation:

• The parameters of the control chart are the Upper Limit, Lower Limit, and Central Line. Our theoretical foundation allows us to compute these terms. Within the context of Statistical Process Control (SPC), a process is considered to operate normally if 99.73% of observations fall within three standard deviations above and below the Central/Control Line. For this reason, the SPC's upper limit is three standard deviations above the Average, and the lower limit is three standard deviations below the Average. The process may only be said to be following its typical course if it occurs. If not, the machinery must be adjusted since the process is acting strangely.
• The Information required for plotting the control chart using Excel is now complete. We're going to add one in Excel now.

Step 6: On your Excel sheet, choose the data of columns A as well as B (spread throughout A1:B13) and select the Insert tab from the Excel ribbon. Go to the Insert Line & Area Chart button under the Charts section.

Step 7: Insert a line or area chart in the dropdown menu. A few line & area chart options will appear under Excel. Choose Line with Markers within the 2-D Line section out of all of those, then hit Enter.

• This is how your graph will appear once the data from the previous week is removed from the line chart.
  
• To better understand how the sample data changes, we would like to exclude the mean and standard deviation taken by default.

Step 8: Select the "Select Data" option by right-clicking the graph.

Press the "Edit" button to open the "Select Data Source" dialogue box.

Step 9: In Legend Entries (Series), enter Control Line as the "Series name" and the appropriate control line values as the "Series values" in the "Edit Series" dialogue box following the click of the "Edit" button. Once completed, click the "OK" button.

Step 10: Click the "Edit" button, then in the "Edit Series" dialogue box, enter the Upper Limit as a "Series name" and the matching Upper Limit numbers as "Series values." When finished, click the "OK" button.

• When you're done, click the "OK" button to close the "Edit Series" dialogue box after selecting the "Edit" button, entering the Lower Limit as a "Series name", and selecting the matching Lower Limit values as "Series values."
  
• The control chart is available for viewing below.
  

Step 11: You can close this graph by giving it the title "Control Chart."

In Excel, a control chart may be drawn in this manner.

Points to Keep in Mind

1. Control charts are plotted to determine whether the process is under control.
2. It is necessary to compute and plot the Upper Limit, Lower Limit, and Central/Control Limit to determine whether the process falls between them.
3. The process is under control if all the points fall between upper and lower control limits. Otherwise, if one or more points fall outside the limits, then the process is out of control, which means there is a presence of Assignable causes.