X-bar and R control chart calculator (SPC) template

	A	B	C	D
1	X-bar and R control chart calculator (SPC)
2	Example data — replace the blue input cells with your own.
3
4	X-DOUBLE-BAR (MM)	R-BAR (MM)	CPK	POINTS OUT OF CONTROL
5	25.002	0.042	1.83	2
6
7	Inputs
8	Subgroup size (n)	5	The Samples tab holds 5 measurements per subgroup. Use a size from 2 to 5.
9	Upper specification limit (USL, mm)	25.10
10	Lower specification limit (LSL, mm)	24.90
11
12	Constants for n (from the Constants tab)
13	A2 (X-bar limit factor)	0.577
14	D3 (lower R limit factor)	0.000
15	D4 (upper R limit factor)	2.114
16	d2 (sigma estimate factor)	2.326

	A	B	C	D	E	F	G	H	I	J	K	L
1	Subgroup measurements
2	One row per subgroup of measurements. Blue cells are inputs; the other columns are formulas.
3	Enter subgroups in order. Flags compare each subgroup with the control limits on the Summary tab.
4
5	Subgroup no.	Date	Measurement 1 (mm)	Measurement 2 (mm)	Measurement 3 (mm)	Measurement 4 (mm)	Measurement 5 (mm)	n (count)	X-bar (mm)	Range (mm)	X-bar flag	R flag
6	1	Sep 7, 2026	24.970	24.982	24.986	25.003	24.989	5	24.986	0.033	OK	OK
7	2	Sep 8, 2026	24.973	25.022	25.035	25.021	25.029	5	25.016	0.062	OK	OK
8	3	Sep 9, 2026	25.033	24.988	25.024	24.996	25.015	5	25.011	0.045	OK	OK
9	4	Sep 10, 2026	25.000	24.988	25.018	24.995	25.024	5	25.005	0.036	OK	OK
10	5	Sep 11, 2026	24.979	25.000	25.008	25.007	25.014	5	25.002	0.035	OK	OK
11	6	Sep 12, 2026	24.992	25.013	25.032	25.002	25.038	5	25.015	0.046	OK	OK
12	7	Sep 13, 2026	25.027	24.991	24.998	25.027	24.983	5	25.005	0.044	OK	OK
13	8	Sep 14, 2026	24.976	25.000	25.012	24.979	25.011	5	24.996	0.036	OK	OK
14	9	Sep 15, 2026	25.034	25.042	25.060	25.021	25.005	5	25.032	0.055	Out high	OK
15	10	Sep 16, 2026	25.006	25.026	24.981	25.011	25.011	5	25.007	0.045	OK	OK
16	11	Sep 17, 2026	24.996	25.019	25.023	25.022	24.998	5	25.012	0.027	OK	OK

	A	B	C	D	E
1	Control chart constants
2	Shewhart factors for X-bar and R charts by subgroup size n. Published reference values; the Summary tab reads them by exact match on n.
3
4	n	A2	D3	D4	d2
5	2	1.880	0.000	3.267	1.128
6	3	1.023	0.000	2.574	1.693
7	4	0.729	0.000	2.282	2.059
8	5	0.577	0.000	2.114	2.326
9	6	0.483	0.000	2.004	2.534
10	7	0.419	0.076	1.924	2.704
11	8	0.373	0.136	1.864	2.847
12	9	0.337	0.184	1.816	2.970
13	10	0.308	0.223	1.777	3.078
14
15	Standard Shewhart control chart factors, as tabulated in ASTM E2587 and in Montgomery, Introduction to Statistical Quality Control.

	A
