Waterfall Chart Utility.

In Waterfall Charts, Fancy Waterfall Chart, and Waterfall Chart that Crosses the X Axis, I have described techniques for constructing waterfall charts in Excel. I have also written a Waterfall Chart utility that allows the user to construct waterfall charts charts directly from the data; the utility adds the necessary data to the table and then creates and formats the chart from this table. The utility is designed to work in Excel versions 2000, 2002, and 2003, and adjustments have been made to allow its use in 2007, though it's not fully integrated into the Excel 2007 Ribbon. The utility has not been tested in any Macintosh version or in Excel 97, it may or may not work on these versions. The Waterfall Chart utility is a beta version, available at no cost. Ultimately it will be incorporated into a commercial Advanced Charting utility. The Waterfall Charter dialog is shown below:

Download

The utility can be downloaded from here, which links to a zip file. The zip file contains an EXE file which installs the Waterfall Chart utility.

Installation

Close Excel, then double click on the PTS_Waterfall_Charter_Setup.exe file from the zip file you've downloaded. The Waterfall Chart utility is installed as an 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.

Using the Utility

When you install the add-in, it adds a new menu, PTS Charts, to Excel's Worksheet and Chart Menu Bars. The PTS Charts menu has a new item, Waterfall Charter, added to it. This menu and menu item are shown below:

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

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. If the column to the right of the selected range is not blank, it will be overwritten.

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 new columns of data next to the original table and an embedded chart. The chart is a standard Excel chart; it can be moved, resized, and otherwise formatted as any other Excel chart. The added columns of data and the chart are dynamic: if you change the data in the original range, the chart will update automatically.

Versions

• Version 100 - Build 1114 - 12 November 2006 - Initial version
• Version 100 - Build 1115 - 13 November 2006 - Enables sorting of input data
• Version 103 - Build 1120 - 26 July 2007 - Adds optional labels above columns in chart
• Version 103 - Build 1121 - 27 July 2007 - Corrects problems with non-US regional settings
• Version 110 - Build 1301 - 18 January 2008 - Corrects infrequent error upon Excel shutdown, installs in Excel 2000 - 2007
  Note: Uninstall previous version before installing this update

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 have immediate plans to extend its compatibility to Mac versions of Excel or Excel 2007. The extensive data validation required to enable multiple area ranged prevent me from addressing this capability at this time.

Check back for news and updates.

Contact Jon about the Waterfall Chart utility.