Quick Links

Control chart Installation – Excel 2007 and Later
Control chart Installation – Excel 2003 and Earlier
Usage – How to use the Control chart Utility
Adding Reference to Control chart Library From Other Functions in a VBA Project
Syntax for Calling the Control chart from Other Functions

Installation – Excel 2007 and Later

Here are the steps to set-up the control chart utility in Excel 2007 and later.

Step 1 – Extract the contents of the zipped file to a permanent Folder – Once you have downloaded the excel control chart and the ribbon add-ins, extract both the files to a permanent folder.

Step 2 – Open a Blank Workbook and Attach the Add-In – Click on the ‘File’ tab on the Ribbon

Step 3 – Click on the ‘Options’ under the ‘File’ tab.

Step 4 – Click on Add-Ins

Step 5 – Click on the ‘Go’ button

Step 6 – Click on the ‘Browse’ button – Browse to the folder where the Control chart Add-In was extracted.

Step 7 – Select the file and click ‘OK’ – Enable the option that reads ‘Control chart’.

Step 8 – Click ‘Ok’ twice

Step 9 – A new tab has been added to the ribbon

Installation – Excel 2003 and Earlier

Here are the steps to set-up the control chart maker in Excel 2003 and earlier.

Step 1 – Extract the contents of the zipped file to a permanent Folder – Once you have downloaded the excel control chart and the ribbon add-ins, extract both the files to a permanent folder. If you are only going to use this add-in with the Excel 2003 or earlier versions, you may ignore the ribbon add-in.)

Step 2 – Open a Blank Workbook and Attach the Add-In – Click on the ‘Tools’ -> ‘Add-Ins’ option on the toolbar.

Step 3 -Click on Browse and navigate to the folder where the add-in was extracted.

Step 4 – Select the add-in and click ‘OK’ twice

Step 5 – A new menu option is added to the toolbar

Usage

Use the ribbon using Excel 2007 (and later versions) and the menu toolbar in Excel 2003 (and earlier versions) and click the “Control Chart” button.

The control chart user interface lets you configure the various options for the chart. You can specify the number of control limits to show, highlight outliers, static or dynamic display of plot vlaues, choose line and plot area colors, line and border styles, marker size / style, significant digits, label various elements, font size, chart size and the location on the sheet.

Select the data range beforehand or select a range using the range input box, specify the options as per your preference and click ‘OK’. The finished control chart will be placed in the same worksheet.

The last column of the data range should contain numeric values.

Call Control Chart Function From Other Functions in a VBA Project

You can also call the control chart function from other modules in the project. To access the control chart library, simply include a reference to the CHARTMAKER_CONTROLCHART library from the VBA IDE.

Check the option box next to CHARTMAKER_CONTROLCHART library. You can now freely use the objects in library.

Syntax for Calling the Control Chart from Other Functions

(If you want to use the shortest form, skip over to the section immediately below)

The CONTROL_CHART function has the following arguments:

control_chart_range : The control chart range used to plot the chart. Can be one or two dimensional. If two dimensional data range is used, the values along the first range are taken as x-axis labels. This is the only argument in the list that is mandatory.
control_chart_range_has_headers : Boolean flag indicating whether the control chart range specified above has headers. If TRUE, the chart x-axis and y-axis titles will be picked up from the specified range. Default = False.
chart_title : String, user specified chart title. Default = “Control Chart”.
x_axis_title : String, user specified chart x-axis title. Default = “Observations”.
y_axis_title : String, user specified chart y-axis title. Default = “Range”.
dynamic_nature_of_chart : Boolean flag indicating whether the final chart should remain linked to underlying data source or should be made static/standalone. Default = “True”.
custom_sigma_control_limit : User defined custom control limit. Leave empty if you do not want to show a custom control limit (apart from the default 1, 2 and 3 sigma limits). Default = 0.
highlight_outliers_beyond_sigma_limit : Indicates the cut-off for the control limit for highlighting outliers. Points beyond this control limit will be highlighted. Default = 1.
one_sigma_control_limit_line_color : Numeric value of line color to be used for one sigma control limit. Default = Deep Red.
two_sigma_control_limit_line_color : Numeric value of line color to be used for two sigma control limit. Default = Deep Red.
three_sigma_control_limit_line_color : Numeric value of line color to be used for three sigma control limit. Default = Deep Red.
custom_sigma_control_limit_line_color : Numeric value of line color to be used for custom sigma control limit. Default = Black.
plot_line_color : Numeric value of line color to be used for plot line color. Default = Black.
plot_area_color : Numeric value of color to be used for the chart area background. Default = Light Grey.
plot_line_style : Numeric value for the line style to be used for plotting data points. Default = Solid. Permissible values = 1, 2, 3, 4 or 5. Default = 1.
number_of_significant_digits : Number of significant digits to use to show data values and control limit labels. Note : The calculations for various chart elements are not truncated, only the display is.
plot_line_marker_size : Numeric value indicating the marker size to use specify the marker size. Min = 2, Max 72.
plot_line_marker_style : Numeric value for the line style to be used for plotting data points. Default = Solid. Permissible values = 1, 2, 3, 4 or 5. Default = 1 (circle).
chart_width As Double : Chart width. Default = 400.
chart_height As Double : Chart height Default = 275.
chart_offset_top : Offset from the top. Default = 50.
chart_offset_left : Offset from the left. Default = 100.
label_data_values : Boolean flag used to specify whether the points on the chart plot series should be labelled. Default = False.

Easy Way to Use the CONTROL_CHART Function

If the above looks daunting, don’t worry. Only the first argument (data range) is mandatory and the rest are optional. So you can create a control chart by writing something as simple as :

or

If you want to customize it further, you can add more parameters

You can even omit parameters that you don’t want. So the following is good enough.

Once the call finishes, the function returns a handle to the chart object which you can then be used further in your VBA module.

Click here to buy the Control chart utility for Excel. It has been tested to work with Excel versions – 2000, 2003, 2007 and 2010 (windows versions).