How to Create Control Charts in Excel

1. Create a two-columned table; enter the dates or other time measuring criteria in the right column, and the data to be graphed in the left column. For example, to begin creating a control chart to measure the number of products produced on an assembly line per day, list the respective dates on the left and the corresponding production numbers on the right.
2. Calculate the average of your entire data series by entering '=AVERAGE(first value,last value)' into any cell, and establish your desired upper and lower control limits. In our production example, you may find that the average number of products made per day is 100, and you may wish to produce no more than 150 per day and no less than 50. In this case 150 would be your upper control limit and 50 would be your lower control limit.
3. Create a second two-columned chart to list your average data value and control limits. Since these values will be the same for all dates, list only the first and last date in your data series in the left column, once for each value, and the corresponding values in the right column. In our example, assuming your first date is 1/1/09 and your end date is 2/2/10, you would enter '1/1/09' in any cell, '2/2/10' in the cell directly beneath it, and '100' directly to the right of both dates. You would then repeat this procedure twice, directly under the first entries; once for the upper control limit, and once for the lower.
4. Create an XY chart by highlighting the data in your first table and clicking 'Insert -> Charts -> XY Chart'. You will now have a control chart showing the data values at each time interval. In the example, you would have a horizontal XY chart showing the number of products produced each day.
5. Add your data average and control limits by highlighting each of the four-cell blocks in your second table, one at a time, and dragging them over to the chart. Click 'OK' in the Paste Special dialog box that pops up each time, and you will have a complete statistical process control chart. Following the example, you would highlight the start date, end date, and both instances of '100' for the average, then drag the group over the chart. You would then repeat the process for the upper and lower control limits.