A waterfall chart is simply a stacked bar chart with a portion of the stacked bars formatted to be clear (you do this manually).
to create the waterfall effect.
There are two possibilities depending on if your data contains all positive or mixed positive and negative values. If both positive or negative values are present, you need three columns. One is the absolute value of the data column that contains the negative data.
Here is some data to show you how it works. Just plot the "start" column and the next two columns.
Excel 2010
A B C D E F G 1 How to make a waterfall chart 2 3 4 5 Three column method works if all the changes (Dark values) are positive 6 clear visible 7 start 0 100 8 a 100 50 9 b 150 30 10 c 180 20 11 d 200 15 12 e 215 10 13 f 225 5 14 end 0 230 15 16 17 18 If there are negative values - need an extra column. 19 clear abs visible 20 start 0 100 100 21 a 100 30 30 22 b 130 15 15 23 c 145 10 10 24 d 150 5 -5 25 e 140 10 -10 26 f 120 20 -20 27 end 0 120 120 Sheet1
Worksheet Formulas
Cell Formula B8 =SUM(C$7:C7) B9 =SUM(C$7:C8) B10 =SUM(C$7:C9) B11 =SUM(C$7:C10) B12 =SUM(C$7:C11) B13 =SUM(C$7:C12) C14 =SUM(C7:C13) C20 =ABS(D20) B21 =IF(D21>=0,SUM(D$20:D20),SUM(D$20:D20)+D21) C21 =ABS(D21) B22 =IF(D22>=0,SUM(D$20:D21),SUM(D$20:D21)+D22) C22 =ABS(D22) B23 =IF(D23>=0,SUM(D$20:D22),SUM(D$20:D22)+D23) C23 =ABS(D23) B24 =IF(D24>=0,SUM(D$20:D23),SUM(D$20:D23)+D24) C24 =ABS(D24) B25 =IF(D25>=0,SUM(D$20:D24),SUM(D$20:D24)+D25) C25 =ABS(D25) B26 =IF(D26>=0,SUM(D$20:D25),SUM(D$20:D25)+D26) C26 =ABS(D26) C27 =ABS(D27) D27 =SUM(D20:D26)
Like this thread? Share it with others