Waterfall Charts that Cross the X Axis.

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	-9.87		0	-9.87	0	0	5.4	0	5.4
10	Price Effect	6.35	1.48		0	0	-9.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.

Waterfall Chart Utility

In this page and in the two links above I have described a technique for constructing waterfall charts in Excel. I have also written a 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 versions 2000, 2002, and 2003, and adjustments have been made to allow its use in 2007, though it's not fully integrated into the Excel 2007 Ribbon. The utility has not been tested in any Macintosh version or in Excel 97, it may or may not work on these versions. The Waterfall Chart utility is a beta version, available at no cost. Ultimately it will be incorporated into a commercial Advanced Charting utility. The Waterfall Charter dialog is shown below: