PTS Cluster Stack Utility Documentation.

Installation

Start installation by closing Excel, then double click on the PTS_ClusterStack_Setup.exe file that you've downloaded. The Cluster Stack Chart Utility is installed as an Excel add-in. In addition, ClusterStackData.xls, a workbook containing sample data to illustrate the operation of the utility, is saved in PTS Charts, a new directory in your My Documents directory.

The Excel User Interface

Excel 2000 through 2003 Interface
When installed, both versions of the add-in add a new menu, PTS Charts, to Excel's Worksheet and Chart Menu Bars, and this PTS Charts menu has a new item, Clustered Stacked Chart, added to it. The add-in also adds a new toolbar, PTS Charts, and a Clustered Stacked Chart icon is added to the new toolbar. This menu and toolbar are shown below:

Excel 2007 Interface
When installed, the professional utility adds a new custom ribbon tab, PTS Charts, to Excel's ribbon. The new tab has a group called Custom Charts, with a new item called Cluster Stack Chart. If you use other PTS charting utilities, they also appear on this custom tab. This new ribbon tab is shown below:

The Cluster Stack Chart Dialog

To use the utility, select a range of data to chart, then select the Clustered Stacked Chart menu item, and the following dialog appears.

Cluster Stack Chart Dialog
 

If a range containing chartable data has been selected, this range is indicated in the range selection box at the top of the dialog. If the selected range consists of multiple discontiguous areas, if the selected range contains no data, or if the selection is not a range, this box is empty. If the selected range consists of a single cell, the utility identifies the range of data surrounding the active cell in the range selection box, like the Chart Wizard does. If the selection is larger than one row high and one column wide, the utility identifies only the selected range in the box.

The utility determines the number of rows (category labels) and columns (number of series) in the data range. When first opened the utility assumes there will be two columns (bars) side by side for each category in the chart, and splits the series as evenly as possible between these two columns (bars). These parameters can all be adjusted, because not all charts have the same number of columns (bars) for each category, or even the same number of series stacked in each column (bar). The utility validates the inputs after every change.

The utility inserts a new worksheet for its output. The selected data, linked to the original cells, is copied to this worksheet in an appropriately staggered arrangement, and a chart is created that plots the data. The chart is a standard Excel chart; it can be copied and pasted, moved, resized, and otherwise formatted as any other Excel chart.

For a quick refresher of the data requirements, click on the large button above the OK button to see the following screen.

Example Usage

The following two examples are offered to help illustrate how to use the Cluster Stack Utility.

Example 1

An office supplies exporter wants to examine quarterly shipments (Q1 through Q4) of two product lines (pencils and pens) to three geographic regions (North America, Europe, and Asia). A table of the data is shown below; this table is already arranged in the input format required by the utility. The geographical regions comprise the categories, each of the two product lines will be plotted as adjacent columns above each category, and the four quarter results will be stacked in each of these columns.

Select the data range and click the button on the menu, toolbar, or ribbon. The range address is indicated in the edit box, as are the number of rows (categories) and columns (series or blocks). By default, the utility has selected two columns per category, and split the eight series evenly between them.

Since this is the desired arrangement, click OK. The utility inserts a new worksheet, creates a table with values linked to the selected range above, but staggered in order to create the clustered and stacked appearance of the chart.

The clustered-stacked bar version of the chart is shown below:

Example 2

This example uses the same data as the first, but the input table below has been rearranged to plot quarterly columns for each region, with the two product lines stacked together.

Note that the entire range need not be selected. If you select one cell in a range, the utility detects the larger range as the initial range in the dialog. Of course you can select a more appropriate range. The columns per category option has been changed to 4, and the list of stacks and blocks has been updated to show two blocks per stacked column.

When the dialog entries are approved, the utility creates the corresponding linked table and chart.

Accessing the PTS Cluster Stack Utility via VBA

The ability to call the PTS Cluster Stack Utility from other VBA procedures has been incorporated in this version of the utility. Documentation is incomplete but will be added here. The following brief explanation should help get experienced programmers started.

The core of the PTS Cluster Stack Utility is a procedure called PTS_ClusterStackChart, which can be called from other VBA procedures. The syntax of the PTS_ClusterStackChart function is:

        Function PTS_ClusterStackChart(ChartData As Range, _
                                      Optional [DataLayouts], _
									  Optional ChartStyle As String = "Column") _
                                      As Chart

ChartData is the range containing the data to chart, laid out as described above and repeated below:

Contact Me