Create control charts in excel

This article is written by Christopher Hansen. He is a continous improvement specialist.

Control charts are used to study the variation of process parameters over time. Hence, we can know whether the process is under statistical control or not. Under control means all of the variation is the result of common causes and that the process is behaving naturally. Out of control means that an assignable or special cause can be determined. Processes out of control can still be within tolerance and processes in control can still exceed tolerances.

There are different types of control chart and we will be discussing six types of control charts in this article. We will be discussing construction of the data for preparing control charts and also construction of the control chart with Microsoft Excel.

You can download the below excel file which have different types of control charts and those are editable.

An overview of this article

• Introduction to Control Charts
• Types of Control Charts
• Control Charts for Variables data
• Control Charts for Attribute data

Introduction to Control Charts

Control Charts were developed around 1920 by Walter Shewhart. They are used to show the typical variation in a process (that is, “common cause” variation). This is done by plotting Key Process Indicator (KPI) statistics with an average for reference. Control Limits are then added at three standard deviations from that average. The center line and control limits can be calculated from historical data. When points fall outside of these limits, they indicate something unusual has occurred (that is, “special cause” or “assignable cause” variation).

The six Control Charts we will review all follow this same premise. As a result, the discussion of each is a bit redundant when considered as a whole. However, each of the charts has been hyperlinked to review just the chart you are interested in at the moment.

Control Charts are a way to listen to the Voice of the Process (VoP). The KPI, its average, and limits show the variation inherent in the system. W Edwards Deming demonstrated that approximately 94% of all variation is “common cause.” This allows employees to focus on the 6% of variation that is “special cause.” Engineering tolerances, or the Voice of the Customer (VoC), are never included on a Control Chart. That relationship is described with a histogram in Capability Analysis.

Control Charts are one of the 7 Basic Tools of Quality. As such, they are one of the fundamental tools of the trade. In this article, we will discuss the six basic Shewhart Charts or Control Charts that you may need to build in Excel. They are:

1. x–MR (individual and moving range)
2. x-bar–R (mean and range)
3. c-chart (nonconformances)
4. u-chart (nonconformances per unit)
5. p-chart (fraction nonconforming)
6. np-chart (number nonconforming)

Types of control Charts

You may have heard that there are two type of people: those who divide things into two categories and those who don’t. To keep things simple, we will start with looking at the two major divisions in Control Charts: charts for Variables Data and Charts for Attribute Data. We will also avoid: median, standard deviation, Cumsum EWMA, Rare-events, and trends Control Charts and the other sundry types.

Therefore, let us focus on Control Charts categorized into the following:

1. Control Charts for Variables data
2. Control Charts for Attribute data

Control Charts for Variables data

The first type of charts we are going to look at are for Variables data. This type of data is measurable on a continuous scale. While the sample data used is integers, it could be collected to any degree of precision that is appropriate for the tolerances. Determining the appropriateness of the data in relation to the tolerances is the job of a Measurement System Analysis (MSA).

1. x–MR (individual and moving range)
2. x-bar–R (mean and range)

Control Charts for Attribute data

The next group of charts we will discuss are those for attribute data. This raw data that is counted and always remains as integer values. In addition to the main division of attribute data, it can be divided into either defect data or defectives data.

The first type, defect data, is data that may have several issues before becoming a defect. For instance, a screen may have up to 3 bad pixels before the screen is declared defective. Another example would be an essay that may be acceptable with a minimum number of errors. The second type of data is defective data where any imperfection results in a failure. For example, a checking routing number is a defect if one digit is incorrect.

1. Defect
   1. c-chart (nonconformances)
   2. u-chart (nonconformances per unit)
2. Defectives
   1. p-chart (fraction nonconforming)
   2. np-chart (number nonconforming)

Construction of Control charts

In the following sections, we will provide the process required to convert the raw data (along with the data types required for each chart) into usable charts. We will discuss the creation of the control chart constants needed. Then we will examine each of the charts in the order listed above.

Sample File of Control Charts built in Microsoft Excel

To follow along with these examples, you can use this sample file:

Each section of this article corresponds to a tab within the spreadsheet.

Control Chart constants

First, we need to create a table of Control Chart constants. The charts we are creating define the constants that are required. That is, different control charts require different constants.

If all of the chart types are in a single file, then make a table with the Control Chart constants for all of the charts and pull the data repeatedly from this single table. While the sample file uses static calls, it is easily adapted to use =VLOOKUP() to make the charts increasingly dynamic but there is rarely a need for that complexity. There are hundreds of sources readily available in print or online to find the chart constants. All required values for these charts are included in this article.

The table of constants should have the following values: A₂, D₃, D₄, E₂, d₂. By adding d₃ to the table, all other constants can be calculated from d₂ and d₃. Therefore to reduce transcription error, the final table should have this structure:

The completed table in Excel will look like this: (note the values for the constants we will not calculate, d₂ and d₃).

Plotting Technique

The purpose of this section is to provide you with a detailed description of constructing the control charts in Excel. This technique will work with all of the control chart types covered in this article.

The columns are arranged in our file to make plotting tables easier. Consistency in setting up columns leads to consistency in creating plots. This, in turn, makes it easier to edit the plots for consistent results later. The arrangement I use here is: sample statistic, Lower Control Limit (LCL), Center Line (CL), and Upper Control Limit.

After selecting the data in these four columns with their label, Excel makes it easy to create a chart by selecting Insert -> Line or Area Chart. We want to be sure to select the version without markers. This is because it is easier to add markers to one line on the plot than to remove them from three lines on the plot.

Once the chart is created, you can right-click on a line then select “Format Data Series…” to change the properties of each line.

This option opens a panel on the right side of the screen. The panel has a large number of options to choose from. The features of most concern are the Color (the LCL and UCL should match and it helps to make the CL and sample statistic different colors), Dash type (only the CL should be adjusted to something appropriate). Keeping this menu open, you can left-click each line in the control chart to modify its properties.

As a personal preference, I tend to like a red for the control limits (as they indicate a need for action when they are crossed) and a yellow or green for the center line (as it represents the expected output from the process). Finally, I tend to use a cool color (such as blue) for the sample statistic to offer contrast. Triad color schemes such as red, yellow, and blue tend to offer the most contrast in the colors while remaining readable and balanced.

Selecting the “Marker” option of the “Format Data Series…” menu lets you adjust the final feature of the control chart plots.

The easiest option is to select the “Automatic” option under “Marker options” to add markers to the sample statistic line in the control chart.

These edits then result in the consistent control charts presented here and provided in the sample file.

Control Charts for Variables data

x–MR (individual and moving range) Control Chart creation

In this section, we will construct the most basic of variable data Control Charts: the individual and moving range charts. These charts are best used when there is significant time between each observation.

As the name implies, we will need to create two charts: one for the individual values and one for the moving range. For the process to be in statistical control, both charts will need to be in control.