How to Make a Control Chart in Google Sheets

A control chart is one of the most effective ways to differentiate between normal, everyday fluctuations in your data and significant changes that demand your attention. Instead of reacting to every small dip or spike, a control chart provides a clear statistical framework to understand your process performance over time. This guide will walk you through creating a control chart from scratch in Google Sheets.

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

We’ll cover how to structure your data, calculate the necessary statistical lines, build the chart, and most importantly, interpret what it’s telling you about your business process, whether it’s website traffic, daily sales, or ad campaign performance.

What Exactly is a Control Chart?

At its core, a control chart is a line graph with a few extra lines that provide crucial context. It’s a tool used in statistical process control (SPC) to monitor how a process behaves over time. The goal is to see if your process is stable and predictable or if something has changed.

Every control chart has three main components:

• The Center Line (CL): This is simply the average (or mean) of your data points. It represents the historical average performance of your process.
• The Upper Control Limit (UCL): This line is positioned three standard deviations above the center line. It’s the upper boundary of what you’d expect from normal process variation.
• The Lower Control Limit (LCL): This line sits three standard deviations below the center line, representing the lower boundary of expected variation.

When your data points bounce around randomly between the UCL and LCL, your process is considered "in control." This random variation is called common cause variation - it's the natural, inherent noise in any system. However, when a data point falls outside these limits, or when a non-random pattern emerges, it signals special cause variation. This is a sign that an external, unforeseen factor has impacted your process, and it’s time to investigate.

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

Step 1: Set Up Your Data in Google Sheets

Before you can calculate anything, you need to get your data organized properly. A control chart works best with time-series data, meaning data points collected at regular intervals (e.g., hourly, daily, weekly, monthly).

For this tutorial, let’s imagine we’re a small e-commerce store tracking the number of orders we receive each day. Our setup in Google Sheets will be simple:

• Column A: The time period. This could be the date (e.g., 11/1/2023) or a simple label (e.g., Day 1, Day 2).
• Column B: The metric you are measuring. For our example, this is "Daily Orders."

Here’s what our sample data looks like for the last 30 days:

Sample Data Setup:

Step 2: Calculate the Center Line and Control Limits

With our data ready, we can now calculate the three key lines for our chart. We'll create new columns for the Center Line (CL), Upper Control Limit (UCL), and Lower Control Limit (LCL).

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

Calculate the Center Line (CL)

The Center Line is the average of all your data points. It gives you a baseline for your process performance.

1. In an empty cell (e.g., F1), calculate the overall average of your data using the AVERAGE function. Label it "Average."

=AVERAGE(B2:B31)

1. In cell C1, type the header Center Line (CL).
2. In cell C2, reference your calculated average. Use an absolute reference so it doesn’t change when you copy the formula down:

=$F$1

1. Drag the fill handle from C2 down to the last row of your data (C31). This will fill every cell in the column with the same average value, creating a horizontal line on your chart.

Calculate the Standard Deviation

To find our control limits, we first need to calculate the standard deviation:

1. In cell F2, compute the standard deviation of your data:

=STDEV.S(B2:B31)

1. Label it "Std Dev" in F2.

Calculate the Upper Control Limit (UCL)

The UCL is typically set at three standard deviations above the average:

1. In cell D1, type UCL.
2. In cell D2, calculate UCL:

=$F$1 + (3*$F$2)

1. Drag this formula down to fill all the rows in the UCL column.

Calculate the Lower Control Limit (LCL)

The LCL is three standard deviations below the average, but it should not be negative if the data cannot logically be negative:

1. In cell E1, type LCL.
2. In cell E2, compute:

=MAX(0, $F$1 - (3*$F$2))

1. Drag down the formula for all data rows.

Your sheet now has five columns of data ready for visualization: your original data, the Center Line, UCL, and LCL.

Step 3: Build the Control Chart Visualization

1. Highlight all five columns of your data, including headers (A1 to E31).
2. Go to the menu and click Insert > Chart. Google Sheets should suggest a Line Chart.
3. If not, change the chart type in the Chart Editor to "Line Chart."
4. You will now see four lines: your data ("Daily Orders"), the CL, the UCL, and the LCL. To improve clarity, customize the chart:

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

Customizing Your Chart for Readability

• Under "Chart style", adjust background color if desired.
• Under "Chart & axis titles", add a descriptive title like "Control Chart for Daily Orders" and label the vertical axis "Number of Orders."
• Under "Series", customize each line:

After adjustment, your chart will be clearer and more professional-looking.

Step 4: How to Read and Interpret the Control Chart

Creating the chart is only half the battle. The real value comes from understanding what it shows.

What is "In Control"?

If all points are within the UCL and LCL and are randomly distributed around the CL, your process is "in control." Fluctuations are due to common cause variation — normal system noise. In this case, the best action is to do nothing.

Signals of an "Out of Control" Process

Look for patterns indicating special causes:

• Point outside limits: A single point above UCL or below LCL signals an issue.
• Runs: Seven or more consecutive points on the same side of the center line suggest a shift in the process.
• Trends: A series of points trending up or down (e.g., six or more in a row) indicate a process drift.

When you see such signals, investigate: "What happened during that period?"

Final Thoughts

Building a control chart in Google Sheets turns raw numbers into a powerful process management tool. By calculating mean, standard deviation, and control limits, you can visualize whether variations are natural or indicate a process change.

While manual sheet reports are useful, they can be repetitive. At Graphed, we simplify this with integrations. Connecting tools like Google Analytics or Shopify, you can ask in plain English: "Create a control chart for daily users last 60 days," and get live dashboards without formulas — saving hours and enabling instant insights.