1	X-bar and R control chart calculator (SPC)
2	Notes: what this workbook does, how to use it and the method behind it.
3
4	What it does
5	Calculates X-bar and R control limits from subgroups of five measurements, flags subgroups outside the limits and estimates process capability (Cp and Cpk).
6	The example data is fictional: 25 subgroups of shaft diameter in millimeters. Two subgroups fall outside the limits so that the flags show something.
7	The Constants tab holds the Shewhart factors for subgroup sizes 2 to 10.
8
9	How to use it
10	1. On the Summary tab, enter the subgroup size and the upper and lower specification limits.
11	2. On the Samples tab, type subgroup numbers, dates and measurements in the blue columns, up to 50 subgroups.
12	3. Read the control limits, sigma estimate, Cp and Cpk on the Summary tab.
13	4. Check the Status column on the Samples tab. Investigate each flagged subgroup before you revise the limits.
14	5. Recalculate the limits only after the process is stable and the flagged causes are understood.
15
16	Formulas and method

What does this template do?

An X-bar and R chart tracks a process over time by plotting the average and the range of small samples, called subgroups. This workbook is for quality engineers and production supervisors who monitor a dimension and need control limits and a capability figure.

The Samples tab holds subgroups of five measurements with their dates. For each subgroup the sheet calculates the mean and the range. The Summary tab computes the grand mean, the average range, the X-bar limits (grand mean plus or minus A2 times the average range) and the R limits (D4 and D3 times the average range). It estimates sigma as R-bar divided by d2, then gives Cp and Cpk from the specification limits.

Each subgroup is flagged when its mean or range falls outside the limits. The factors come from the Constants tab for subgroup sizes 2 to 10. The example data is fictional: 25 subgroups of a machined shaft diameter, with two subgroups outside the limits.

What’s inside

X-bar and R control limits from the average range and Shewhart factors
Flags for each subgroup mean and range outside its limits
Sigma estimated as R-bar divided by d2
Cp and Cpk from the specification limits
Constants table for subgroup sizes 2 to 10

Which tabs does the workbook have?

Tab	What it holds
Summary	Inputs, the constants for n, control limits, capability and out-of-control counts.
Samples	Subgroup measurements with the mean, range and control flags for each subgroup.
Constants	Shewhart control chart factors for subgroup sizes 2 to 10.
Notes	What the workbook does, the formulas in words, assumptions and limits.

What formulas does this template use?

This template holds 323 formulas in 635 cells across 4 tabs, so 51% of its cells calculate. They use 13 distinct functions; the longest formula is 272 characters and 111 of them read from another tab.

Function	Uses	What it does
`IF`	912	one result when a test is true, another when false
`ISNUMBER`	100	true for a number
`NOT`	100	reverses true and false
`AVERAGE`	52	mean of numbers
`COUNT`	51	counts numeric cells
`MIN`	51	smallest value
`AND`	50	true when every test is true
`MAX`	50	largest value
`OR`	8	true when any test is true
`COUNTIF`	5	counts cells meeting one condition
`IFERROR`	4	swaps an error for a fallback value
`VLOOKUP`	4	looks a value up in a column

The 12 most used of 13 functions. Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

Enter the subgroup size and the upper and lower specification limits on the Summary tab.
Type subgroup numbers, dates and measurements on the Samples tab, up to 50 subgroups.
Read the control limits, sigma estimate and capability on the Summary tab.
Check the Status column on the Samples tab for subgroups outside the limits.
Investigate each flagged subgroup before you recalculate the limits.

What is it good for?

Monitoring a machined dimension at the start of each shift
Setting trial control limits from the first 25 subgroups
Reporting Cpk for a critical dimension
Teaching the X-bar and R chart method

Questions about this sheet

What are the control limits based on?

The X-bar limits are the grand mean plus or minus A2 times the average range. The R limits are D4 and D3 times the average range. The factors come from the Constants tab for the subgroup size you enter.

Why does the sheet flag a point that is inside the specification limits?

Control limits describe the variation of the process, and specification limits describe what the customer accepts. A point can sit inside the specification and still outside the control limits, which signals a change in the process.

Is Cpk reliable with few subgroups?

It rests on the sigma estimate from the average range, so it is only as reliable as the data behind it. Confirm that the process is stable before you rely on Cpk.

What subgroup sizes does the sheet support?

The Samples tab has five measurement columns, so the subgroup size must be from 2 to 5 here. The Constants tab lists factors up to size 10 for reference.