Excel guide · 6 min read
How to make a control chart in Excel
=AVERAGE(data), moving range =ABS(B3-B2), sigma = average moving range / 1.128, and limits = centre +/- 3 x sigma. Put the centre and limits in columns next to the data, chart all four as lines, and flag points outside the limits with =IF(OR(B2>UCL,B2<LCL),"Out","").Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Sample | Weight (g) | Moving range | Centre | UCL | LCL | Flag | Centre line | 502 | |
| 2 | 1 | 501.9 | 502 | 503.27379619 | 500.72620381 | Average moving range | 0.47894736842 | |||
| 3 | 2 | 502.1 | 0.2 | 502 | 503.27379619 | 500.72620381 | Sigma (MR / 1.128) | 0.42459873087 | ||
| 4 | 3 | 501.6 | 0.5 | 502 | 503.27379619 | 500.72620381 | UCL | 503.27379619 | ||
| 5 | 4 | 502.4 | 0.8 | 502 | 503.27379619 | 500.72620381 | LCL | 500.72620381 | ||
| 6 | 5 | 502 | 0.4 | 502 | 503.27379619 | 500.72620381 | MR upper limit | 1.5647210526 | ||
| 7 | 6 | 501.4 | 0.6 | 502 | 503.27379619 | 500.72620381 | ||||
| 8 | 7 | 501.8 | 0.4 | 502 | 503.27379619 | 500.72620381 | ||||
| 9 | 8 | 502.5 | 0.7 | 502 | 503.27379619 | 500.72620381 | ||||
| 10 | 9 | 502.2 | 0.3 | 502 | 503.27379619 | 500.72620381 | ||||
| 11 | 10 | 501.7 | 0.5 | 502 | 503.27379619 | 500.72620381 | ||||
| 12 | 11 | 502 | 0.3 | 502 | 503.27379619 | 500.72620381 | ||||
| 13 | 12 | 502.6 | 0.6 | 502 | 503.27379619 | 500.72620381 | ||||
| 14 | 13 | 502.3 | 0.3 | 502 | 503.27379619 | 500.72620381 | ||||
| 15 | 14 | 501.5 | 0.8 | 502 | 503.27379619 | 500.72620381 | ||||
| 16 | 15 | 501.9 | 0.4 | 502 | 503.27379619 | 500.72620381 | ||||
| 17 | 16 | 502.2 | 0.3 | 502 | 503.27379619 | 500.72620381 | ||||
| 18 | 17 | 501.6 | 0.6 | 502 | 503.27379619 | 500.72620381 | ||||
| 19 | 18 | 502.1 | 0.5 | 502 | 503.27379619 | 500.72620381 | ||||
| 20 | 19 | 502.4 | 0.3 | 502 | 503.27379619 | 500.72620381 |
Download this sheet as an Excel practice file (.xlsx)
Set up the data and the centre line
A control chart answers one question: is the process behaving the same way over time, or did something change? The sheet has the fill weight of a 500 g bag, one bag checked every hour, in time order. Order matters: a control chart of sorted data is meaningless.
The first 20 samples (B2:B21) are the baseline that sets the limits. Samples 21 to 25 are new and get judged against those limits. Don't recalculate the limits every time new points arrive, or a slow drift pulls the limits along with it and never shows up.
The centre line in J1 is the average of the baseline. About 20 to 25 points is the usual minimum for limits you can trust.
Result: 502
502.00 g: the centre line, from the 20 baseline bags.
Sigma from the moving range, not STDEV.S
With one measurement at a time (an individuals chart), estimate the short-term variation from the moving range: the jump from one point to the next. C3 is =ABS(B3-B2), filled down; the first row has no moving range. Average the baseline ranges in J2 and divide by 1.128, the constant that turns the average range of two neighbouring points into a standard deviation.
Why not STDEV.S? It measures the spread of all points around one average, so a shift in the process inflates it and widens the limits, hiding the very shift you want to see. On this sheet =STDEV.S(B2:B26) over all 25 points is 0.58, while the moving-range sigma from the baseline is 0.42. Limits built from the 0.58 (average +/- 3 x STDEV.S of everything) would put the upper limit at 503.96 g, and sample 24 at 503.8 g would pass as normal. The standard deviation guide covers STDEV.S vs STDEV.P.
Result: 0.42459873087
0.425 g: sigma from an average moving range of 0.479 g.
Upper and lower control limits
The limits sit 3 sigma above and below the centre: J4 is =J1+3*J3 and J5 is =J1-3*J3. A stable process stays inside them almost all the time, so a point outside is a signal worth investigating.
Control limits are not specification limits. The spec says what the customer accepts (for example at least 500 g); the control limits say what the process actually does. A process can be in control and still make bad bags, or out of control while every bag is within spec. Whether it fits the spec is a capability question: see Cp and Cpk.
For a matching moving-range chart, the centre line is the average moving range and the upper limit is =3.267*J2 (J6, 1.56 g); it has no lower limit.
Result: spills 1 row × 2 columns
| 500.72620381 | 503.27379619 |
LCL 500.73 g and UCL 503.27 g, 3 x 0.425 either side of 502.00.
Build the chart
Columns D, E and F repeat the centre and limits on every row (=$J$1, =$J$4, =$J$5). They look odd in a table, but they are what draws the three flat lines on the chart. Chart the weights and those three columns together as a line chart.
- Select B1:B26, then hold Ctrl and select D1:F26.
- Go to Insert > Insert Line or Area Chart > Line with Markers.
- Right-click the Centre, UCL and LCL lines one by one > Format Data Series > Marker > None; make the limits dashed red and the centre a thin solid line.
- Right-click the vertical axis > Format Axis and set Minimum and Maximum just outside the limits (for example 500 and 504), so the points aren't squashed in the middle.
- Right-click the chart > Select Data > Horizontal (Category) Axis Labels > Edit and pick A2:A26 for sample numbers.
Flag out-of-control points
Column G marks any point outside the limits: =IF(OR(B2>$J$4,B2<$J$5),"Out",""). To list them in one cell in Excel 365, filter the sample numbers. Sample 24 (503.8 g) is above the upper limit.
A point outside the limits is the clearest signal, but not the only one. Look at the run around it: samples 21 to 25 are all above the centre line and climbing, and that started before any point crossed a limit. Sample 25 (503.2 g) is back inside the limits, but the process has not come back to 502. A common rule flags 8 points in a row on the same side of the centre (5 so far here, so it hasn't fired yet); on row 9 you could test it with =IF(OR(COUNTIF(B2:B9,">"&$J$1)=8,COUNTIF(B2:B9,"<"&$J$1)=8),"Run",""), filled down. Pick your rules before you look at the data and write them next to the chart.
When a point is out, find the cause (here maybe a filler setting changed after sample 20), fix it, and note it on the chart. Only recalculate the limits after a deliberate change to the process, from 20 or more new points.
Result: 24
24: the only sample outside the limits (503.8 g, above the UCL of 503.27 g).
Which chart to use
An individuals and moving range (I-MR) chart, as here, fits data that comes one value at a time: one bag an hour, one invoice-processing time per day, daily delivery times, a monthly KPI. If you measure several items at once (5 bags every hour), use an X-bar and R chart instead: plot the subgroup averages, and the limits use the average subgroup range with constants for the subgroup size. The steps in Excel are the same; only the limit formulas change.
For counts of defects or a percentage defective, attribute charts (c, u, p charts) have their own limit formulas. And if you just want a smoothed trend rather than a stability test, a moving average is the simpler tool.
Common mistakes
Using STDEV.S of all the data for the limits
A shift in the data inflates STDEV.S and widens the limits. For individual values use the average moving range / 1.128.
Recalculating the limits with every new point
The limits then follow a drift instead of catching it. Fix them from a stable baseline and recalculate only after a deliberate process change.
Confusing control limits with spec limits
Control limits come from the data; spec limits come from the customer. Show them on separate charts or label them clearly.
Charting data that isn't in time order
Sorting or grouping the data destroys the time pattern the chart is meant to show. Keep the original order.
FAQ
How do I calculate UCL and LCL in Excel?
For individual values: UCL = average + 3 x (average moving range / 1.128) and LCL = average - 3 x (average moving range / 1.128). In Excel: =AVERAGE(B2:B21)+3*AVERAGE(C3:C21)/1.128, with C holding =ABS(B3-B2).
Does Excel have a built-in control chart?
No. Excel has no control chart type; you build one as a line chart from the data plus columns holding the centre line and the limits. Add-ins exist, but the formulas above are all you need.
What is 1.128 in a control chart?
It's the d2 constant for a range of two values. Dividing the average moving range by 1.128 estimates the process standard deviation for an individuals chart.
How many data points do I need for a control chart?
About 20 to 25 points for the baseline is the usual guide. With fewer, treat the limits as provisional and recalculate once you have enough stable data.
What is the difference between a control chart and a run chart?
A run chart plots data over time with a median line. A control chart adds limits calculated from the data's own variation, so you can tell normal noise from a real change.
Functions used
AVERAGEMean of numbersABSAbsolute valueSTDEV.SStandard deviationIFPicks a result from a testORTrue if any test is trueFILTERKeeps matching rows, spillsHSTACKJoins arrays side by sideCOUNTIFCounts cells that match
Related guides
- How to calculate Cp and Cpk in Excel
- How to calculate standard deviation in Excel
- How to calculate a moving average in Excel
- How to make a Pareto chart in Excel
- How to calculate a z-score in Excel
- How to calculate a tracking signal in Excel
Updated