How to Make a Control Chart in Excel With Control Limits

A control chart in Excel shows your data over time with three extra lines: the average in the middle and an upper and lower control limit on either side. Points that land outside the limits, or odd patterns inside them, signal that something changed and is worth investigating. Excel has no built-in control chart, but you can build one in a few minutes with AVERAGE, ABS and a line chart. This guide makes an individuals chart, the most common type for one measurement per day, batch or week.

What a control chart shows#

A control chart separates normal, everyday variation from unusual changes. Every process varies a little: delivery times, fill weights and call lengths never repeat exactly. The control limits mark how far the data normally wanders. Inside them, the variation is routine. Outside them, something probably happened, such as a new supplier, a broken machine or a staff shortage.

The chart has four parts:

The examples use twelve days of average delivery times in minutes, in A1:B13:

Day numbers are in column A and minutes in column B, from row 2 to row 13.

Step 1: Calculate the moving ranges#

A moving range is the size of the jump from one value to the next, ignoring direction. In C3, type and fill down to C13:

=ABS(B3-B2)

ABS removes the minus sign, so a drop of 2 and a rise of 2 both count as 2. Row 2 stays empty because day 1 has no previous day. The eleven moving ranges add up to 47.

Step 2: Calculate the mean and the control limits#

Put the summary numbers in their own cells so the chart can refer to them:

The 2.66 is a standard constant for individuals charts. The average moving range divided by 1.128 estimates the standard deviation of the process, and control limits sit three standard deviations from the mean: 3 ÷ 1.128 ≈ 2.66. If the LCL comes out below zero for something that can't be negative, such as a count, use 0. The AVERAGE function guide covers the first two formulas.

Step 3: Add the limit columns for the chart#

A chart needs each line as a column of values, so repeat the mean and limits on every row. In D2, E2 and F2, type these and fill down to row 13:

=$I$1
=$I$3
=$I$4

The $ signs lock the references. Without them, filling down would shift I1 to I2, I3 and so on, and the "flat" lines would jump about. If the dollar signs are new, read how absolute references work.

Add headers in row 1: Mean, UCL and LCL. Now columns B, D, E and F hold everything the chart needs.

Step 4: Plot the control chart#

Build an ordinary line chart from the data and the three limit columns:

1. Select A1:B13, then hold Ctrl (Mac: ⌘) and select D1:F13.
2. Choose Insert > Line > Line with Markers.
3. Right-click the UCL line, choose Format Data Series, and make it a dashed red line with no markers. Do the same for the LCL.
4. Make the mean line solid grey with no markers.
5. Add a chart title, such as "Delivery time, minutes".

The result is a line of daily points between two dashed limits. Day 10, at 44 minutes, sits above the UCL of 42.87. For more on formatting lines, see the line chart guide. The technique of drawing a flat line from a column of repeated values is the same one used to add a target line to a chart.

To make out-of-control points stand out, add one more column. In G2, fill down:

=IF(OR(B2>$I$3,B2<$I$4),B2,NA())

It repeats the value only when it's outside the limits. NA() gives #N/A, which a line chart skips. Add column G to the chart, and format it with large red markers and no line.

STDEV.S limits or moving range limits?#

A common shortcut is to set the limits at the mean ± 3 × STDEV.S. On this data, that gives a UCL of about 44.00 and an LCL of about 19.00, wider than the moving range limits. Day 10 is now just inside the upper limit, and the unusual day disappears into "normal".

That happens because STDEV.S measures the spread of all twelve values together, so the unusual day itself pushes the standard deviation up. The moving range method looks only at neighbouring days, so one unusual point affects it less. That's why textbook individuals charts use the moving range. The standard deviation guide explains STDEV.S itself.

How to read a control chart#

Look for points outside the limits first, then for patterns inside them.

• A point beyond a limit. Find out what happened on that day. Here, day 10 needs a look.
• A long run on one side of the mean. Many rule sets flag about 8 points in a row above or below the centre line as a shift.
• A steady trend. Several points in a row rising or falling can signal wear or drift.

Once you find and fix the cause of an unusual point, many teams recalculate the limits without it and keep those limits fixed for future data. Limits describe the process, not a target. If the process is in control but still too slow for customers, the process itself has to change.

Practise it#

Try the exercise on this page to calculate an upper control limit from moving ranges. Then practise AVERAGE, MIN and MAX in a live spreadsheet and lock cells correctly in the relative vs absolute references lesson. For quick summary statistics around your chart, see AVERAGE, MIN and MAX explained.