Peltier Tech
Chart Utilities

Excel Dashboards

Books at Amazon.com

Buy me a coffee

If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee.

Waterfall Charts.

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. At the end of the page you'll find a link to a utility for creating waterfall charts easily.

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.

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:

Peltier Technical Services, Inc.

Commercial Utilities | Custom Solutions | Training | 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