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 inserts a new worksheet, adds a table with the appropriate data and the necessary formulas, and then creates and formats a waterfall chart from this table. The utility is designed to work in Excel versions 2000, 2002, 2003, and 2007. The utility has not been tested in any Macintosh version or in Excel 97, it may or may not work on these versions.

There are two versions of this utility, a Free Demo and a Professional version. Features of these versions are compared below.

The Waterfall Chart dialogs are shown below:

Professional Waterfall Chart Dialog

Waterfall Chart Demo Dialog

Purchase the Professional Utility

The utility can be purchased from the PTS Excel Waterfall Chart Utility page.

Download the Demo Version

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

Before installing a new version of the PTS Waterfall Chart Utility, you should remove any existing versions.

Start installation by closing Excel, then double click on the PTS_Waterfall_Chart_Demo.exe file from the zip file you’ve downloaded or the PTS_Waterfall_Chart_Pro.exe file from the zip file you’ve purchased. 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.

Using the Utility

Excel 2000 through 2003
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
When installed, the professional utility adds a new custom ribbon tab, PTS Charts, to Excel’s ribbon. The new tab has a custom group, Custom Charts, added with a new item, Waterfall Plot. 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.

To use the utility, select a range of data to chart, then select the Waterfall Plot 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 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.

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. The extensive data validation required to enable multiple-area ranges prevent me from addressing this capability at this time.

Contact Jon about the Waterfall Chart utility.

Possibly Related Posts:

• Waterfall Utility – Last Time I’ll Bug You
• Waterfall Utility Update
• Low Box Plot Utility Discount Price Expires Soon
• Introducing PTS Dot Plot Utility
• Error Bars in Excel 2007 Charts
• Announcing the Box and Whisker Chart Utility
• PTS Charts in Excel 2010
• Deming Regression Utility
• Simple Waterfall Chart with Up-Down Bars
• LOESS Utility – Awesome Update