When Cedric and I started talking about the history of manufacturing and XmR charts, he asked me, “Why have XmR charts not spread outside the manufacturing production floor?” This is a thought provoking question, as when you have a tool as practical and straightforward as the XmR chart, you would have expected it to enter other industries and jobs in the economy.

It is also an important question for me as I’ve spent a fair bit of my career working in manufacturing. And it’s important to Xmrit’s overall goals: when we started this project, we aimed to spread the use of XmR charts to as many new places as possible — we even made the code for the Xmrit tool open source to help accelerate adoption.

Some possible reasons for the lack of adoption outside of factories I have heard are:

1. Slightly More Complex: They require slightly more technical understanding than a line chart and, therefore, have several situations in which they can either fail or need to be adapted to ensure they give you usable data (all of which we cover in detail in the Metrics Masterclass course).
2. Misleading Academia: Academics over-indexed on data needing to be normally distributed and required people to perform complex calculations to transform the data before plotting on an XmR chart. From reading Dr Donald Wheeler’s technical analyses on the robustness of XmR charts and using them on my own data, I know this fear to be wildly overblown.
3. Snobbery: There is a certain amount of snobbery toward factory floor ideas, especially ones initially designed to be simple enough for the untrained 1940s factory worker to understand. People sometimes think that if something is too simple, it can’t be useful for the complex work they do.

All these reasons likely contribute to XmR charts not spreading as widely as I would expect, but another reason I want to explore further today is a problem everyone runs into when they first start building XmR charts for themselves: just how excruciating it is to make XmR charts in Excel and other spreadsheet programs.

The Challenge of Building an XmR Chart in a Spreadsheet

When you first try to make an XmR chart, you might look at Cedric’s blog post, which provides step-by-step instructions, or other articles at the top of Google, like Stacey Barr’s walkthrough. You start off with your data and realise you have to add a column for the moving range. Not too bad, you think;

… and then add a column for the average of the moving range

… then you need two more columns for your process limits

… and then another column for your process average

… don’t forget to calculate your two quartile limits

Before you know it, your neat data has spread across nine columns with formulas everywhere. And all this is before you have to pray to the Excel gods to chart seven different lines on the same line chart.

What a mess!

But at least it is now done, and you can start to experience the benefits of the XmR chart world you have been promised. However, as you begin to use your painstakingly created XmR chart, you notice a few problems in your setup, flaws which become more problematic the more you try to use your XmR charts in practice.

Problem 1: Inability to Auto Detect Exceptional Variation

The first issue you run into is your spreadsheet XmR charts are unable to automatically detect when one of the three rules of Exceptional Variation is being triggered. Since your spreadsheet graph doesn’t have any way to identify points that break a rule, you are reduced to zooming in when you need to check if a point is over a process limit or counting to see if a run of 8 was over a centre line.

Practice this yourself. Have a look at this graph, which contains both routine and exceptional variation. How quickly can you spot the three portions of Exceptional variation? Now, imagine you are in the middle of a metrics review and have 20 similar graphs you need to look at.

Did you find the Exceptional variation? How long did it take you? Now, look at the same graph in Xmrit’s free tool. The three portions of the Exceptional Variation jump out immediately.

Problem 2: Inability to Handle Process Shifts

One recurring issue with spreadsheet XmR charts is handling changes to your process limits, especially when your process shifts. With a spreadsheet, you have to duplicate all the calculations you painstakingly set up before. Below is an example of the 16 columns you need to create to be able to plot a segmented graph.

With just a single segment, you can already see that this is not a sustainable way of managing your data.

With Xmrit’s free tool, all this pain is removed. Adding segments is as simple as clicking a single button to insert a divider. You can also add up to four segments for when your process has gone through multiple changes.

Problem 3: Inability to Lock Limits and Removing Outliers from Limit Calculations

The final major problem that spreadsheets have is when you want to customise your process limits. The two most common customisations are removing outlier points from the process limit calculations and locking your limits so they don’t update as you enter new data.

This is possible in a spreadsheet, but it now requires you to go back and manually edit all the nice formulas you created. This introduces the chance of making a mistake, sometimes forgetting to update a single cell making all your process limits incorrect. All this is made 10X worse because your spreadsheet hides the calculations from you, making it hard to find the errors when you make them.

To avoid this problem, the free Xmrit tool allows you to lock limits and visually see all the points you include (or exclude) when doing so. In the example below, you can immediately see that I removed the data point for 2022-01-13 when calculating my locked limits, and I could do it without having to perform any manual calculations.

Why We Built Xmrit’s Free XmR Chart Tool

When Cedric and I encountered these problems initially, we tried using premade spreadsheet templates, such as one from the American Society for Quality. However, even with its advanced macros the ASQ template could not segment data or lock limits and was limited to only 200 data points.

We quickly realised that XmR charts are simply too challenging to create in a spreadsheet and that we needed to develop our standalone XmR chart product. Only by building the product would we be able to make XmR charts more accessible to a broader range of people and industries.

Of course, I would say our tool is better than the humble spreadsheet; I helped create it after all. But you don’t need to trust my words; you can watch Xmrit’s tool in action side by side with a spreadsheet:

I hope you find the improvements in making your XmR charts so helpful that you start plotting things you previously avoided because of how annoying the charts were to create. If you find any features you want to add to the tool let us know in this feedback form.