Waterfall Charts.

Peltier Tech Waterfall Chart Utility This tutorial shows how to create Waterfall Charts, including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, can be prone to error, and becomes tedious.

I have created the Peltier Tech Waterfall Chart Utility to create such charts automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. This is a commercial product, tested on hundreds of machines in a wide variety of configurations, which saves time and aggravation.

Please visit the Peltier Tech Waterfall Chart Utility page or the Peltier Tech Waterfall Chart Utility Documentation page for more information.

Waterfall charts are a special type of Floating Column Charts. A typical waterfall chart shows how an initial value is increased and decreased by a series of intermediate values, leading to a final value. An invisible column keeps the increases and decreases linked to the heights of the previous columns.

This page shows how to arrange your data and create a waterfall chart.

Arrangement of the Data

For this exercise we'll use the following data in A1:F10 of our worksheet. Cell A1 is completely blank, while cell A2 contains a space; these help Excel realize the first row contains series names and the first column contains category labels. The values of 1000 in B2 and in B10 are unnecessary, but they remind me that I want to use these rows to provide a gap between the Y axis and the "Initial" column, and between the "Final" column and the right edge of the plot area.

The green shaded cells contain formulas. The formula in B4 is:

     =SUM(B3,E3:F3)-D4

This is dragged down to fill B4:B8. It merely keeps track of where the bottom of the visible bar (whether Minus or Plus) must be drawn. The formula in C9 is:

     =SUM(B8,E8:F8)-D9

which computes the height of the Final column.

	A	B	C	D	E	F
1		Invisible	Final	Minus	Plus	Initial
2		1000
3	Initial					10000
4	Alpha	10000			2000
5	Beta	9000		3000
6	Gamma	7000		2000
7	Delta	6000		1000
8	Epsilon	6000			2500
9	Final		8500
10		1000

Creating the Waterfall Chart

We'll start creating the waterfall chart by selecting the entire range above, clicking on the Chart Wizard button, and building a stacked column chart.

Double click the "Invisible" series to open the Format Series dialog. Click on the Patterns tab, and make the series truly invisible by choosing None for Border and Fill. Make the legend entry invisible too: click once on the legend to select it, then click once on the legend entry "Invisible" to select just the legend entry, then press the Delete key.

Finally, double click on any of the series to bring up the Format Series dialog again. On the Options tab, set the Gap Width to zero. This widens the columns so that the gap between them vanishes.

Variations on Waterfall Charts

Another page on this site shows a Fancy Waterfall Chart.

The Stacked Column Charts that Cross the X Axis technique can be used to correct a Waterfall Chart that Crosses the X Axis.

PTS Waterfall Chart Utility

In this page I have described a technique for constructing waterfall charts in Excel. I have also developed the PTS 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 for Windows versions 2000, 2002, and 2003, and 2007. The utility has not been tested in any Macintosh version or in Excel 97; it is not expected to work in these versions. The PTS Waterfall Chart Utility dialog is shown below:

Read about the PTS Waterfall Chart Utility.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile