How To Make Control Charts In Excel

What Is a Control Chart (SPC) and Why Use It in Excel?

A control chart – also called a Statistical Process Control (SPC) chart – visualises the variation of a process over time. It shows the average (center line), the upper control limit (UCL) and the lower control limit (LCL). When the data stay inside the limits the process is considered “in control”; points outside signal a special cause that needs investigation.

Step‑by‑Step Guide to Build a Control Chart in Excel

Follow these concise steps to create a ready‑to‑use control chart without any add‑ins.

1. Prepare Your Data

• Enter your measurement data in a single column (e.g., column B).
• Make sure there are no blank rows inside the data range.

2. Calculate the Mean (Average)

Select an empty cell below the data (e.g., B17) and enter:

=AVERAGE(B2:B16)

Press Enter. The result is the process average (center line).

3. Calculate the Standard Deviation

In the next empty cell (e.g., B18) type:

=STDEV.S(B2:B16)

This returns the sample standard deviation.

4. Build the Control Limits

Copy the average cell (e.g., B17) down the column to match the length of your data – this creates a constant line for the center line. Do the same with the standard deviation cell (e.g., B18).

• Upper Control Limit (UCL):

  =B$17 + 3*B$18

• Lower Control Limit (LCL):

  =B$17 - 3*B$18

Place the formulas in two new columns (e.g., D for UCL and E for LCL) and copy them down.

5. Insert the Chart

1. Select the range that includes your data, the mean line, and the two control‑limit columns.
2. Go to Insert → Line → 2‑D Line.
3. Excel will plot the data series and the three straight lines (UCL, LCL, Center).

Give the chart a clear title such as “Process Control Chart – Weekly Output”.

Tips for a Clean and Insightful Chart

• Use absolute references ($) when copying formulas so the mean and sigma stay constant.
• Format the control‑limit lines with a different colour or dash style to make them stand out.
• Hide the legend for the mean line if it clutters the view – the chart itself tells the story.
• Add data labels only for points that fall outside the limits; they highlight out‑of‑control events.

Industry‑Specific Examples

Manufacturing – Quality Inspection

Measure the diameter of a machined part each hour. Plot the values on a control chart to quickly spot tool wear (points above UCL) or calibration drift (points below LCL).

Service – Call‑Center Metrics

Track average handling time (AHT) per shift. When AHT spikes above UCL, investigate staffing or training gaps.

Quick Reference Checklist

Task	Done?
Data entered in one column without blanks
Average (center line) calculated
Standard deviation calculated
UCL = average + 3·σ
LCL = average – 3·σ
All formulas copied down to match data length
Line chart inserted with three series
Chart styled (colours, titles, axis labels)

Print this checklist beside your workstation and tick off each step as you build the chart.

Boost Your Excel Reporting with Automation

If you need to create control charts regularly, consider using a template that automates the calculations and chart refresh. Our automated Excel reporting toolkit includes pre‑built dashboards, dynamic charts, and a step‑by‑step guide to integrate SPC charts into any monthly report.

Next Steps

Ready to turn raw numbers into actionable insights? Download the free Financial Dashboard Excel template and add a control‑chart widget to monitor key performance indicators in real time.

Conclusion

Control charts are a powerful yet simple way to visualise process stability. By following the steps above you can build a reliable SPC chart in Excel in minutes, customise it for any industry, and embed it into regular reporting cycles.