How to Make a Control Chart

A control chart helps you see if the changes and fluctuations in your business metrics are just random noise or signs of a serious problem. It’s a simple but powerful graph that monitors a process over time, showing you what’s normal and flagging when something is out of the ordinary. This article will walk you through exactly what a control chart is, how to build one step-by-step in Excel, and how to interpret the results to make better decisions.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Control Chart, Really?

Think of a control chart as a health monitor for any business process. Whether you're tracking daily website traffic, customer support ticket resolution time, or weekly sales figures, a control chart gives you a clear visual for its performance over time. It’s essentially a line graph with a few extra, very important lines added to it:

• The Center Line (CL): This is simply the average (or mean) of your historical data. It represents the "normal" or expected performance of a stable process.
• The Upper Control Limit (UCL): This line is typically plotted at three standard deviations above the center line. It marks the upper boundary of expected variation.
• The Lower Control Limit (LCL): This line is plotted at three standard deviations below the center line. It defines the lower boundary of expected variation.

Data points that fall between the UCL and LCL signal that your process is "in control" - meaning the variation you're seeing is predictable and normal. A point that falls outside these limits is a signal that something different has happened, something worthy of your attention.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Common Cause vs. Special Cause Variation

The entire point of a control chart is to help you distinguish between two types of variation, because you need to react to one and ignore the other.

Common Cause Variation is the natural, predictable "noise" inherent in any process. Imagine you’re tracking the time it takes you to commute to work. Some days it might take 28 minutes, others 32, due to normal traffic light patterns or slight differences in traffic flow. This is common cause variation. It’s the background static you can’t, and shouldn’t, react to every day.

Special Cause Variation is different. It’s the result of something specific, unusual, and unpredictable happening. Using the same commute example, a major accident that shuts down a lane and makes your commute 75 minutes long is a special cause. Similarly, a new highway opening that cuts your commute down to 20 minutes is also a special cause. These are the events you need to investigate. Was it a one-time event, or has the process fundamentally changed?

A control chart makes it visually obvious which type of variation you're dealing with. It keeps you from overreacting to common cause noise while ensuring you act quickly when a special cause creates a genuine shift in performance.

Step-by-Step: How to Make a Control Chart in Excel

Building a control chart is easier than it sounds. For this example, let's pretend we're a SaaS company tracking the number of new free trial signups per day over the last 25 days. The goal is to see if our sign-up process is stable.

Step 1: Collect Your Data

First, you need time-ordered data. This is critical - the order of the data points matters. Open Excel or Google Sheets and create two columns: 'Day' and 'Signups'.

Here’s our sample data:

Day 1: 52 Day 2: 49 Day 3: 55 Day 4: 48 Day 5: 51 Day 6: 53 Day 7: 65 Day 8: 50 Day 9: 47 Day 10: 54 Day 11: 49 Day 12: 52 Day 13: 50 Day 14: 56 Day 15: 48 Day 16: 51 Day 17: 53 Day 18: 38 Day 19: 50 Day 20: 49 Day 21: 55 Day 22: 47 Day 23: 52 Day 24: 51 Day 25: 50

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Calculate the Center Line (The Average)

The center line is the average of all your data points. In a new cell, use the AVERAGE function to calculate this. If your signup numbers are in cells B2 through B26, the formula is:

=AVERAGE(B2:B26)

For our data, the average is 50.6. In a new column next to your data (let's call it 'Center Line'), paste this value for every day. Having a column of these values will help us plot it as a straight line on the graph.

Step 3: Calculate the Standard Deviation

Standard deviation measures how spread out your data is from the average. We need this to calculate our control limits. In another cell, use the STDEV.S function (the 'S' stands for sample, which is appropriate for most business scenarios).

=STDEV.S(B2:B26)

For our data, the standard deviation is 5.46.

Step 4: Determine the Control Limits (UCL and LCL)

Now we have everything we need to set our boundaries. The standard practice is to set them three standard deviations away from the average.

To calculate the Upper Control Limit (UCL): UCL = Average + (3 * Standard Deviation) So for us, that’s 50.6 + (3 * 5.46) = 66.98

To calculate the Lower Control Limit (LCL): LCL = Average - (3 * Standard Deviation) And for us, that’s 50.6 - (3 * 5.46) = 34.22

Just like with the center line, create two new columns in your spreadsheet: 'UCL' and 'LCL'. Paste your calculated UCL and LCL values into every row next to your data.

Your spreadsheet should now look something like this:

Step 5: Plot Everything on a Line Chart

1. Highlight all of your data, including the four columns: Signups, Center Line, UCL, and LCL. Don't include the 'Day' column numbers in the selection.
2. Go to the 'Insert' tab in Excel and select a 'Line' chart, specifically the 'Line with Markers' option.
3. Excel will generate your chart. At this point, it looks messy, but it contains all the right information.
4. Right-click on the chart and choose 'Select Data'. You can now properly define your horizontal (X) axis. Click 'Edit' under 'Horizontal (Category) Axis Labels' and select your 'Day' range (A2:A26).

After a bit of formatting (renaming your series, adjusting colors to make the control limits stand out), your control chart should look something like this:

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Read Your Control Chart

Now for the fun part: interpretation. At a glance, the chart tells a story.

Nearly all of our data points fall between the upper and lower control limits. This suggests our sign-up process is generally stable and predictable. The bumps and dips from day to day are mostly common cause variation - just the normal noise of business.

However, two points stand out:

• Day 7: Signups hit 65. This is very close to our upper control limit of 66.98. While not technically "out of control," it's worth a look. Did we run a successful social media post that day? Was our website featured somewhere? This is an anomaly you want to replicate.
• Day 18: Signups dropped to 38. This is also inside our limits, but it’s the lowest point on the chart. Is there a reason? Maybe there was a bug on the signup page that day, or a major holiday that explains the dip.

Rules for Spotting an "Out of Control" Process

While points outside the limits are the most obvious signal, statisticians have identified other patterns that also indicate special cause variation. Here are a few key ones:

• One Point Out: The most straightforward rule. One or more data points fall outside the UCL or LCL. It’s a definite signal that something unusual occurred.
• The Rule of Seven: Seven consecutive data points all landing on one side of the center line (all above or all below). The probability of this happening by chance is low, suggesting a shift in the process average.
• A Clear Trend: Seven or more consecutive points are steadily increasing or decreasing. This might indicate that a process is improving (a good thing!) or degrading over time (a bad thing!).

Observing any of these patterns means it's time to investigate. The goal isn’t to panic, but to get curious. Ask "what changed on that day or during that period?" If it’s something positive, figure out how to do more of it. If it’s negative, identify the root cause and implement a fix.

Final Thoughts

Control charts are a fantastic tool for distinguishing signal from noise in any business process, helping you manage by data instead of gut feelings. By calculating your average and setting control limits, you gain an objective way to monitor performance over time and know exactly when to take action.

Of course, manually building these charts in Excel from ever-changing data can be time-consuming. We built Graphed to automate this entirely. By connecting directly to your live data sources like Google Analytics, Shopify, or Salesforce, we let you create real-time control charts and performance dashboards just by asking in natural language. Instead of exporting CSVs and updating formulas, you can simply ask, "show me daily user signups for the last 90 days as a control chart," and get an interactive, always-up-to-date visualization in seconds.