Include Top

How Do I Create X-Bar & Range Control Charts in Excel Using SigmaXL?

X-Bar & Range Charts

  1. Open the file Catapult Data – Xbar Control Charts.xls. Each operator fires the ball 3 times. The target distance is 100 inches. The Upper Specification Limit (USL) is 108 inches. The Lower Specification Limit (LSL) is 92 inches.
  2. Select B2:F22; here, we will only use the first 20 subgroups to determine the control limits.
  3. Select SigmaXL > Control Charts > X-Bar & R.
  4. Do not check Use Entire Data Table!

    Data Selection

  5. Click Next. Select Subgroups across Rows, select Shot 1, Shot 2, Shot 3, click Numeric Data Variables (Y) >>; select Operator, click Optional X-Axis Labels >>. Check Tests for Special Causes as shown:
  6. X-Bar Variables

  7. Click OK. Resulting X-bar & R charts:

    X-Bar and R Chart

  8. This is currently a stable catapult process. Subgroups 21 to 25 were added afterwards. To add the additional data to this chart, click SigmaXL Chart Tools > Add Data to this Control Chartt as shown. In Excel 2007/2010 click:

    X-Bar and R with Added Data

  9. Note that the Add Data button does NOT recalculate the control limits. Once control limits are established, they should only be recalculated when a deliberate process change or improvement is introduced. (Control Limits can be recalculated using SigmaXL Chart Tools > Recalculate Control Limits, but a warning message Are you sure that you want to recalculate control limits? is given.)
  10. The Tests for Special Causes report gives us more detail on the recent instability:

    Test for Special Causes

    The X-bar chart and Tests for Special Causes report clearly shows that this process is now out of control with an unstable mean. The process must be stopped, and the Out-of-Control Action Plan must be followed to determine and fix the root cause. In this case, the assignable cause was a change of rubber band requiring a reset of the pull back angle. The use of tests for special causes gave us an early warning of this at observation number 22.

    Note that the Range chart is in-control even though the X-Bar chart is out-of-control.
  11. The tests for special causes can have defaults set to apply any or all of Tests 1-8. Test 2 can be set to 7, 8, or 9 points in a row on same side of CL. Click SigmaXL > Control Charts > “Tests for Special Causes” Defaults to run selected tests for special causes. (Note that these defaults will apply to Individuals and X-bar charts. Test 1 to 4 settings will be applied to Attribute Charts.)
  12. To add a comment to a data point, select SigmaXL Chart Tools > Add Data Label. Select Text Label. Enter a comment as shown. Click on the data point to add the comment. Click Done.

    Add Data Label

  13. Now we will look at Process Capability Indices for this process. Click on Sheet 1 (or press F4 to activate last worksheet). Click SigmaXL > Control Charts > X-Bar & R. Check Use Entire Data Table. Click Next. (Alternatively select B2:F27, press F3.)
  14. Select Shots 1-3, click Numeric Data Variables (Y) >>. Select Historical Limits. These are the limits calculated with the original 20 subgroups.
  15. Click Advanced Options. Enter LSL = 92, Target = 100, USL = 108.
  16. The resulting dialog box settings are shown:

    X-Bar and R Variables

    Tip: Another approach to using Historical Limits, would be to select Specify Subgroup Number for Calculation of Control Limits and specify Subgroup Numbers 1 to 20 for calculation of the control limits.
  17. Click OK. Click X-Bar & R – Proc Cap sheet for the Process Capability report:

    Process Capability Report

    Note the difference between Pp and Cp; Ppk and Cpk. This is due to the process instability. If the process was stable, the actual performance indices Pp and Ppk would be closer to the Cp and Cpk values.

X-Bar & R Charts – Exclude Subgroups

After creating a control chart, you can specify subgroups (or rows) to exclude by using the Exclude Data tool.
  1. Click on Sheet 1 (or press F4 to activate last worksheet). Click SigmaXL > Control Charts > X-Bar & R. Check Use Entire Data Table. Click Next.
  2. Select Shots 1-3, click Numeric Data Variables (Y) >>. Ensure that Calculate Limits is selected. Click OK.
  3. The resulting X-bar & R charts are displayed:

  4. X-Bar and R Chart

    The control limits here were calculated including subgroups 21 to 25 which have a known assignable cause.

  5. To calculate the control limits excluding subgroups 21 to 25, click SigmaXL Chart Tools > Exclude Subgroups. Select Show Highlighted Points for Excluded Subgroups. Enter 21,22,23,24,25 as shown:

Define, Measure, Analyze, Improve, Control

Lean Six Sigma Software Excel Add-in

Simulate, Optimize,

Lean Six Sigma Software Excel Add-in

Web Demos

Our CTO and Co-Founder, John Noguera, regularly hosts free Web Demos featuring SigmaXL and DiscoverSim
Click here to view some now!

Contact Us

Ph: 1.888.SigmaXL (744.6295)