Professional business owners who use six sigma to design their operation outline and significantly lower the errors sometimes use a control chart in **Excel **to help them supervise the process. Control chart comprises of elements like **Upper Control Limit**, **lower control limit** and **mean **etc. Upper control limit is the highest limit for data to keep the process optimum which can be calculated by using average and standard deviation of data. In this article, we will the stepwise procedures to calculate **upper limit control limit with formula in Excel**.

## Download Practice Workbook

You can download the practice workbook from here for a better understanding of the procedures.

## Step-by-Step Procedures to Calculate Upper Control Limit with Formula in Excel

In this section, we will discuss the stepwise procedures to calculate upper control limit with formula in **Excel**. The general formula for determining upper control limit is:

`UCL = Average + (3*Standard Deviation)`

Without any dilemma, let’s jump to the procedures to calculate the upper control limit.

### STEP 1: Input Sample Data for Upper Control Limit Calculation

- Firstly, you have to include sample data for calculating the upper control limit. For demonstration, we have included some random data in
**Sample Data**column.

**Read More: ****How to Calculate Upper and Lower Limits in Excel (2 Methods)**

### STEP 2: Determine Average of Sample Data

- Then, we have to determine the mean of the sample data.
- To do that, we are using
**the AVERAGE function**of**Excel**. Just use the following formula in**Cell D5**.

`=AVERAGE($C$5:$C$14)`

**Note:**We have used fixed cell reference in the formula so that we can copy it without changing it. Simply, press

**Alt**+

**F4**to make a cell reference fixed.

- Then, use the
**Fill Handle**to copy the formula in the following cells.

**Read More: ****How to Set Limit in Excel Cell (5 Simple Ways)**

### STEP 3: Find Standard Deviation for Data

- Afterward, use
**the STDEV.S function**to calculate the standard deviation. Just apply the following formula in**Cell C16**.

`=STDEV.S($C$5:$C$14)`

**Read More: ****How to Set Limit for Visible Area in Excel (3 Ways)**

### STEP 4: Calculate Upper Control Limit with Formula

- Finally, it’s time to calculate the upper control limit.
- For that, insert the following formula in
**Cell E5**and press**Enter**.

`=D5+3*$C$16`

- As a result, we will see the upper control limit for the sample data in
**Cell E5**. - At last, use the
**Fill Handle**to copy the formula and get the**UCL**in the following cells.

## How to Determine LCL with UCL and Create Chart with It in Excel

So far, we have calculated the upper control limit and now let’s calculate the lower control limit and make a chart with the average, upper control limit and lower control limit. For that, follow the steps given below.

- Firstly, calculate the upper control limit following the previously mentioned procedures.
- Then, apply the following formula in
**Cell F5**to get lower control limit there.

`=D5-3*$C$16`

- Afterward, use the
**Fill Handle**to copy the formula in the following cells.

**Note:**The formula for lower control limit is similar to upper control limit formula. Except we subtracted

**3*Standard Deviation**from

**Average**instead of adding.

- Further, select the whole dataset including column
**Sample Data**,**Average**,**UCL**and**LCL**for creating a line chart with them.

- After that, go to the
**Insert**tab and select**Line or Area Chart**>**2-D**>**Line**from the ribbon.

- Consecutively, we will see the line chart. Obviously, you can give it a suitable name.

- Later on, select any data point on the line chart and right-click on it.
- Then, select
**Add Data Label**.

- In a similar fashion, you can add more
**Data Labels**for a better understanding of the line chart.

**Read More: ****How to Limit Data Range in Excel Chart (3 Handy Ways)**

## Conclusion

Calculating upper control limit with formula in **Excel **is quite simple. Hope, you will be able to calculate upper control limit, lower control limit and create a line chart with them afterward. If you have any queries or suggestions, please leave a comment. Visit our **ExcelDemy Website **for similar articles regarding **Excel**.