How to Chart Upper Control Limit in Excel (With Formulas)

Business professionals who use Six Sigma to streamline their operations and minimize errors often rely on control charts in Excel to help them monitor processes. Control charts comprise many important elements, including lower and upper control limits. Understanding the importance of an upper control limit in Excel and other related components can help you create, interpret and monitor control charts effectively. In this article, we explain what a control chart is in Six Sigma, the formulas used to calculate control limits and how to chart them in Excel, with control chart examples and tips for creating one.

What is a control chart in Six Sigma?

A control chart in Six Sigma is the main statistical control process (SPC), which involves using statistical methods to monitor a process. It allows quality control (QC) professionals to track the input or output of a specific process over time to identify and resolve extreme variations before they become significant concerns. QC professionals evaluate control charts regularly to gather data they can use for many purposes. Here are some uses for data collected from control charts:

• Making decisions

• Relaying information to teams

• Identifying trends

• Predicting process capabilities

• Examining results

• Recording information for reference

Related: Complete Guide to the Six Sigma Steps

How to chart upper control limit in Excel

Consider these steps to chart an upper control limit in Excel:

1. Enter your data

The first step to creating an upper control limit in Excel is to enter your data into a spreadsheet. For example, you can put your data into cells A1 through D3:

2. Find the average (mean) value

You can find the average value of each row using two methods. The first method is by calculating it yourself using these formulas:

µ = Σ(A1:D1) / n

µ = Σ(A2:D2) / n

µ = Σ(A3:D3) / n

Where the parts of the equation mean the following:

• µ represents the average

• Σ represents the sum

• A# represents the value in that cell

• : represent the word through

• D# represents the value in that cell

• n represents the total number of values in each row

For each row of cells, their respective averages are:

µ(A1:D1) = Σ(26 + 54 + 39 + 82) / 4

µ(A2:D2) = Σ(64 + 25 + 12 + 34) / 4

µ(A3:D3) = Σ(17 + 53 + 3 + 92) / 4

Which returns the values 50.25, 28 and 41.25, respectively. You can enter these values in cells E1 through E3.

The second way you can calculate the average of each row of cells is by using a formula within Excel. These are the three steps you can use to have Excel calculate the average:

1. Type the text "=AVERAGE (A1:D1)" in cell E1 and press "Enter."

2. Guide your mouse cursor over the right-hand corner of cell E1 until it changes to a "+" symbol.

3. Click and drag down to cell E3 to fill in those cells with a similar formula.

3. Find the average of averages

Afte r you've found the average value of each of your ranges, you can calculate the average of averages. There are also two methods to do this. The first is to calculate the values yourself using the formula:

µ = Σ(E1:E3) / n

Where the parts of the formula are:

• µ represents the average

• Σ represents the sum

• E# represents the value in that cell

• : represents the word through

• n represents the total number of values

Using the formula, you can calculate the average as:

µ(E1:E3) = (50.25 + 28 + 41.25) / 3

µ(E1:E3) = 41.75

You can also calculate the average of averages by typing =AVERAGE (E1:E3) in cell E4 and pressing "Enter."

4. Calculate the standard deviation

To find the standard deviation of your data set in Excel you can use the text command =STDEV(Cell name:Cell name). Using this formula and the information above, you can type into cell E5 =STDEV(A1:D3) and press "Enter." This will populate cell E5 with the standard deviation from your data set. The standard deviation of the sample data above is 27.818.

Read more: How To Calculate Standard Deviation: What It Is and How To Use It

5. Add the UCL formula

Once you've calculated your average of averages, standard deviation and averages, type the formula for the upper control limit. You can type this formula into cell E6. For the data above, the upper control limit in Excel is =E4+3*E5

Where the parts of the formula are:

• E4 represents your average of averages

• 3 represents the number of standard deviations needed for a control limit

• E5 represents one standard deviation

The upper control limit of the data above is 125.204.

6. Add the LCL formula

For Excel to chart a graph appropriately, it may also need a formula for the lower control limit. You can type =E4-3*E5 into cell E7 for the example data above. Where the parts of the formula are:

• E4 represents your average of averages

• 3 represents the number of standard deviations for a control limit

• E5 represents one standard deviation

The lower control limit for the data above is -41.704.

7. Copy the UCL, LCL and average of averages

Once you've calculated your average of averages, upper control limit and lower control limit, you can copy those values and paste them below your original data. For example, the data above has an upper control limit of 125.204, a lower control limit of -41.704 and an average of averages of 41.75. In the Excel spreadsheet, you can paste these values in cells A4 through D4, A5 through D5 and A6 through D6, respectively, so they appear on your graph.

8. Select your data and choose a chart

When you have all of your data in the spreadsheet, you can select the cells you want to convert into a chart by clicking and dragging your cursor from cell A1 through the last cell of your data. For example, the data above uses cells A1 through D6. Once you have your data selected, go to the "Insert" tab in the ribbon at the top of the page and insert a chart. From there, you can select a chart style. Typically, a line chart is used to represent data that have upper and lower control limits.

9. Change the chart title and save the workbook

Finally, after you've created your chart, give it a title that represents the data that this shown on the chart, including the upper and lower control limits. The title can be short as long as it's an accurate representation of the data. This is important because you want to remember what the chart represents and allow others who may see or use the chart to know what data is within the chart. After you create the chart, remember to save the workbook for later use by typing "Ctrl + S" or clicking "Save" in the ribbon.

Related: 25 Best Excel Tips To Optimize Your Use

What formulas are used to calculate control limits in Excel?

You can add specific formulas into the cells on your control chart to calculate upper control limits and lower control limits in Excel. Each serves an important purpose, so understanding their role and how to use them is beneficial for creating an effective control chart. Here are definitions of each and the formulas you can use to calculate control limits in Excel:

Centerline

The centerline for a control chart serves as the basis for the chart's control limits. You can calculate the centerline by finding the average of all your data values. Use this formula:

µ = Σ(All cells with values) / n

Where the parts of the formula are:

• µ represent the average

• Σ represents the sum

• n represents the total number of values

Sigma lines

Sigma lines are specific amounts of variable data within your set. You can establish Sigma lines in Excel by calculating the average of averages and standard deviation. The formulas for an average of averages and standard deviation in excel are:

=Average(first cell name:last cell name)

=STDEV(first cell name:last cell name)

Where in the first formula the cell names hold the average for each row of data, and in the second formula the first cell name represents the first value you have in a set of data. Typically, cell A1 and the last cell name refer to the last cell in the spreadsheet that contains the numeric value you want to use, which varies based on your data set.

Upper control limit

You calculate the upper control limit in a control chart from the centerline and Sigma lines for the data. QC professionals who use Six Sigma place the UCL Three Sigma, or standard deviations, above the centerline. The Excel formula for calculating the UCL is:

=Cell name+3*standard deviation (sigma)

Where the cell name is the value of your centerline. The standard deviation for your data set is the values you calculated based on your data.

Lower control limit

You can calculate the lower control limit in a control chart from the centerline and the Sigma lines for the data. Like the upper control limit, QC professionals use three standard deviations, or Sigma, below the centerline. The Excel formula for calculating LCL is:

=Cell name-3*standard deviation (sigma)

Where the cell name is where you stored your centerline value and the standard deviation for your data set is the number you calculated based on your data.

Related: Basic Excel Formulas and How To Use Them

Example of a control chart in Excel

Here's an example of a complete control chart based on the data stored in column B:

In cells F2 and F3, the values are the same. F2 represents the Excel formula and F3 is the actual value after the formula made the calculation. The same is true for cells G2 through G3, D2 through D6 and E2 through E6.

Tips for creating a chart in Excel

Use these tips to help you create your charts in Excel:

Know your control limits

If you already know what your control limits are, you can skip the steps needed to calculate them and simply input them into the cells below the data you want to show in the chart. Remember that you may need your upper control limit, your lower control limit and your centerline before you can create the chart that shows all that data. Knowing your standard deviation can also help if you plan to share the data with others.

Re-evaluate your control limits

If your data is always well within your control limits, you may reduce your control limits to a degree to increase efficiency. While this may cause some data points to lie outside your control limits, you can use these new limits to design more efficient processes and develop better procedures within the workplace. This can also be a good practice to see if you're creating more efficient processes.

Please note that none of the products mentioned in this article are affiliated with Indeed.