Control Chart – How to create one in Excel 2010

Control Charts are an important tool for analyzing your process. In this post I show you how you can create a lead time Control Chart in Microsoft Excel 2010.

Important note
In this example the control limits are based on the standard deviation function in Excel. This may or may not be the correct way to calculate your control limits of your data

Let’s get started.

1. Create a new workbook

2. Rename “Sheet1” to “Data”

3. Add the columns “Id” , ”Title”, “StartDate”, “EndDate”, “LeadTime”, “Type”, “Comments”

4. Select all the columns and the row below

5. Open the “Insert tab” in the Excel ribbon

6. Create a table by clicking on the “Table” button on the Excel ribbon

7. Check “My table has headers” and click OK

8. Rename table to “LeadTimeTable”

9. Format the header row to make more useable

10. Format the columns “StartDate” and “EndDate” as date columns

12. Add the formula “=[EndDate]-[StartDate]” in the “LeadTime” column

12. Format the “LeadTime” column as number column

13. Add some example data

14. Switch to the “Sheet2” worksheet and rename it to “Criterias”

Now we will create criteria’s that will be used to select the appropriate rows from the “LeadTimeTable” on the “Data” sheet when we do the calculations for the control chart.

15. Add a label for your criteria and add it to the “Criterias” sheet

16. Copy the header row form the “Data” sheet as shown

17. Add the criteria to get all features by adding the formula =”=Feature” in the feature column

18. Select the all the headers and the criteria row below

19. Open the “Insert tab” in the Excel ribbon

20. Create a table by clicking on the “Table” button on the Excel ribbon

21. Check “My table has headers” and click OK

22. Rename table to “FeatureCriteria1”

23. Switch to the “Sheet3” worksheet and rename it to “Features”

24. Add the columns “EndDate”, “Lead time”, “Average Lead Time”, “OneSigma”, “Upper Control Limit”, “Lower Control Limit”

25. In the “EndDate” column add the formula =LeadTimeTable[@EndDate]

26. In the “Lead time” column add the formula =IF(LeadTimeTable[@Type] = “Feature”;LeadTimeTable[@LeadTime];NA())

This formula will get the “Lead time” if the type is a feature and sets a #N/A if not. This will prevent non feature values to be plotted on the control chart.

27. In the “Average lead time” column add a DAVERAGE formula as shown

This formula will calculate the average lead time for all rows matching the “FeatureCritera1”

28. In the “OneSigma” column add a DSTDEVP formula as shown

This formula will calculate the standard deviation for the lead time of the population matching the “FeatureCritera1”

29. In the “Upper Control Limit” column add the formula =C2+D2

This formula adds one sigma or one confidence interval to the average lead time. This will be the upper control limit that is approximately 70% of the population.

30. In the “Lower Control Limit” column add the formula =C2-D2

This formula subtracts one sigma or one confidence interval from the average lead time.

31. Format the columns to make it more usable

32. Select all the columns and the row with the formulas

33 Open the “Insert tab” in the Excel ribbon

34. Create a table by clicking on the “Table” button on the Excel ribbon

35. Check “My table has headers” and click OK

36. Rename table to “FeatureTable”

37. Now expand the “FeatureTable” to the same row count as in the “LeadTimeTable”

We are now ready to create the Feature Lead Time Control Chart.

38. Select all but the “OneSigma” column

39. Insert a “Scatter with only Marker” chart and move it to a new sheet named “FeaturesControlChart”

40. Select the “Average Lead Time” series by selecting one of its markers

41. Right click and select “Change Series Chart Type”

42. Select a “Line” chart

43. Now repeat step 31 to 33 for “Upper Control Limit” and “Lower Control Limit”

The chart should now look something like this

44. Now format the chart to make it clear what is inside the control limits. It could look something like this

Done!

Now you have a lead time control chart! Now you can replace the example data and add your own data. Just add a new row for every delivered feature. Remember to also update number of rows of the “FeatureTable” (see step 28) or the cart will not be updated correctly.

You can also add other work item types and other criteria’s for the calculations as you use it.

Download example file here: Control Chart Example.xlsx