How to Make a Control Chart in Excel: 5 Easy Steps in 2026
A control chart plots a process measurement over time against a center line and two control limits. To make one in Excel, list your values in time order, calculate the average and the moving range, and compute the upper and lower limits. Then plot all four as a line chart and check the points against standard rules.
This guide covers what a control chart shows, which type to use, and the five Excel steps with the exact formulas. It then explains how to read the result and how it differs from a run chart.
What a control chart is
The NIST/SEMATECH e-Handbook of Statistical Methods gives the standard description. "Control charts are used to routinely monitor quality." The chart shows a measured value against sample number or time.
"In general, the chart contains a center line that represents the mean value for the in-control process." Two more horizontal lines sit above and below it: the upper control limit (UCL) and the lower control limit (LCL).
The handbook explains the purpose of those limits. They are set "so that almost all of the data points will fall within these limits as long as the process remains in-control." A point outside them is a signal worth investigating.
The idea goes back to Walter Shewhart in the 1920s. Its value is that it separates routine variation from variation with a specific cause. Without that separation, teams tend to react to every small change.
Which control chart you need
The right chart depends on how you collect data. Two types cover most business and operations uses.
| Chart type | Use it when | Center line | Limits based on |
|---|---|---|---|
| Individuals chart (I-MR) | You record one value per period, such as daily defects or weekly lead time | Average of all values | Average moving range |
| X-bar and R chart | You take small samples of several items at each point, such as five parts per hour | Average of the sample averages | Average sample range |
This guide uses the individuals chart. It suits most spreadsheet data, because business metrics usually arrive as one number per day, week, or batch.
The NIST handbook notes that "Historically, k = 3 has become an accepted standard in industry." Here, k is the number of standard deviations between the center line and each limit. That is why control limits are usually called 3-sigma limits.
How to make a control chart in Excel
The five steps below build an individuals chart from a single column of values. They work in current versions of Excel for Microsoft 365, and the formulas also work in Google Sheets.
Step 1: Lay out the data in time order
Put the time or sample label in column A and the measurement in column B. Start with headers in row 1, and data from row 2.
Order matters more than anything else. A control chart reads left to right in time, so sort by date before you calculate anything. Remove blank rows and make sure every value in column B is a number.
Aim for at least 20 data points before you trust the limits. Fewer points make the average and the moving range unstable.
Step 2: Calculate the center line
Add a column C for the moving range, then a column D for the center line.
The center line is the average of all your values. In D2, enter =AVERAGE($B$2:$B$21) and fill it down to the last row. Every row shows the same number, which is what makes it draw as a flat line.
Adjust the range to match your data. If you have 30 points, use $B$2:$B$31.
Step 3: Calculate the control limits
First calculate the moving range, which is the absolute difference between each value and the one before it. The NIST handbook defines it as "the absolute value of the first difference." In C3, enter =ABS(B3-B2) and fill down. C2 stays empty, because the first value has no predecessor.
Next, find the average moving range. In a spare cell, such as H2, enter =AVERAGE($C$3:$C$21).
Now compute the limits. The handbook's individuals chart formula divides the average moving range by 1.128, then multiplies by 3. In E2, for the UCL, enter =D2+3*$H$2/1.128. In F2, for the LCL, enter =D2-3*$H$2/1.128. Fill both down.
The constant 1.128 converts the average moving range into an estimate of the standard deviation. The handbook notes that it is the value of d2 for a sample size of 2.
Step 4: Build the chart
Select columns A, B, D, E, and F, including headers. Hold Ctrl on Windows or Command on Mac to select non-adjacent columns.
On the Insert tab, open the line chart menu and choose Line with Markers. Excel draws your data as a marked line and the three reference columns as flat lines.
Then tidy it up. Remove the markers from the center line and both limits. Make the limits dashed and the center line solid. Add a chart title that names the metric and the period.
Step 5: Read the chart against the rules
A point outside either control limit is the clearest signal. It means something outside the normal pattern happened, and it deserves a look.
Points inside the limits can still signal a shift. The next section lists the standard rules for reading patterns. Mark any rule violations directly on the chart, so the reader sees the signal without studying the numbers.
Finally, write one sentence under the chart. State whether the process is stable and, if not, which points need investigation.
A worked example
A packaging line records the average fill weight of a bag each day, in grams, for 20 days. The values range from 497.1 to 503.4 grams.
The calculations give an average of 500.2 grams and an average moving range of 1.8 grams. Dividing 1.8 by 1.128 gives an estimated standard deviation of about 1.6 grams.
The UCL is 500.2 plus three times 1.6, or about 505.0 grams. The LCL is 500.2 minus the same amount, or about 495.4 grams. Every daily value falls inside those limits.
Days 12 through 19, though, all sit above 500.2. That run of eight points on one side of the center line matches one of the standard rules. The line is stable in range, but its average appears to have shifted upward. A check shows the filler settings were changed on day 11.
How to read a control chart
The NIST handbook lists the Western Electric Company rules, often called the WECO rules. Each describes a pattern that is unlikely under normal variation.
- Any point above the upper 3-sigma limit or below the lower one.
- Two out of the last three points beyond 2 sigma on the same side.
- Four out of the last five points beyond 1 sigma on the same side.
- Eight consecutive points on one side of the center line.
- Six points in a row trending up or down.
- Fourteen points in a row alternating up and down.
The handbook explains the logic. For a normal distribution, a point outside plus or minus 3 sigma has a probability of about 0.3%. The other patterns are chosen because they are roughly as rare.
It also adds a caution. The extra rules make the chart more sensitive to drifts, but they also increase false alarms. Start with the 3-sigma rule and the run of eight, then add others only if you need earlier warning.
To apply the 1-sigma and 2-sigma rules, add two more pairs of reference lines. Use the same formula with 1 and 2 in place of 3.
Using subgroups: the X-bar and R chart
If you measure several items at each point, such as five parts every hour, use an X-bar and R chart instead. It is really two charts. One tracks the average of each subgroup, and the other tracks the range within each subgroup.
The Excel layout changes slightly. Put each subgroup's measurements across one row, in columns B to F for a subgroup of five. Add a column for the subgroup average with =AVERAGE(B2:F2) and one for the range with =MAX(B2:F2)-MIN(B2:F2).
The limits then use tabled factors that depend on subgroup size. The NIST handbook gives the X-bar chart limits as the grand average plus or minus A2 times the average range. For a subgroup of five, it lists A2 as 0.577. The R chart runs from D3 times the average range to D4 times it. For a subgroup of five, D3 is 0 and D4 is 2.115.
The handbook also sets a boundary for this method. "In general, the range approach is quite satisfactory for sample sizes up to around 10." For larger subgroups, it recommends using subgroup standard deviations instead.
When to recalculate the limits
Control limits describe the process as it was when you calculated them. They should not move every time new data arrives, or the chart loses its ability to show change.
Recalculate only when you have a reason. A deliberate process change, such as a new machine setting or a new supplier, is one. A confirmed shift that you decide to accept as the new normal is another.
When you recalculate, use only the data from after the change. Mark the date on the chart so readers can see where one set of limits ends and the next begins.
Control chart vs run chart
The two are easy to confuse because both plot a measure over time.
| Control chart | Run chart | |
|---|---|---|
| Reference lines | Center line plus upper and lower control limits | Median or average only |
| Main question | Is the process stable, and when did it change? | Is there a trend or shift over time? |
| Calculation effort | Needs a variation estimate | Needs only the median |
| Best for | Ongoing monitoring of a defined process | Early improvement projects and quick checks |
A run chart is a good first step when you have little history. Once you have 20 or more points and want to monitor a process routinely, a control chart gives a clearer signal.
Doing it faster with AI
The Excel method works well for one metric. It gets slow when you track many metrics, or when the data arrives as a raw export that needs cleaning first.
An AI workspace can do the calculation and the flagging in one request. Upload the export to Powerdrill Bloom and ask for an individuals control chart of the metric. Ask for the average, moving range, and 3-sigma limits, and a list of any points that break the WECO rules.
The chart itself is a line chart with three reference lines, which sits within the line chart output its line graph creator page describes. The part that saves time is the rule check across every metric at once.
A useful request is specific. Name the metric column, the date column, and the chart type, and ask for the limits as a table next to the chart. Ask for each flagged point with its date and the rule it broke.
Check the output the same way you would check a spreadsheet. Confirm the average and the moving range against a quick Excel calculation for one metric. If they match, the rest of the batch is likely right. For wider Excel work, the Excel AI assistant page covers the same file-first approach.
Common mistakes
- Using specification limits as control limits. Specification limits come from customer requirements. Control limits come from your own data. They answer different questions.
- Calculating limits from too few points. Twenty points is a practical minimum for an individuals chart.
- Including the shift in the baseline. If a process changed partway through, calculate limits from the stable period only, then extend them forward.
- Adding every rule at once. More rules mean more false alarms. Start simple.
- Treating every signal as a problem. A signal means something changed. Investigate before you act.
For the next step after spotting a signal, a Pareto chart helps rank the likely causes. If you need to test whether a change is real rather than noise, our explainer on statistical significance covers that question.
If you have a process export ready, you can try Powerdrill Bloom and compare its limits with your own Excel calculation.
Frequently asked questions
What is a control chart in simple terms?
A control chart is a line chart of a process measure over time, with a center line and upper and lower control limits. Points outside the limits, or unusual patterns inside them, signal that something in the process has changed.
How do you calculate control limits in Excel?
For an individuals chart, calculate the average of your values and the average moving range. The UCL is the average plus three times the average moving range divided by 1.128. The LCL is the average minus the same amount.
What are the rules for reading a control chart?
The most common are the Western Electric rules. They include any point beyond 3 sigma, two of three points beyond 2 sigma, and eight consecutive points on one side of the center line. NIST cautions that adding rules also increases false alarms.
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 upper and lower control limits calculated from the data's variation. That makes it better for judging whether a process is stable.
How many data points do you need for a control chart?
Twenty data points is a practical minimum for an individuals chart. Fewer points make the average and moving range unstable, so the limits can shift noticeably as new data arrives.
Sources: NIST/SEMATECH e-Handbook, What are Control Charts? · NIST/SEMATECH e-Handbook, What are Variables Control Charts? · NIST/SEMATECH e-Handbook, Individuals Control Charts. The worked example uses illustrative figures.