Fancy Waterfall Chart.

This page describes construction of a fancy Waterfall Chart. A 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 chart shows how three classes of revenue increased from 2002 to 2003 because of Volume, Price, and other factors.

Arrangement of the Data

The data is shown below. The top left cell is blank, to tell Excel to use the first row as series names and the first column as category labels. Cells with a yellow background include formulas, while those in white are entered as numbers or text. The Blank column will be the invisible series that causes the others to float. Bottom, Middle, and Top are three classes of revenues being tracked in the chart. Totals provides the vertical position, and Labels provides the text labels, for the data labels we will apply to the chart.

	A	B	C	D	E	F	G
1		Blank	Bottom	Middle	Top	Totals	Labels
2	2003		17	20	25	62	62
3	Volume	62	10	12	14	98	36
4	Price	98	4	6	8	116	18
5	Mix	116	5	5	6	132	16
6	Other	132	4	4	5	145	13
7	2004		40	47	58	145	145

Constructing and Formatting the Chart

Select the first five columns of the data, including the top row of labels. Use the chart wizard to construct a stacked coumn chart, with series in columns.

Now all we have to do is apply a little formatting. First, delete the plot area and horizontal gridlines.

Now remove the borders and apply the specified shades of blue to the Top, Middle, and Bottom series.

Now hide the blank column by removing its border and fill.

For the labels, we need a dummy series. Add the Totals data to the chart (copy the worksheet range, select the chart, and use Paste Special from the Edit menu to add the data as a new series). For now, the Totals series appears as an additional column in the stack.

Right click the new series, choose Chart Type, and select a Line series.

Format the new series so it's invisible (no lines, no markers). Use a third party tool to apply the labels from the Labels column to the Totals series. Both Rob Bovey's Chart Labeler (http://appspro.com) and John Walkenbach's Chart Tools (http://j-walk.com) are good; Rob's allows you to move all labels at once, while John's includes a few additional tools, such as a chart-to-gif file exporter.

One by one, select the legend entries you want to delete (two clicks, one to select the legend, one to select the text; then press Delete). To float all the bars above the X axis, Double click the Y axis and click on the Scale tab, then set the Y axis minimum and the X Axis Crosses At scaling parameters to the same small negative number.

Waterfall Chart that Crosses the X Axis

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