Create an Excel Control Chart to Analyze Data

In this article, we’ll show you how to construct an Excel control chart to analyze data and improve efficiency. Control charts are useful for monitoring any process that has a level of variation – for example, filling containers with a certain number of items. They can easily illustrate whether your process is operating within its intended parameters.

1. Gather and Format Your Data
Once you have the data gathered from your process, the first step in creating an Excel control chart is to ensure that it is correctly formatted. Your data set should be organized along a mean (average) – i.e. a target point that the data points are clustered around. If it’s not, your control chart will not be useful.

2. Organize Data Columns
Secondly, create columns to organize your data. Label them Range, Number, Data, Lower, Upper and Average. Fill the Data column with your organized data points, and fill the Number column with values ascending in numerical order, starting with 1.

3. Get Your Upper and Lower Limits
Here’s where your formula comes in. In the first cell of the Range column, enter the formula =Abs(C2-C3). Select the cell and drag it down to copy the formula to the end of your data. In the Lower column, do the same with the formula =Average(C:C)-2.66*Average(A:A), and in the Upper column use=Average(C:C)+2.66*Average(A:A).

4. Get Your Data Average
Finally, set up the formula for your Average column. In F2, type =Average(C:C). After you hit enter, autofill the formula down to the end of your data. This creates the target average.

5. Create Your Excel Control Chart
Now that you have the framework for your Excel control chart set up and your data imported, select the data in columns B through F and navigate to the Insert tab and locate the Chart group on the menu. Choose a Scatter chart type.

For the final step, you need to establish the field areas for your deviation. The chart may look complete, but it isn’t in the proper format just yet. Right-click a data point on your Lower limit line and select Change Series Chart Type. When the menu opens, choose a line chart. Repeat this action for your “Upper” and “Average” data sets. Click OK to confirm and your Excel control chart will be complete.

In this Fred Pryor Seminars_Excel Control Chart_Practice sample, play around with the numbers to see how the average and other limit lines change when data shifts or try this process out yourself. Do you have a process or sample group data suited for this type of chart? What can YOU use a control chart for?