If you want an X̄-R chart in Google Sheets, here is how to build one properly. No sales pitch until the end; the recipe below works and you are welcome to it.
Building it in Sheets
Say each row is a subgroup of five measurements, in B2:F26.
1. Subgroup mean and range
G2: =AVERAGE(B2:F2)
H2: =MAX(B2:F2)-MIN(B2:F2)
Fill both down.
2. Baseline the limits on a fixed window — and this is the part that matters
Use only the rows from a period when the process was behaving, say the first 25 subgroups, and anchor the ranges absolutely so they never grow:
J1: =AVERAGE($G$2:$G$26) grand mean (x̄̄)
J2: =AVERAGE($H$2:$H$26) mean range (R̄)
3. Constants for n = 5
J3: 0.577 A2
J4: 0 D3
J5: 2.114 D4
For other subgroup sizes, the full constants table is here. Note D3 is zero below n = 7: a range chart at n = 5 genuinely has no lower limit, and drawing one at zero is decoration.
4. Limits
X̄ chart: UCL = J1 + J3*J2 CL = J1 LCL = J1 - J3*J2
R chart: UCL = J5*J2 CL = J2 LCL = J4*J2
5. Plot
Add four columns that repeat those constants down every row (=$J$1, =$K$…)
and chart them as lines alongside the means. Same again for the range chart
underneath.
6. Rule 1
=IF(OR(G2>$L$2, G2<$L$4), "OUT", "")
That gives you a correct X̄-R chart with frozen limits, which already puts it ahead of most spreadsheet charts in service.
The three things it will not do
Rules 2 to 8. Rule 1 is one formula. "Nine consecutive points on the same side of the centre line" is a running-count column; "six in a row rising" is another; "two of three in the outer third" needs the sigma zones. Each is maintainable in isolation and a burden together, and they are where most of the early warning lives — rule 1 tells you a part is already bad.
Watching when you are not. A Sheet shows a violation to whoever opens it. The drift that starts on Friday evening is found on Monday.
Remembering what the limits were. When somebody asks in six months why a point in March passed, the answer needs the limit set that was in force in March. A Sheet has whatever is in those cells today.
What is the same
Be clear about this: the arithmetic is identical. A2 is 0.577 in both. If your Sheet is built as above, our chart will agree with it to the last decimal — we test the engine against published worked examples for exactly that reason. If they ever disagree, one of us has a bug and we would like to know which.
What is different
| Google Sheets | SPC Visuals | |
|---|---|---|
| Correct X̄-R maths | Yes, if built carefully | Yes |
| Limits frozen | If you anchor the ranges | Enforced, with the baseline window recorded |
| Nelson rules | Rule 1 realistically | 1–8, configurable per characteristic |
| Alerts | No | Email when a rule trips |
| Data arrives on its own | No | CSV, REST API, PLC collector |
| Shop-floor display | Not really | Full-screen mode, unlimited viewers |
| Audit trail on limit changes | No | Versioned, attributed, timestamped |
| Ad-hoc slicing | Excellent | Deliberately limited |
| Cost | Free | From €169/month per line |
When Sheets is the right answer
One characteristic, one engineer, a chart reviewed weekly. Genuinely: build the Sheet. The recipe above is complete and the maths is right.
It stops being the right answer at about the point where a second person needs to see the chart, or where nobody notices for three days that the process moved.
Check your Sheet against ours
Paste the same numbers into the free control limit calculator — it shows the constants it used, so you can see exactly where any disagreement comes from.