Peltier Tech Waterfall Chart Utility Documentation

Installation

If you have not done so already, download the utility's setup file and save it in a convenient local directory. Do not try to install the utility directly from the internet link.

To install, the user must be logged into their Windows user account. If administrator credentials are required, the admin should not log the user out. The user must remain logged into Windows, and the setup file must be run from the user's account, using "Run As" with the admin credentials entered at the prompt.

The setup file creates the following directory:

• C:\Users\username\AppData\Roaming\PTS Charts (Windows Vista, Windows 7)
• C:\Documents and Settings\username\Application Data\PTS Charts (Windows XP)

and installs the following program files into this directory:

• PTSWaterfall.xla
• PTSWaterfall.xlam

PTSWaterfall.xla is the installed add-in, even in Excel 2007 and later. For the later Excel versions, PTSWaterfall.xlam is a supplementary file that controls the ribbon interface for the utility.

The installation process may leave uninstallation files such as unins001.exe and unins001.dat in this directory. During operation the program may save user preferences and other settings in one or more text or binary files in this directory. The user is discouraged from altering any of these files, and from storing any work files in this directory.

The program also creates the following directory:

• C:\Users\username\Documents\PTS Charts (Windows Vista, Windows 7)
• C:\Documents and Settings\username\My Documents\PTS Charts (Windows XP)

Re-installation

If something changes or goes wrong (new computer, hard drive crash, Windows or Excel upgrade), the easiest way to reinstall the utility is to rerun the setup file you downloaded and saved when you first purchased the license.

If Excel just seems to have forgotten the utility was installed, you can use the manual steps to install the utility. First, open the Add-Ins dialog:

• Excel 2003 and earlier: Tools menu > Add-Ins
• Excel 2007: Office button > Excel Options > Add-Ins > Go
• Excel 2010: File menu > Options > Add-Ins > Go

If the list of add-ins includes PTS Waterfall Plotter, check the box in front of this entry and click OK until you're returned to Excel.

If the utility is not listed in the Add-Ins dialog, click Browse and navigate to this directory:

• C:\Users\username\AppData\Roaming\PTS Charts (Windows Vista, Windows 7)
• C:\Documents and Settings\username\Application Data\PTS Charts (Windows XP)

Select PTSWaterfall.xla, then click OK until you're back in Excel.

The Excel User Interface

Excel 2000 through 2003 Interface

Excel 2007 through 2010 Interface

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.

Using the Utility

Data

The Waterfall Chart Utility uses a two-column wide range, as shown below left. The same range is shown below right, with regions of the data range highlighted.

The first row, highlighted orange, contains a column header in the second cell. This row is optional; if it is not included, the utility inserts a row for this and other column headers.

The first column, highlighted green, contains category labels, which will be located along the horizontal axis in the Waterfall Chart. I've used labels like "START" and "FINISH" in these examples, but you can use whatever labels you want.

The second column, highlighted blue, contains values, either the initial value in the first blue cell, or incremental values in the other cells. A blank in this column, as in the last row, will result in a bar from the horizontal axis to the current value, not a floating bar between the previous and current values.

The last row, highlighted yellow, contains a label for the last category in the first cell and a blank value in the second cell. This row is optional; if it is not included, the utility inserts a row for this final label and value.

Making Your First Waterfall Plot

Select your data. You can either select the entire two-column range, or select one cell wihtin the range and let the utility determine the size of the range. Click the Waterfall Plot button on the menu or ribbon to launch the dialog, check the settings in the dialog, and click OK.

The utility inserts a row if your selected range did not have column headers, and inserts another row if the range did not have a blank in the values column for the final category. Then the utility constructs a block of cells to the right of the starting data with formulas needed to draw the chart elements that produce the floating bars and labels.

Finally the utility draws the Waterfall Chart.

Waterfall With Intermediate Totals

The Waterfall Chart Utility makes it easy to add a full height (not floating) column in the middle of the waterfall plot, to show an intermediate subtotal. To do this, enter a label for the subtotal in the data range, with a blank in the value column. This is illustrated in the table below: in addition to the bar at START, there will be a bar at FINISH and another at MIDDLE.

As in the first example, select the data (or a single cell within the data) and click the Waterfall Plot button. The utility constructs the appropriate data range and builds your chart.

Formatting Your Waterfall

The Waterfall Chart Utility creates a waterfall chart using a regular Excel chart. The magic is in the arrangement of data, the formulas, and the combination of elements in the chart, and in the rapid creation of the chart.

Since your waterfall chart is a regular Excel chart, you can format its individual elements using Excel's formatting tools which you are laready familiar with. Starting with the original waterfall chart...

... you can clean it up by removing unnecessary gridlines and borders, ...

