PTS Waterfall Chart Utility Documentation.

Installation

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, Waterfall Plot, added to it. The add-in also adds a new toolbar, PTS Charts, and a Waterfall Plot 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 Waterfall Plot. If you use other PTS charting utilities, they also appear on this custom tab. This new ribbon tab is shown below:

The demo utility doesn't have specific Excel 2007 user interface features, so it merely adds a PTS Charts menu and a Waterfall Plot menu item to the menu commands group of the Add-Ins tab, as shown below:

The demo's user interface isn't as well integrated into the ribbon, but it is still functional.

The Waterfall Chart Dialog

To use the utility, select a range of data to chart, then select the Waterfall Plot menu item, and the dialog appears.

Professional Waterfall Chart Dialog
 

Waterfall Chart Demo Dialog
 

Example Usage

The Waterfall Charter Utility requires a two column wide data range as shown below, with several blank columns to the right. If your selected data range isn't properly shaped, the utility will make some attempt to figure out what data to use. Your column ought to have a label above the values in the second column, and a label with a blank value in the last row; if you omit either of these, the utility will insert them for you.

If the selected range contains chartable data, it is indicated in the range selection box at the top of the dialog. 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, similarly to how the Chart Wizard does. If the selection is larger than one cell, the utility identifies only the selected range in the box. If the selected range consists of multiple discontiguous areas, you may get unpredictable results.

The output produced by this utility consists of a new worksheet. The selected data is copied to this worksheet, formulas are added, 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.

Sorting Categories

The PTS Waterfall Chart Utility allows you to sort the output data and chart in several ways.

Unsorted

Sorted by Increasing Value

The values after the Start value have been sorted in increasing order, from most negative to most positive.

Sorted by Decreasing Value

The values after the Start value have been sorted in decreasing order, from most positive to most negative.

Sorted Alphabetically

The labels between the first and last have been sorted alphabetically, from A to Z.

Inserting Intermediate Totals

If you want an intermediate category with a cumulative value column (blue in this illustration), enter a label with a blank value, and the utility will compute the intermediate total for you. If you insert a label for an intermediate value, and select one of the sorting options, the intermediate label is ignored and the data is sorted.

Accessing the PTS Waterfall Utility via VBA

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

        Function PTS_WaterfallChart(rngData As Range, _
                                      bFirstLabels As Boolean, _
                                      bDataLabels As Boolean, _
                                      lSortData As SortData) _
                                      As Chart

rngData is the range containing the data to chart, bFirstLabels is true if the first row or column of the data contains labels for each set of data, bDataLabels is true if the values should be used in data labesl above each point, and lSortData indicates whether to sort the data, where 0 is not to sort, 1 is to sort by increasing value, 2 is to sort by decreasing value, and 3 is to sort the categories alphabetically. The newly created waterfall chart is returned by the function to the calling procedure for further execution.

The simplest way to use the PTS Waterfall Chart Utility from your own code is to call it using Application.Run, as in the following sample macro. The PTS_WaterfallChart procedure is a function, which returns the waterfall chart to the calling procedure.

Sub TestWaterfallAppRunFunction()
  
  Dim chtWaterfall As Chart
  
  Set chtWaterfall = Application.Run("'PTSWaterfall.xla'!PTS_WaterfallChart", _
      ActiveSheet.Range("A1:B11"), True, True, 0)
  
End Sub

The PTS_Waterfall procedure can also be called as a sub, without returning any results to the calling procedure.

Sub TestWaterfallAppRunSub()
  
  Dim chtWaterfall As Chart
  
  Application.Run "'OTSWaterfall.xla'!PTS_WaterfallChart", _
      ActiveSheet.Range("A1:B11"), True, True, 0
  
End Sub

Intellisense shows the syntax of the PTS_WaterfallChart function as soon as you type it into a code module:

Intellisense displays the allowable values for the SortData argument, and allows you to select the desired option from the list:

As with the Application.Run approach, PTS_WaterfallChart can be called as a function, returning the waterfall chart to the calling procedure.

Sub TestWaterfallChartFunction()
  ' VBIDE: Tools menu > References: set reference to PTS_WaterfallChart
  Dim chtWaterfall  As Chart
  
  Set chtWaterfall = PTS_WaterfallChart(ActiveCell.CurrentRegion, True, True, SortDataUnsorted)

End Sub

PTS_WaterfallChart can also be called as a sub, without returning any results to the calling procedure.

Sub TestWaterfallChartSub()
  ' VBIDE: Tools menu > References: set reference to PTS Box Charter

  PTS_WaterfallChartActiveCell.CurrentRegion, True, True, SortDataUnsorted

End Sub

Version Information

Contact Me

In the event of problems with this utility, use the following link to send me an email. While I cannot answer all email messages, I will certainly address any issues that arise. If you are reporting a problem, please describe the problem as clearly as possible, and if relevant include a copy of the data and chart.

I will also consider reasonable enhancements to the utility. I do not plan to make the utility compatible with Excel 97 or earlier, nor do I plan to extend its compatibility to Mac versions of Excel.