Peltier Technical Services, Inc.
 

Excel Charting Utilities | Custom Solutions | Training | Charts and Tutorials | PTS Blog


Peltier Tech
Chart Utilities

PTS Waterfall Chart Utility
PTS Cluster-Stack Column Chart Utility
PTS Box and Whisker Chart Utility
PTS Marimekko Chart Utility
PTS Dot Plot Utility
PTS Cascade Chart Utility

 

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.

 ABCDEF
1 InvisibleFinalMinusPlusInitial
2 1000    
3Initial    10000
4Alpha10000  2000 
5Beta9000 3000  
6Gamma7000 2000  
7Delta6000 1000  
8Epsilon6000  2500 
9Final 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.
 

 

Page copy protected against web site content infringement by Copyscape

 

PTS Waterfall Chart Utility PTS Cluster-Stack Column Chart Utility PTS Box and Whisker Chart Utility PTS Marimekko Chart Utility PTS Dot Plot Utility PTS Cascade Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


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

Microsoft Most Valuable Professional

My MVP Profile