Control Chart in Excel

Control charts are an important tool for process quality control. Its efficient way of analyzing performance data to evaluate a process. Control chart can be used on normalized distributed data.

In example below, Company have set average sales target as 247 daily and they want to evaluate sales department’s performance by plotting control chart of one month data.

To plot a control chart below data requires

1.       Data should be normally distributed revolving around a mean.

2.       Mean: average of the data

3.       Standard deviation (SD): to add multiple control limits (UCL1, UCL2 UCL3, LCL1, LCL2, LCL3)

LCL= Lower control limit

UCL=Upper control limit

Keep data in first column name it as sales, second column will be Mean and rest columns are for Control limits based on mean and standard deviation as mentioned below use same structure and formulas.

 

Use below formulas to get all numbers

Cell	Column name	Formula
E2	UCL(SD3)	=Round(B$2+(STDEV($A$2:$A$31)*3),0)
D2	UCL(SD2)	=Round(B$2+(STDEV($A$2:$A$31)*2),0)
C2	UCL(SD1)	=Round(B$2+(STDEV($A$2:$A$31)*1),0)
B2	Mean	=Round(AVERAGE($A$2:$A$31),0)
F4	LCL(SD1)	=Round(B$2-(STDEV($A$2:$A$31)*1),0)
G4	LCL(SD2)	=Round(B$2-(STDEV($A$2:$A$31)*2),0)
H5	LCL(SD3)	=Round(B$2-(STDEV($A$2:$A$31)*3),0)

Once this done drag down formula and plot a line chart on it, in first look you may find it messy but by doing some formatting like change color and style of each line chart will look good and will be ready to use as below

 

Grey line: UCL(SD1) to LCL(SD1) One sigma

Orange line: UCL(SD2) to LCL(SD2) Two sigma

Red line: UCL(SD3) to LCL(SD3) Three sigma

Black line: Mean

Blue line with marker: sales data

Evaluate the graph to see if the sales is out-of-control. The graph is out-of-control if any of the following are true:

•             Any point falls beyond the red line (above or below the 3-sigma line).

•             8 consecutive points fall on one side of the center line (Mean).

•             2 of 3 consecutive points fall within Orange and red line.

Above rules may not be suitable for your process as there are different rules are given on the same by many people, however you can search for more rules to get details out of control chart.


More Excel tips and tricks:

1. Excel - Very useful keyboard shortcuts
2. Excel - cell references
3. Combining Multiple Tables in a PivotTable usingthe Data Model
4. Excel Copy chart format
5. Vlookup-with-column-function
6. Flash Fill – Excel 2013
7. Left lookup in excel index match 

Please use comment section to provide your feedback or to ask questions.