Categorized | excel chart

Control Chart in Excel – Create Six Sigma Quality Control Chart Using Excel




Control Charts are an important tool for process quality control. A control chart is generated by when upper and lower control limits are inserted in to a line chart representing changes in a variable over a given period of time. Control charts are an important member of the six sigma methodology and help in visually ascertaining the quantum (and trend) of variation observed in a process. The upper and lower quality control limits in a control chart need not be process (or product) specification limits. In many cases they are set narrower in order to ensure that errors in the process are caught well before they reach a magnitude that threatens the process acceptance limits or quality control limits.

One can create a control chart in Excel with relative ease. If needed, multiple control limits can be included in the control chart so as to get early indicators of minor process control limit breaches. The minor controls limits in such a case are set narrower than the major control limits and therefore will be violated earlier than the major ones.

To create a Six Sigma Control Chart in Excel with upper and lower control limits:

1. Ensure that the data is nicely laid out in a single column. For the purpose of this tutorial, we assume that we have data worth 30 days and is present in the first column from cell A1 to A30 in a worksheet named ‘Control Chart’.

2. While creating the various series in control chart, we are going to make frequent use of the named range functionality. (Read more about creating chart with named range here.)

Now, click on ‘Insert” -> ‘Name’ -> ‘Define’ in the menu. It will open up the ‘Insert Name’ dialog box as shown in the picture below.

control-chart-with-named-range

One by one create the following named ranges:

named-range-formula-for-the-control-chart

As you can see, we have created named range for the data series, average or the mean of the series and the lower and upper control limits.

3. Insert a line chart using the ‘Insert’ -> ‘Chart’ option. In the ‘Chart Source Data’, enter the ‘data range’ as =’Control Chart’!data. (Normally we would’ve entered the source data range as =’Control Chart’!$A$1:$A$30. However since we have already created a named range that refers to that data range, we will use it instead.)

main

The first series in the control chart comes out looking something like this:

control-chart-in-excel

4. Format the chart and remove ‘chartjunk’. Right click on the chart and select ‘Source Data’. Click on the ‘Add’ button and insert new series for the average and lower and upper statistical control limits.

source-data-for-control-chart

Once we are done, the control chart looks something like this:

control-chart-with-bands

5. The series we inserted in point 4 all appear as points on the chart. Now one by one right-click on each of the points, select ‘Chart Type’ and in the box that appears, change the chart type to XY Scatter. (Shortcut – You can do this step once for the first point and for the subsequent points, simply select the point and press F4.)

6. Once step 5 is complete, double-click on each of the points and select the X Error Bars tab. In the custom error box enter a relatively large amount in both the + and – errors (say 10000). Repeat this step for all the series apart from one representing the main data.

x-axis-error-bars

7. Once we are done with point 1 to 6, the control chart comes out looking like the one shown below. From this point onwards, its smooth sailing.

control-chart-with-upper-and-lower-bound

8. Right-click on the chart and select ‘Chart Options’. In the ‘Data Labels’ tab, enable both the ‘Series Name’ and the ‘Value’. Ok….bear with me….this IS an ugly looking control chart but redemption is at hand :-)

adding-labels-to-the-control-chart

9. In the Control chart, carefully click on any one of the lables of the main data series (the one that represents the data being plotted). Press delete. This leaves us with only the value and series name for all the remaining series. Now select any label. (Two successive slow single clicks on the same label). Move that label out to the extreme right. Move all the other labels as well by selecting each one and pressing F4.

Right click on the chart and select ‘Source Data ‘. One by one modify the ‘Name’ of each of the data series and give it a more descriptive name. We use LCL1, LCL2, LCL3, UCL1, UCL3, UCL3 and AVG. The name has to be entered in the format shown below.

adding-names-to-series

Once we are done with this step, our six sigma control chart appears as this.

control-chart-with-upper-and-lower-sigma-limit

10. One by one, right-click on each point representing the control limits in the chart and the average and select ‘Format Data Series’. In the ‘Patterns’ options box, set ‘Marker’ to none. Now click on each of the lines and provide proper shading to bands. In our example, we use lighter shades for narrower control limits and darker shades for the outer and more critical quality control limits. If you add markers for 3 limits (+/- 1, 2 and 3), the chart becomes a proper six sigma control chart.

quality-contol-chart

And there we are with our neat looking six sigma control chart.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jon Peltier wrote:

    Your formula for average deducts one standard deviation. Probably an oversight when you got into the copy and paste rhythm.

  2. Ajay wrote:

    Thanks Jon. Rectified.

  3. eman wrote:

    thanks for u guidance

  1. Control Chart in Excel Using VBA (Code, Software) | Excel & VBA - da Tab Is On wrote:

    [...] Control charts using Excel we saw how to make control charts using named ranges. From there on, it was a short hop to porting [...]

  2. An Excel Chart’s Journey Across Versions | Excel & VBA - da Tab Is On wrote:

    [...] back I wrote a post on making control charts using Excel. The chart was first created in Excel 2003. The chart consisted of the plot values, the average [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel