PTS Waterfall Chart Utility Documentation.

Installation

If you have installed an earlier (demo) version of this utility, you should uninstall that version before continuing. Choose Add-ins from Excel's Tools menu, locate Waterfall Charting Utility in the list of add-ins, uncheck the box in front of the entry, and press OK.

Start installation by closing Excel, then double click on the PTS_Waterfall_Chart_Setup.exe file. The Waterfall Chart utility is installed as an Excel add-in. In addition, WaterfallData.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, a new menu called PTS Charts is added 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 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.

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 chart can be created on a new worksheet or on the active sheet. If a new worksheet is used, the data is copied to the new worksheet with links to the original data, formulas are added, and the chart is placed below the data. If the active sheet is used, the formulas are inserted to the right of the original data, and the chart is created half the height and half the width of the active window, centered within the window.

In either case, 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, _
                                    Optional bNewSheet As Boolean = True) _
                                    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. 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. bNewSheet is true to use a new sheet for the output data and chart, false to use the active sheet. 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, True)
  
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 "'PTSWaterfall.xla'!PTS_WaterfallChart", _
      ActiveSheet.Range("A1:B11"), True, True, 0, True
  
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_WaterfallChart ActiveCell.CurrentRegion, True, True, SortDataUnsorted

End Sub

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.