EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Excel Excel Resources Excel Charts Control Charts in Excel

Control Charts in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 24, 2023

Control Charts in Excel

Excel Control Charts (Table of Contents)
  • Definition of Control Chart
  • Example of Control Chart in Excel

Introduction to Control Charts in Excel

Control charts are statistical visual measures to monitor how your process runs over a given period. Whether it is running as expected or there are some issues with it. There are important tool under Statistical Process Control (SPC) which measures the performance of any system/processes whether they are running smooth or not. If there are any disturbances, the processes can be reset. Control charts are most of the times used under manufacturing processes in order to check whether the manufacturing processes are under control or not.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Definition of Control Chart

A control chart is nothing but a line chart. It can be generated when we have upper and lower control limits present for the data, and we wanted to check whether the control points are lying between the actual upper and lower limits or going out of those. The process is controlled if the control points lie well within limits. If some of the points are lying outside of the control limits, the process is said to be not in control. Though there is different Statistical Process Control (SPC) software available to create control charts, Microsoft Excel does not lack in creating such charts and allows you to create those with more ease. This article will show how control charts can be created under Microsoft Excel.

Example of Control Chart in Excel

Suppose we have data from 30 observations from a manufacturing company as below. We want to see whether the process is well within the control limits. We will draw a Control chart to see whether the process is in control. See the screenshot of the partial data given below.

You can download this Control Chart Excel Template here – Control Chart Excel Template

Control Charts in Excel 1-1

Step 1: In the cell, F1 applies the “AVERAGE(B2:B31)” formula, where the function computes the average of 30 weeks.

Control Charts in Excel 1-2

After applying the above formula, the answer is shown below.

Control Charts in Excel 1-3

Step 2: In cell G2, apply the “STDEV.S(B2:B31)” formula to calculate the sample standard deviation for the given data. This formula calculates the sample standard deviation. We have a different formula to calculate the population standard deviation in Excel.

Control Charts in Excel 1-4

After applying the above formula, the answer is shown below.

Control Charts in Excel 1-5

Step 3: In column C, called Control Line, go to cell C2 and input the formula as =$F$1. The $ sign used in this formula is to make the rows and columns constants. When you drag and fill the remaining rows for column C, all cells will have the same formula as the one imputed in cell C2. Drag and fill the remaining cells of column C. You’ll be able to see the output below.

Control Charts in Excel 1-6

After applying the above formula, the answer is shown below.

Control Charts in Excel 1-7

Drag and fill the remaining cell of column C.

Control Charts in Excel 1-8

Because the Control Line is nothing but the line of the center for the control chart, which does not change over observations, we are taking the Average as a value for Control Line.

Step 4: For the Upper Limit, the formula is. Therefore, in cell D2, put the formula as =$F$2+(3*$G$2). Again, the upper limit is fixed for all the week’s observations. Therefore we have used the $ sign to make rows and columns constant. Drag and fill the remaining cell of column D, and you’ll be able to see the output below.

Upper Limit

After applying the above formula, the answer is shown below.

Upper Limit 1

Drag and fill the remaining cell of column D.

Upper Limit 2

Step 5: Lower Limit for the control chart can be formulated as in cell E2. Put the formula as =$G$1-(3*$G$2). This formula calculates the lower limit, which is fixed for all weekly observations; the $ sign achieves that in this formula. Drag and fill the remaining cells with a formula, and you’ll be able to see the output below.

Lower Limit 1

After applying the above formula, the answer is shown below.

Lower Limit 2

Drag and fill the remaining cell of column E.

Lower Limit 3

Explanation:

The Upper Limit, Lower Limit, and Central/Control Line are the control chart parameters. We calculate these terms because we have a theoretical base for that. In Statistical Process Control (SPC), we say that the processes are going normal if 99.73% of observations are scattered around the Central/Control Line within 3 standard deviations above and below the same (that’s why we calculate the upper limit as 3 standard deviations above from average which is a central line and lower limit as 3 standard deviations below of the average). If it happens, then and only then can we say that the process is following the normal pattern. Otherwise, the process is behaving abnormally, and we need to make adjustments to the machinery.

We are done with the required information, which is needed to plot the control chart in Excel. Now we will head towards adding one in Excel.

Step 6: Select the data from columns A and B (spread across A1:B31)  from your Excel sheet and go to the Insert tab at the Excel ribbon. Under the Charts section, navigate towards Insert Line and Area Chart button.

Insert Line and Area Chart

Step 7: Press the Insert Line or Area Chart dropdown button; you’ll be able to see a handful of line and area chart options available under Excel. Out of those all, under the 2 – D Line section, select Line with Markers and Press the Enter key.

2-D Line

After removing the week’s data from the line chart, your graph will look like the one below.

Chart title

We would like to add the central/control, lower, and upper limit lines to this chart to see how the weekly data is moving.

Step 8: Right-click on the Graph and click on the “Select Data” option.

Select Data 1

A “Select Data Source” dialog box will open and click the “Add” button.

Select Data Source 1

Step 9: Inside Legend Entries (Series), after clicking on the “Add” button and input Control Line as a “Series name” and corresponding control line values as “Series values” under the “Edit Series” dialog box. Click on the “OK” button once done.

Control line Data series

Step 10: After clicking on the “Add” button and inputting Upper Limit as a “Series name” and corresponding Upper Limit values as “Series values” under the “Edit Series” dialog box, click the “OK” button after done it.

upper limit Data series

After clicking on the “Add” button and inputting Lower Limit as a “Series name” and corresponding Lower Limit values as “Series values” under the “Edit Series” dialog box, click the “OK” button after done with it.

lower limit edit series

Select Data Source

You’ll be able to see the control chart ready below.

Chart title 1

Step 11: Give the title “Control Chart” for this graph, and you are done with it.

Control chart

This is how we can create a control chart under Excel. This is from this article. Let’s wrap things up with some points to be remembered.

Things to Remember

  • Control charts are plotted to see whether the process is within control.
  • Calculating and plotting the Central/Control Limit, Upper Limit, and Lower Limit is mandatory to check whether the process lies between them.

Recommended Articles

This is a guide to Control Charts in Excel. Here we discuss How to create Control Charts in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Radar Chart in Excel
  2. Combo Chart in Excel
  3. Marimekko Chart Excel
  4. Interactive Chart in Excel
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Control Chart Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA

Download Control Chart Excel Template

EDUCBA

डाउनलोड Control Chart Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW