• Around The HomeToggle Menu

    • Entertainment
    • Productivity
    • Smart Home
  • FamilyToggle Menu

    • Parenting
    • Toys
    • Pets
    • Travel
  • ReviewsToggle Menu

    • Phones
    • Tablets
    • Laptops
    • Desktops
    • Wearables
    • Audio
    • Cameras
    • Headphones
    • Printers
    • Smart Home
    • TVs
    • Gaming and Video
  • One Cool ThingToggle Menu

    • Frugal Tech
    • Kickstarters
    • Videos
  1. Home
  2. Around The Home
  3. Productivity
  4. How Do I Create Control Charts in Excel?

How Do I Create Control Charts in Excel?

By: David Weedmark | November 2, 2018
  • Share
  • Share on Facebook

A control chart Excel process is a useful tool for studying how processes or other data changes over time. The chart consists of four lines -- the data, a straight line representing the average, as well as an upper control limit and a lower control limit (ucl and lcl in Excel).

Woman sitting at the window using laptop
credit: Westend61/Westend61/GettyImages

UCL and LCL in Excel

...
Enter the data in a single column.
credit: Screenshot courtesy of Microsoft.

Open a blank Excel worksheet. Type the name you want to use for your data in cell B1 and then enter the data for your chart in that column. In our example, 12 numbers are entered in cells B2 through B13.

Video of the Day

...
Calculate the average.
credit: Screenshot courtesy of Microsoft.

Select a blank cell below your data in column B. Click the Formula tab and then click the small Arrow beside the AutoSum button. Select Average from the drop-down menu. Highlight the cells containing the data and press Enter.

...
Calculate the standard deviation.
credit: Screenshot courtesy of Microsoft.

Select the blank cell beneath the cell used to calculate the data average. In our example, this is cell B16. Click the small Arrow beside the AutoSum button again. This time select More Functions. Click STDEV in the window that opens, highlight the cells containing the data and press Enter.

...
Copy the average to the top of a new column.
credit: Screenshot courtesy of Microsoft.

Type Average in cell C1 and then click cell C2. Type = and then click the cell containing the average. Insert a $ between the column letter and row number and then press Enter. In our example, the formula is =B$15.

...
Paste the average into column C.
credit: Screenshot courtesy of Microsoft.

Select cell C2 and press Ctrl-C to copy it. Drag the cursor over the blank cells in column C that have a value beside them and then press Ctrl-V to fils each cell with the average. When you plot the control chart, having these cells filled with the same number gives you a straight average line.

...
Calculate the Upper Control Limit, or UCL.
credit: Screenshot courtesy of Microsoft.

Type UCL in cell D1 to specify the Upper Control Limit. The UCL is calculated by adding the average to 3 times the standard deviation. Type the following formula into this cell, replacing "B15" and "B16" with cells containing your average and your standard deviation: =B15 + (B16*3)

Insert a $ between the cell and row for each cell and then press Enter. Your final formula in this cell should look like this: =B$15 + (B$16*3)

...
Calculate the Lower Control Limit, or LCL.
credit: Screenshot courtesy of Microsoft.

Type LCL in cell E1 for the Lower Control Limit. The LCL subtracts 3 times the standard deviation from the average: =B15 - (B16*3)

Insert a $ between the cell and row for each cell and press Enter. The final formula in this cell looks like this: =B$15 - (B$16*3)

...
Highlight the cells containing the Average, UCL and LCL data.
credit: Screenshot courtesy of Microsoft.

Copy the cells containing the UCL and LCL values and paste them into the cells below them. This will give you straight lines for both the UCL and LCL values in the control chart.

Highlight the cells containing the Average, UCL and LCL data.

...
Create a line chart from the highlighted data.
credit: Screenshot courtesy of Microsoft.

Click the Insert tab and click the Line Chart icon. From the drop-down menu, select the first line chart that appears.

...
Change the chart title to Control Chart.
credit: Screenshot courtesy of Microsoft.

Click the Chart Title at the top of the line chart and replace it with Control Chart. If you want to change the appearance of your chart, click the Style icon beside the chart and select a new style.

Show Comments

Related Articles

How to Do an Excel Chart With Multiple Legends

How to Do an Excel Chart With Multiple Legends

Around The Home
Productivity
By: Vera Leigh
How to Turn Excel Spreadsheet Data Into Graphs

How to Turn Excel Spreadsheet Data Into Graphs

Around The Home
Productivity
By: Darrin Koltow
How to Get the RMS in Excel

How to Get the RMS in Excel

Around The Home
Productivity
By: Ron Price
How to Calculate Normalized Data in Excel

How to Calculate Normalized Data in Excel

Around The Home
Productivity
By: Lee Johnson
How to Count Checked Text Boxes in an Excel Spreadsheet

How to Count Checked Text Boxes in an Excel Spreadsheet

Around The Home
Productivity
By: Warren Rachele
How to Find the Z-Score Using Microsoft Excel

How to Find the Z-Score Using Microsoft Excel

Around The Home
Productivity
By: Lee Johnson
  • HOW WE SCORE
  • ABOUT US
  • CONTACT US
  • TERMS
  • PRIVACY POLICY
  • COPYRIGHT POLICY
  • Advertise

An error occurred. Try again later.

Thanks for signing up!
© 2019 Leaf Group Ltd. Leaf Group Media

Get great tech advice delivered to your inbox.

Keep your family productive, connected, entertained, and safe.

Please enter a valid email.