Create the Perfect Control Chart for SPC in Excel - MiniTab not Required

Learn how to create an Individuals and Moving Range (ImR) control chart that dynamically formats out of control data points.
In this video, we'll show you how to create an ImR control chart in Excel that formats similar to MiniTab. This control chart is a commonly used tool for measuring process variation, and is a great way to evaluate process control. After watching this video, you'll be able to create an ImR control chart in Excel quickly and easily! No need for MiniTab.
Control Charts are a great tool for engineering, business, and quality Engineers and other quality professionals. The most basic form of control charts are the Individuals and Moving Range chart (XmR chart).
Time Quick Links:
[0:34] - End Product
[1:36] - MR Bar Formula Correction
[4:55] - MR Chart Conditional Columns
[6:22] - Setting Up Test Columns
[9:30] - Rule #1 (Outside control limits - Out of control)
[10:24] - Rule #2 (9 IAR same side of Mean - Process Shift)
[12:50] - Rule #3 (6 IAR Increasing or Decreasing - Trend)
[14:11] - Rule #4 (14 IAR Alternating Inc/Dec Points - Over Control)
[15:38] - Rule #7 (15 IAR within 1 Sigma of mean - Under stratification)
[17:02] - Rule #5 (2/3 Greater than 2 Sigma - Going Out of Control)
[18:22] - Rule #6 (4/5 Greater than 1 Sigma - Going Out of Control)
[18:53] - Rule #8 (8 IAR Greater than 1 Sigma Either Side - Mixture)
[19:55] - Data Labels Column
[21:26] - In Control column
[21:42] - Formatting & Update Chart Data
[26:30] - Bonus Tip
- - - - - - - - - - - - - - - - - - - -
Last Video (Simple ImR Chart): • Unlock the Power of Im...
Control Chart Basics: [ / ugcb7vlp0ts ]
Full Control Chart Playlist: [ • Control Charting ]
- - - - - - - - - - - - - - - - - - - -
Check out my channel for more videos and tutorials for engineers!
Like, follow, share and don't be afraid to drop me a comment/message with feedback, questions, or video suggestions!
Thanks for watching!
Subscribe for More! bit.ly/2PmVzwJ
Channel Link: / @theengineeringtoolbox...
- - - - - - - - - - - - - - - - - - - -
Other great content!
Top Free Engineering Software:
• EVERY Engineer Should ...
Removing Outlier from Excel Pivot Table (Pt 1):
• Excel Removing Outlier...
- - - - - - - - - - - - - - - - - - - -
Follow and interact!
Twitter: / theengtoolbox
Facebook: / theengineeringtoolbox
- - - - - - - - - - - - - - - - - - - -
#spc
#processcontrol
#engineeringtoolbox
#EngineeringTutorials
#Engineering

[@hai-duynguyen8429]

I just wanted to come back to this video and update on my usage of this approach. Ever since I introduced it to my work operations, you wouldn't believe the amount of time shaved off to help visualize day to day activities in a semiconductor Fab. I recently moved to Austin and as part of my interview, I mentioned building out completed control charts for process and this had been instrumental in helping me land a new job. So thank you again.

[@TheEngineeringToolboxChannel]

So happy to hear that! Thank you for sharing.

[@user-rb6sj9es7l]

This is a great tutorial, the only change I would make is I have also added the USL and LSL (Upper and Lower Size Limit) for when your data is a measurement you can not only check your process is in control but your part is in spec as well.

[@zorbd2088]

Exactly what I'm looking for. Thank you!!

[@matresshead]

This is a fantastic tutorial! Thanks for taking the time to make this!

[@TheEngineeringToolboxChannel]

No problem! I wasn’t able to find anything online that showed how to do this so I decided to share. Glad you found it useful!

[@louarthur7648]

This is super! I am doing some work to analyze a web application for transaction performance and your work here has helped me greatly. We test once per minute bringing back a date:timestamp and the #seconds to complete a transaction. I have looked at a number of similar videos and to-date yours is the most explanatory and comprehensive. I have implemented the simple control chart in your first video, working on expanding it today to this more robust version. I do have some questions on its applicability to my data that I'd like to email you about. Thank you.

[@TheEngineeringToolboxChannel]

Awesome. Glad you enjoyed. Definitely feel free to email me at theengineeringtoolbox.com! I’d be happy to discuss further

[@joagj1978]

Thank you very much for this. This is something that I will definately adapt to our purposes.

[@TheEngineeringToolboxChannel]

Awesome! Glad you found it useful

[@TheEngineeringToolboxChannel]

Let me know what you thought of this one! I know it was long but I have never seen this done anywhere else so I had to share!

[@vinayjoshy2071]

Great stuff! Could you share the final excel file?

[@danielmoser1]

Could you share the final excel file?

[@TheEngineeringToolboxChannel]

Sorry, But i don’t not share my files. Everything you need to create it on your own is in this video and the previous one on ImR charts.

[@alhasanalkeddah9031]

Thank you very much for this. i added some formula to make it more benifit for user

[@VitaliyKolesov]

The "2/3" and "4/5" both give false positive conditional formatting. I have added the extra verification for the last point to actually satisfy the criteria of being one of these "2" or "4", and being on the same side of the mean: =IF(ROW()-ROW(Table1[[#Headers],[2 - 9 IAR Same Side]])+2Sigma]]=1,SUM(OFFSET([@[I>+2Sigma]],-2,0,3))>=2),AND([@[I+1Sigma]],-4,0,5))>=4),AND([@[I

[@amitsondhi333]

Can you please help clarify? The original test seems to ensure that they are all on the same side of the mean. E.g., if the test would not result in a positive if two are more than +2Sigma and two are less than -2sigma; in that case both conditionals of the OR statement would return false

[@lewnamesoj]

Hi great explanation i wonder if you could explain something to me. Im trying to apply all what you explained the problem is that as part of a quality plan not all ros have data for some characteristics. How can you solve this issue i mean having rows without data. I think my excel is taking that spaces as a number.

[@TheEngineeringToolboxChannel]

Right click the chart, click “select data”, then click hidden and empty cell button. You can adjust setting as needed but believe you might be looking for “connect data points” option to be on.

[@VitaliyKolesov]

Great tutorial! Excel can do an impressive job when operated by skilled professional! Thank you for this video! Could you maybe provide any good literature references which explain well about how to interpret the SPC Charts and all these OOC signals? Would really appreciate!

[@TheEngineeringToolboxChannel]

DR. Donald J Wheeler is widely consider to be one of the top experts of our time on this topic. www.amazon.com/Understanding-Statistical-Process-Control-Wheeler/dp/0945320698/ref=mp_s_a_1_1?dchild=1&keywords=understand+process+control+wheeler&qid=1601422045&sr=8-1

[@asmamelhem4035]

For the 6 data points NELSON RULE 3, if I have the same result repeated for example (105.0) for all results, then the formula will show true although the results are not increasing nor decreasing. can you please help to adjust the formula?

[@TheEngineeringToolboxChannel]

Good catch...Hoping i can get time to do a follow up video that shows how to fix this and some of the other bugs with these formulas.

[@martinbldel4120]

Hi. This is fantastic work, and I going to use it for whenever working with trending and control charts. However, with reference to your other videos (which are at least as great as this one), would it be possible to create "buttons" in excel that can 'switch on' the different tests when needed (for example only in the start-up process), without needing to recode the columns?

[@TheEngineeringToolboxChannel]

This is a great idea! I would creat an active x control checkbox for each rule and tie it’s value to a hidden cell for each rule. Then reference that cell in the formula for the various rule columns in the table. So I.e. add an if statement to the existing formula columns like “if(CELL=true, , NA())”. So if the checkbox is checked for a given rule, then show the resulting value for each rule column, otherwise make it NA so that datapoint doesn’t show up in the formatted series of that rule column. Hope this makes sense!!!

[@rahulbanerjee1234]

I am having one problem though. Values in one column are getting connected by a straight line. Hence I am getting multiple lines. For example: if the range is like this: Date A B 1-May #NA 1 2 - May 50 #NA 3-May 40 #NA 4-May #NA 45 the 1 and 45 of column B are getting connected by a straight line. Can you please help me out with this? Thanks a lot

[@TheEngineeringToolboxChannel]

Ahh I think i see what you are saying. Id be happy to help over email if you would like, but If i am understanding your problem correctly...I think what you need to do is right click on your chart and then click "Select Data". In the select data form there is a button in the bottom left that says "HIdden and Emptry Cells". Click that and adjust those setting until you get the results you want. I think what you want is "Show empty cells as: Connect data points with Line"? It's just much easier if we are looking at the same document instead of trying to describe things over text so if you would like Id be happy to take a look... [e-mail].😄

[@rahulbanerjee1234]

Thanks a lot 😊

[@rahulbanerjee1234]

I am having one problem though. Values in one column are getting connected by a straight line. Hence I am getting multiple lines. For example: if the range is like this: Date A B 1-May #NA 1 2 - May 50 #NA 3-May 40 #NA 4-May #NA 45 the 1 and 45 of column B are getting connected by a straight line. Can you please help me out with this? Thanks a lot

[@TheEngineeringToolboxChannel]

@@rahulbanerjee1234 See above ;) Thanks again for watching!

[@markenriquez6937]

You mention that this not good data for a control chart... what do you do when your process doesn't provide data good enough to be evaluated for a control chart? Just plot it an use the control chart as a reference as you make improvements until it is good enough?

[@TheEngineeringToolboxChannel]

I probably should have said it differently…. But in short yes you are correct. There really isn’t a dataset out there that CANT be evaluated by a control chart. In my case I was trying to make the point that the dataset I had was clearly indicating a very unstable process. So beyond the control chart telling me I have an unstable process, there wouldn’t be much value in using the control chart for ongoing process control. There would be too much noise and I wouldn’t know what to react to. Ultimately a control chart is meant to tell us when to take action. As you said, the process would have to be improved to a point where it is relatively stable so we are able to identify special cause from normal cause. So if I wanted to monitor the processes control going forward, I would not be able to do so effectively until I understood the sources of special cause, and eliminated enough of the variation to get the process to a state of reasonable control. So There is always value in using control charts to evaluate a process. There first way is to tell if process is in or out of control. If it’s in control, great….Lock control limits and monitor for special cause going forward. If not in control, sources of special cause must be identified and eliminated (or at least identified and understood). Often times, if the process is mostly in control with a few OC points outside the 3sigma limits, you can remove those datapoints from the control limit calc before locking limits. But ONLY if you are able to identify the special cause behind them.

[@birddog8083]

Hey. This is great. Made my own but can't seem to get the 14 alternating rule to work. Just get NA for everything. Tried adding commas to the 010101.... didn't work. Tried removing the "" didn't work. Ensured fields were general and nothing worked. Any advice?

[@TheEngineeringToolboxChannel]

Hey Brent, Hard to say without seeing your project. Can you send me your document and I can take a look? [e-mail]

[@andrewthares]

@@TheEngineeringToolboxChannel I am having the same problem as Brent. Were you able to figure out what the problem was? That might help me fix the issue on my end as well.

[@TheEngineeringToolboxChannel]

Andrew Thares did you try both concatenation formulas? Try both “CONCAT()” and “CONCATENATE()”

[@joagj1978]

If you try to go to the "Formula" menu and "Evaluate formula", you should be able to see the result of the CONCATENATE() function and then replicate that.

[@joagj1978]

Hi again. Edit to my previous response. I didn't have Excel available, so I couldn't actually test this out. From what I could find out, the CONCATENATE() function can't combine ranges in that fashion. It needs an array, otherwise it will only return the first value. The CONCAT() function is supposed to be able to do this, but reportedly you'll need an Office 365 subscription to have this function available. I did however find a "solution", using CONCATENATE(), by using multiple offset statements to build the array. It doesn't look pretty but here it is (note the table and header names if copying and pasting): =IF(ROW()-ROW(Table_Data1[[#Headers],[14 IAR Alternating]])

[@amitsondhi333]

Hey man, thanks for making this. Would it be OK if I replicate (with a few changes) what you have done here and use that in my project catalog on Upwork to showcase the kind of work I can do? I'll be typing out and creating everything myself by following along with this video so I don't think I'll be stepping on any of your rights but I still wanted to check. Thanks and much respect! - Amit

[@TheEngineeringToolboxChannel]

That is fine with me. I am all for getting these tools out into the world. Thanks for the support and asking!

[@amitsondhi333]

@@TheEngineeringToolboxChannel Alright, thanks a lot man. Cheers!

[@happyimhealthy]

Good day to you Engineer. I was able to follow your instructions and I was testing the Chart. Could you please check the formula for rule 4 Oscillation, 14 in a row alternating? I tried it having data increasing and decreasing fourteen or more times. The chart does NOT detect the pattern.

[@TheEngineeringToolboxChannel]

You are correct, the 2/3 and 4/5 also need slight tweaks to get this to work. Hopefully this video gets you close enough to fix the shortcomings!

[@happyimhealthy]

@@TheEngineeringToolboxChannel thank you for your reply. I tried and was successful to finally see Rule 4 Oscillation, 14 IAR alternating work. Your solution was really tremendous help for me. I also tweaked the 2/3 >2 Sigma and 4/5 >1 Sigma. Now all the 8 Nelson Rules are working! 🤓🤓🤓 I'm planning to create Power BI Control Chart, and I give the credit to your work on the videos for inspiring me.

[@ElIng.bat8652]

@@happyimhealthy Could you share how you managed to solve it, I would like to have the 8 rules working, correctly

[@theolorniehila4343]

Can I also do this for Xbar -R?

[@TheEngineeringToolboxChannel]

Sure can! kzbin.info/www/bejne/nHOYlp5jgLmLm9U

[@theolorniehila4343]

Thank you!!

[@nrubale79]

Can you please share the excel file? Thank you

[@TheEngineeringToolboxChannel]

Sorry, I don’t share the documents. Though I do outline exactly how to create it for yourself 😁