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

- - - - - - - - - - - - - - - - - - - -

- - - - - - - - - - - - - - - - - - - -

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!

- - - - - - - - - - - - - - - - - - - -

Other great content!

Top Free Engineering Software:

Removing Outlier from Excel Pivot Table (Pt 1):

- - - - - - - - - - - - - - - - - - - -

Follow and interact!

- - - - - - - - - - - - - - - - - - - -
#spc
#processcontrol
#engineeringtoolbox
#EngineeringTutorials
#Engineering

[BeeryMiller]

Thanks for putting together the video. I was able to follow along and created the necessary graphs I wanted. It took some time with all those formulas, and it works. I thought I was reasonably well versed in Excel, but you opened my eyes to a lot more potential. Thanks again!

[hai-duynguyen]

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.

[KelvinElvidgeUSF]

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.

[joagj]

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

[alhasanalkeddah]

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

[zorbd]

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

[louarthur]

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.

[matresshead]

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

[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!

[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.

[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:



P.S. You cannot imagine the real world data set that made me remark these bugs :D

[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!

[martinbldel]

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?

[amitsondhi]

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

[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.

[birddog]

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?

[asmamelhem]

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?

[markenriquez]

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?

[rahulbanerjee]

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

[nrubale]

Can you please share the excel file? Thank you