... resize and move the chart, change the font size, ...

... and change the colors of the different bars.

When formatting the bars, note that all up bars (green in the original chart) must have the same formats, and all down bars (red in the original) must have the same formats, which may be different than the up bar formats. The full height bars (blue in the original chart) can be formatted the same, or if you select each bar separately (select a single bar by single-clicking on it twice) you can format it independently of the others.

Modifying Your Data

The Waterfall Chart Utility's output range has formulas that make it easy to insert, delete, and rearrange rows without breaking the chart. You must perform all of your changes between the START and FINISH rows.

Inserting a Row

To insert a row, select the row of the calculated table that you want to insert a line above. Right click on the selected row, choose Insert from the pop-up menu, and select Shift Cells Down in the Insert dialog that pops up.

The inserted row is now highlighted, and blank. The chart is messed up, but we'll fix that in a moment.

Fill the blank row with formulas from the row above by pressing Ctrl+D, the shortcut for "Fill Down". Note: if you insert a row immediately below the START row, Ctrl+D will fill in the wrong formulas. Instead, copy another row and paste this into the empty row.

The paste inserts enough information to fix the chart. Now simply enter the appropriate label and value into the first two cells of the inserted row (shaded in the example), and the table and chart correctly incorporate the new data.

If you want to insert a new intermediate subtotal, enter a label into the first cell of the inserted row and clear the second cell (the two shaded cells below).

Deleting a Row

To delete a row, select the row of the calculated table that you want to delete. Right click on the selected row, choose Delete from the pop-up menu, and select Shift Cells Up in the Delete dialog that pops up.

The table and chart update instantly.

Rearranging Rows

To move a row to a new position in the table, select the row, then hold Shift while you drag it to a new position. The new position is highlighted with a thick light gray I-beam cursor.

The table and chart update as soon as you release the mouse button.

Sorting Rows

Select the range of cells to be sorted. Do not select the START or FINISH rows. To sort by values, press tab so the active cell moves right one column as shown below.

Press the Sort Ascending button (A to Z) to sort from smallest (or most negative) to largest (or most positive) value.

Press the Sort Descending button (Z to A) to sort from largest (or most positive) to smallest (or most negative) value.

Sort Ascending (shown below) or Descending with the active cell in the first column to sort by category label.

Dialog Options

Sorting Categories

The Peltier Tech Waterfall Chart Utility allows you to sort the chart's data in several ways. This is a one-time sort applied to the data before the chart is drawn. Note that it may be easier to create simple unsorted charts, and modify the data as shown above.

Unsorted

Sorted by Increasing Value

The values between START and FINISH have been sorted in increasing order, from most negative to most positive.

Sorted by Decreasing Value

The values between START and FINISH have been sorted in decreasing order, from most positive to most negative.

Sorted Alphabetically

The labels between START and FINISH have been sorted alphabetically, from A to Z.

Chart Location

The Waterfall Chart Utility provides two options for placement of your waterfall charts. The most common option, and the one shown in all of the examples above, adds the formulas to the selected data range in the active sheet and builds the chart nearby, the way Excel draws its built-in charts. A second option inserts a blank worksheet, copies the selected data to this worksheet (linked to the original data range), and builds the data table and chart in this new worksheet.

Accessing the Peltier Tech Waterfall Utility via VBA

The core of the Peltier Tech 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 Peltier Tech 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 as a chart variable to the calling procedure. The calling procedure can then perform operations on this chart variable.

        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()
  
            Application.Run "'PTSWaterfall.xla'!PTS_WaterfallChart", _
                ActiveSheet.Range("A1:B11"), True, True, 0, True
  
        End Sub

A more effective way to use the Peltier Tech Waterfall Chart Utility programmatically from your VBA project is to set a reference to the utility from within your project. From the VB Editor's Tools menu, choose References, then scroll down until you find PTSWaterfall2000 in the list, and check the box in front of it.

Setting a reference allows you to use the utility in Early Binding mode. You have access to its public functions and constants in the Object Browser, and you have the use of IntelliSense to help you while programming.

The members of the PTSWaterfall2000 library are shown in the Object Browser.

The syntax of the PTS_WaterfallChart function can be examined.

The SortData constants are enumerated.

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 Peltier Tech Waterfall Chart Utility

          PTS_WaterfallChart ActiveCell.CurrentRegion, True, True, SortDataUnsorted

        End Sub

Contact Peltier Tech

In the event of problems with this utility, use the following link to send an email. I respond especially quickly to bug reports and to problems users report with installation and use of the utility. 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, and screen shots of any error messages.

I welcome comments about this utility. I consider suggestions for reasonable enhancements. I do plan eventually to introduce a Mac versions of the utility.