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

Peltier Tech
Chart Add-Ins

Excel Dashboards

Books at Amazon.com

Waterfall Charts that Cross the X Axis.

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, described in the Waterfall Charts and Fancy Waterfall Chart pages on this web site, are a graphical approach to show how an initial value is increased and decreased by intermediate values, to reach its final value. When the intermediate value drops below the X axis, the stacked column chart approach no longer accurately displays these values. This page shows how to combine the Waterfall Chart technique with the Stacked Column Charts that Cross the X Axis approach to correct the chart's appearance.

The input data shown below contains the previous year's profit and a list of items that contribute to profit with their corresponding effects on the following year's profit.

	A	B
1		Amount
2	Profit 2003	-3.81
3	Trade Profit	5.71
4	Prime Costs	1.2
5	Conversion Costs	-0.71
6	A&P	1.41
7	Other F&S	2.72
8	G&A	-1.12
9	Volume Effect	-10.27
10	Price Effect	6.35
11	Profit 2004

The table below provides the complete data range needed to chart how profit changes from 2003 to 2004 as various parameters are added or subtracted. Column A contains labels, column B the values. Column C uses formulas to determine the cumulative value. Columns D through J use formulas to compute the values to be plotted as stacked columns, following the examples cited in the previous paragraph. Column K uses a formula to compute values to be plotted as a line chart series, for adding labels above the columns in the chart; omit Column K if you don't want labels.

	A	B	C	D	E	F	G	H	I	J	K
1		Amount	Cum	endpoints	blank neg	red neg	grn neg	blank pos	red pos	grn pos	Y label
2	Profit 2003	-3.81	-3.81	-3.81							0
3	Trade Profit	5.71	1.9		0	0	-3.81	0	0	1.9	1.9
4	Prime Costs	1.2	3.1		0	0	0	1.9	0	1.2	3.1
5	Conversion Costs	-0.71	2.39		0	0	0	2.39	0.71	0	3.1
6	A&P	1.41	3.8		0	0	0	2.39	0	1.41	3.8
7	Other F&S	2.72	6.52		0	0	0	3.8	0	2.72	6.52
8	G&A	-1.12	5.4		0	0	0	5.4	1.12	0	6.52
9	Volume Effect	-10.27	-4.87		0	-4.87	0	0	5.4	0	5.4
10	Price Effect	6.35	1.48		0	0	-4.87	0	0	1.48	1.48
11	Profit 2004	1.48		1.48							1.48

These are the formulas in columns C:J.

Formulas filled down to row 10
Cell	Formula
C2:	`=SUM(B2:B$2)`
E3:	`=IF(AND(C3<0,C2<0),MAX(C2:C3),0)`
F3:	`=IF(AND(C3<0,B3<0),MAX(B3,C3),0)`
G3:	`=IF(AND(C2<0,B3>0),MAX(-B3,C2),0)`
H3:	`=IF(AND(C3>0,C2>0),MIN(C2:C3),0)`
I3:	`=IF(AND(C2>0,B3<0),MIN(-B3,C2),0)`
J3:	`=IF(AND(C3>0,B3>0),MIN(B3,C3),0)`
Formulas filled down to row 11
Cell	Formula
K2:	`=MAX(D2,SUM(H2:J2))`
Formulas in one cell only
Cell	Formula
D2:	`=B2`
D11:	`=C10`
B11:	`=C10`

Select the two-area range highlighted in green in the table above. To select multiple areas, select the first area, then hold the Ctrl key while selecting each additional area.

With this range selected, start the Chart Wizard and create a stacked column chart, shown below:

Double click on the X axis, select the Patterns tab, check None for Major and Minor Tick Marks and Low for Tick Mark Labels; select the Alignment tab, and change the Orientation to 90 degrees. Hide the Blank Pos and Blank Neg column series: select the series or the corresponding legend key and use the shortcut key Ctrl+1 (numeral one); select the Patterns tab, and choose None for Border and for Area. Format the other series appropriately: this example uses green for positive changes, red for negative changes, and blue for the initial and final values. While formatting any of the columns, click on the Options tab, and set the Gap Width to 0, so adjacent columns touch each other.

Finally, remove the legend to obtain the completed chart:

To add labels, copy the values in column K (shaded yellow above), select the chart, then use Paste Special from the Edit menu to add the data as a new series, with series name in the first row. Excel adds the data as another series of columns, stacked on top of the others, using the existing category labels.

Select the new series, go to the Chart menu, choose Chart Type, and select a line chart style.

Apply data labels to this series. The manual (i.e., tedious) way is to double click on the series, use the Data Labels tab to add arbitrary labels, then edit the text of each label. There are a couple of easy to use and free utilities that help you do this automatically:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

Using either of these utilities, apply the "Amount" data from B2:B11 as data labels for the new Y label line chart series.

Finally a little cleanup. Double click on the Y label line series, and on the Patterns tab, pick None for Line and for Markers, so the series becomes invisible. Double click on the category axis, and on the Patterns tab, select Low for Tick Mark Labels. Adjust the Y axis maximum if needed to allow room for the labels.

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