How are the X-bar and R control limits calculated?

From the baseline subgroups: the centre line is the grand mean of the subgroup means and R-bar is the average range. Sigma is estimated as R-bar divided by d2, and the X-bar limits are the grand mean plus or minus 3 times sigma divided by the square root of n. With a grand mean of 25.0020 mm, R-bar of 0.0061 mm and n = 5 (d2 = 2.326), sigma is 0.0026 mm and the limits are about 24.9985 and 25.0055 mm. The R chart limits are D3 and D4 times R-bar.

Why does the template compute A2 instead of using the printed 0.577?

A2 is defined as 3 divided by d2 times the square root of n, and the printed value is that number rounded to three decimals. For n = 5 the exact value is 0.5769. Calculating it keeps the limits identical to the sigma-based formula used by the Xbar Control Chart Limits calculator on this site; the difference from the rounded table value is about 0.0001 times R-bar.

How many subgroups do I need before the limits mean anything?

The AIAG SPC manual suggests 25 or more subgroups containing about 100 or more individual readings for initial limits. Start with the baseline set to 0 (all subgroups) while you establish control, remove or explain any special causes, then set the baseline to the number of stable subgroups so new data is judged against those fixed limits.

Which out-of-control rules does it check?

A subgroup mean above the upper or below the lower control limit, a range above the R chart upper limit, and a run of points on one side of the centre line. The run length is 7 by default as in the AIAG manual; set 8 for the Western Electric rule or 9 for the Nelson rule if you want fewer false alarms. Trend and zone rules are not evaluated.

Can I put the drawing tolerance on the X-bar chart?

No, and the template does not. The X-bar chart plots subgroup means, which vary much less than individual parts, so a tolerance line on it misleads. Control limits come from the process; specification limits come from the drawing. Use the Process Capability template to compare the process spread with the tolerance.

What subgroup sizes does it support?

Two to five readings per subgroup, set on Setup. The constants change with the size: d2 is 1.128 for n = 2 and 2.326 for n = 5. A subgroup with a different number of readings is left out of the limits and its Signal column says Reading count is not n.

Quality Systems and Compliance

SPC X-bar and R Control Chart Excel Template

Log subgroups of measurements and get X-bar and R control limits, both control charts and every out-of-control signal named in words, with no macros.

Overview

This X-bar and R control chart template is a working Excel workbook for quality engineers, process engineers and cell leaders who run statistical process control on a measured characteristic. It ships with 30 example subgroups for a fictional CNC turning cell, so both charts and every signal are populated when you open it; replace the example rows with your own and everything recalculates.

Each row is one subgroup of two to five readings taken close together in time. The workbook calculates the subgroup mean and range, then builds the limits from a baseline: the grand mean and the average range of the first N subgroups. The within standard deviation is estimated as R-bar divided by d2, the X-bar limits are the grand mean plus or minus three standard errors (the same as A2 times R-bar), and the R chart limits are D3 and D4 times R-bar. Later subgroups are judged against the baseline limits, so a drift cannot widen its own limits.

In practice, enter each subgroup as it is measured and read the Signal column. It names the first rule that fires: a mean above the upper limit or below the lower limit, a range above its limit, or a run of points on one side of the centre line (7 by default, adjustable). In the example data, tool wear pushes the diameter above the upper control limit at subgroup 28, and the Notes column records the offset correction that brings it back.

What this template includes

Subgroups table: subgroup number, date and up to five readings per subgroup, with mean and range calculated per row
Signal column that names the first rule that fires: above UCL, below LCL, range above UCL, a run on one side of the centre line, wrong reading count or duplicate number
Control limits from a baseline you choose (the first N subgroups, or all), so later subgroups are judged against fixed limits
Setup for the characteristic, unit, subgroup size (2 to 5), baseline length and run-rule length, with the public d2, D3 and D4 constants
Dashboard with X-bar and R charts of the latest 30 subgroups, tiles for signals, grand mean, R-bar and within sigma, and the full limits table
Method sheet with every formula and its source, and a Verify sheet that re-checks the formulas against an independent reference (Self-check: PASS)
CSV with live formulas for the subgroup table; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it to run SPC on a critical characteristic from a control plan, to establish control limits during a launch or PPAP run, or to show a customer that a feature is in statistical control.

How to use it

On Setup, name the characteristic and its unit, and set the subgroup size, the number of baseline subgroups and the run-rule length.
On Subgroups, replace the example rows with your own: one row per subgroup with its number, date and readings in time order.
Read the Signal column after each subgroup. Anything other than In control names the rule that fired; record the cause and action in Notes.
Open the Dashboard to see the X-bar and R charts of the latest 30 subgroups, the signal count and the current limits.
Once 20 to 25 baseline subgroups show no signals, keep the baseline count fixed so new subgroups are judged against those limits.

Frequently Asked Questions

How are the X-bar and R control limits calculated?: From the baseline subgroups: the centre line is the grand mean of the subgroup means and R-bar is the average range. Sigma is estimated as R-bar divided by d2, and the X-bar limits are the grand mean plus or minus 3 times sigma divided by the square root of n. With a grand mean of 25.0020 mm, R-bar of 0.0061 mm and n = 5 (d2 = 2.326), sigma is 0.0026 mm and the limits are about 24.9985 and 25.0055 mm. The R chart limits are D3 and D4 times R-bar.
Why does the template compute A2 instead of using the printed 0.577?: A2 is defined as 3 divided by d2 times the square root of n, and the printed value is that number rounded to three decimals. For n = 5 the exact value is 0.5769. Calculating it keeps the limits identical to the sigma-based formula used by the Xbar Control Chart Limits calculator on this site; the difference from the rounded table value is about 0.0001 times R-bar.
How many subgroups do I need before the limits mean anything?: The AIAG SPC manual suggests 25 or more subgroups containing about 100 or more individual readings for initial limits. Start with the baseline set to 0 (all subgroups) while you establish control, remove or explain any special causes, then set the baseline to the number of stable subgroups so new data is judged against those fixed limits.
Which out-of-control rules does it check?: A subgroup mean above the upper or below the lower control limit, a range above the R chart upper limit, and a run of points on one side of the centre line. The run length is 7 by default as in the AIAG manual; set 8 for the Western Electric rule or 9 for the Nelson rule if you want fewer false alarms. Trend and zone rules are not evaluated.
Can I put the drawing tolerance on the X-bar chart?: No, and the template does not. The X-bar chart plots subgroup means, which vary much less than individual parts, so a tolerance line on it misleads. Control limits come from the process; specification limits come from the drawing. Use the Process Capability template to compare the process spread with the tolerance.
What subgroup sizes does it support?: Two to five readings per subgroup, set on Setup. The constants change with the size: d2 is 1.128 for n = 2 and 2.326 for n = 5. A subgroup with a different number of readings is left out of the limits and its Signal column says Reading count is not n.