How to Make an XMR Chart in Google Sheets

Tracking your business metrics over time can feel like watching a noisy stock ticker - it's hard to tell if a random dip in sales is a real problem or just a normal fluctuation. An XmR chart is a simple but powerful tool that helps you separate the meaningful signals from the random noise. This article will walk you through, step-by-step, how to create and interpret your own XmR chart right 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

What Exactly is an XmR Chart?

An XmR chart (also known as an Individuals and Moving Range chart) is a type of statistical process control (SPC) chart. That sounds a bit technical, but the concept is straightforward. It’s designed to help you track the performance and stability of a process over time when you have individual data points, not large batches or samples.

The XmR chart is actually two charts stacked together:

• The “X” Chart (Individuals Chart): This chart plots your raw data points over time (e.g., daily revenue, weekly website sessions, monthly sign-ups). It helps you see the actual performance of your process.
• The “mR” Chart (Moving Range Chart): This chart plots the variation between each consecutive data point. It helps you understand the process's predictability. A stable mR chart means your process variation is consistent, even if the "X" chart shows high or low performance.

Why is this useful? By calculating upper and lower control limits, an XmR chart tells you what range of variation is normal for your process. Any point that falls outside these limits is a "special-cause variation" - a signal that something out of the ordinary has occurred and is worth investigating. Without this, you might overreact to normal ups and downs or, worse, miss an important trend that needs your attention.

Common business use cases include:

• Tracking weekly sales for a small business.
• Monitoring daily customer support ticket resolution times.
• Analyzing month-over-month website conversion rates.
• Keeping an eye on new user sign-ups per day.

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: Get Your Data Ready in Google Sheets

The first step is to set up your raw data. XmR charts work best with time-series data, where individual observations are recorded chronologically. All you need is a simple two-column layout:

1. Column A: The date or time period for each observation (e.g., Day 1, Week 1, Jan-24).
2. Column B: The metric you are measuring (your "X" value).

For this tutorial, let’s imagine we’re tracking daily website traffic. Your initial data in Google Sheets would look something like this:

It's important to have enough data for the chart to be meaningful. A good rule of thumb is to start with at least 20-25 data points.

Step 2: Calculate the Necessary Components

With our raw data in place, we now need to add a few columns to calculate the averages and control limits. This is where the magic happens.

Calculate the Moving Range (mR)

The "Moving Range" is the absolute difference between two consecutive data points. It measures the short-term variation in your process. In column C, label the header "Moving Range (mR)".

The first cell (C2) will be blank because there is no previous data point to compare it to. In cell C3, enter the following formula:

=ABS(B3-B2)

This formula subtracts the previous day's traffic from the current day's and the ABS function ensures the result is always a positive number. Now, click on cell C3, grab the small blue square (the fill handle) in the bottom-right corner, and drag it down to the end of your data. This will apply the formula to all your rows.

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 Averages

Next, we need to find the overall average for both our individual values (X) and our moving ranges (mR). These averages will act as the centerlines for our charts.

Somewhere off to the side (e.g., in cells F2 and F3), create labels for "Average of X" and "Average of mR".

In the cell next to "Average of X" (G2), calculate the average of your raw data:

=AVERAGE($B$2:$B$21)

In the cell next to "Average of mR" (G3), calculate the average of your moving range column, making sure to exclude the first blank cell:

=AVERAGE($C$3:$C$21)

Tip: Using dollar signs ($) makes these absolute references, which is helpful when we copy formulas later.

Calculate Control Limits for the X Chart

The control limits on the X chart define the boundaries of normal, expected variation. We'll need calculations for the centerline (which is just the Average of X), the Upper Control Limit (UCL), and the Lower Control Limit (LCL).

Add three new columns to your main table: "X-Centerline", "X-UCL", and "X-LCL".

1. X-Centerline (Column D): In every cell in this column, just reference your average calculation. In D2, enter =$G$2 and drag it down.
2. Upper Control Limit (X-UCL) (Column E): The formula for the upper limit is: Average of X + (2.66 * Average of mR) The number 2.66 is a statistical constant used specifically for XmR charts. In cell E2, enter:

=$G$2 + (2.66 * $G$3)

Drag this formula down for all your rows.

1. Lower Control Limit (X-LCL) (Column F): The formula for the lower limit is: Average of X - (2.66 * Average of mR) In cell F2, enter:

=$G$2 - (2.66 * $G$3)

Drag this formula down. If your LCL comes out to be negative, and your data can't be negative (like website visits), it's common practice to set the lower limit to 0.

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

Calculate Control Limits for the mR Chart

Now we’ll do the same thing for the mR chart. Add two more columns: "mR-UCL" and "mR-Centerline." The LCL for an mR chart is always 0, so we don't need a column for it.

1. mR-Centerline (Column G): The centerline is equal to the Average mR. In G2, enter =$G$3 and drag down.
2. Upper Control Limit (mR-UCL) (Column H): The formula here is different: 3.268 * Average of mR The number 3.268 is another statistical constant for this chart. In cell H2, enter:

=3.268 * $G$3

Drag the formula down to fill the column.

Step 3: Build the Visual XmR Charts

With all our calculations done, it's time for the fun part: visualizing the data.

Creating the X Chart (Individuals Chart)

1. Select your data: Highlight the columns for Date, Website Visits, X-Centerline, X-UCL, and X-LCL. Make sure you select the headers as well.
2. Insert the Chart: Go to the menu and click Insert > Chart. Google Sheets will likely suggest a combination chart. Under the ‘Setup’ tab in the chart editor, select Line chart.
3. Customize your chart:

Creating the mR Chart

The process is nearly identical for the mR chart.

1. Select your data: Highlight the columns for Date, Moving Range, mR-Centerline, and mR-UCL.
2. Insert the Chart: Go to Insert > Chart and again choose a Line chart from the editor.

Your finished XmR chart setup might look something like this, with the two charts stacked for easy analysis.

Final Thoughts

Creating an XmR chart in Google Sheets may seem like a lot of steps upfront, but it's an incredibly powerful tool for monitoring your business’s health in a more objective way. By understanding what is normal variation versus a signal that something has changed, you can make smarter decisions and avoid chasing ghosts in your data.

After building a couple of these manually in Google Sheets, you're likely to see how tedious and repetitive the process can be. We developed tools to automate these tasks. Instead of manually calculating control limits and building charts, just ask us questions in plain English to create relevant dashboards automatically. It's like having a data analyst on your team who gives you the answers you need in seconds, not hours.