Waterfall Chart - 3 items

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Hi everyone, 'm trying to create a waterfall chart with 3 items for each month.
I found relevant looking chart but need someone to help me out on how to set this up in excel.
Compound Waterfall



Thanks everyone in advance for helping.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

tybaltlives

Active Member
Joined
Nov 6, 2006
Messages
414
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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">How to make a waterfall chart</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;;">Three column method works if all the changes (Dark values) are positive</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">clear</td><td style="font-weight: bold;text-align: center;;">visible</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">start</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">a</td><td style="text-align: right;;">100</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">b</td><td style="text-align: right;;">150</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">c</td><td style="text-align: right;;">180</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">d</td><td style="text-align: right;;">200</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">e </td><td style="text-align: right;;">215</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">f</td><td style="text-align: right;;">225</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">end</td><td style="text-align: right;;">0</td><td style="text-align: right;;">230</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="font-weight: bold;;">If there are negative values - need an extra column.</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">clear</td><td style="font-weight: bold;text-align: center;;">abs</td><td style="font-weight: bold;text-align: center;;">visible</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">start</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">a</td><td style="text-align: right;;">100</td><td style="text-align: right;;">30</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">b</td><td style="text-align: right;;">130</td><td style="text-align: right;;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">c</td><td style="text-align: right;;">145</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">d</td><td style="text-align: right;;">150</td><td style="text-align: right;;">5</td><td style="text-align: right;;">-5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">e </td><td style="text-align: right;;">140</td><td style="text-align: right;;">10</td><td style="text-align: right;;">-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">f</td><td style="text-align: right;;">120</td><td style="text-align: right;;">20</td><td style="text-align: right;;">-20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">end</td><td style="text-align: right;;">0</td><td style="text-align: right;;">120</td><td style="text-align: right;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8</th><td style="text-align:left">=SUM(<font color="Blue">C$7:C7</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=SUM(<font color="Blue">C$7:C8</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=SUM(<font color="Blue">C$7:C9</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B11</th><td style="text-align:left">=SUM(<font color="Blue">C$7:C10</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B12</th><td style="text-align:left">=SUM(<font color="Blue">C$7:C11</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B13</th><td style="text-align:left">=SUM(<font color="Blue">C$7:C12</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C14</th><td style="text-align:left">=SUM(<font color="Blue">C7:C13</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C20</th><td style="text-align:left">=ABS(<font color="Blue">D20</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B21</th><td style="text-align:left">=IF(<font color="Blue">D21>=0,SUM(<font color="Red">D$20:D20</font>),SUM(<font color="Red">D$20:D20</font>)+D21</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C21</th><td style="text-align:left">=ABS(<font color="Blue">D21</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B22</th><td style="text-align:left">=IF(<font color="Blue">D22>=0,SUM(<font color="Red">D$20:D21</font>),SUM(<font color="Red">D$20:D21</font>)+D22</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C22</th><td style="text-align:left">=ABS(<font color="Blue">D22</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B23</th><td style="text-align:left">=IF(<font color="Blue">D23>=0,SUM(<font color="Red">D$20:D22</font>),SUM(<font color="Red">D$20:D22</font>)+D23</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C23</th><td style="text-align:left">=ABS(<font color="Blue">D23</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B24</th><td style="text-align:left">=IF(<font color="Blue">D24>=0,SUM(<font color="Red">D$20:D23</font>),SUM(<font color="Red">D$20:D23</font>)+D24</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C24</th><td style="text-align:left">=ABS(<font color="Blue">D24</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B25</th><td style="text-align:left">=IF(<font color="Blue">D25>=0,SUM(<font color="Red">D$20:D24</font>),SUM(<font color="Red">D$20:D24</font>)+D25</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C25</th><td style="text-align:left">=ABS(<font color="Blue">D25</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B26</th><td style="text-align:left">=IF(<font color="Blue">D26>=0,SUM(<font color="Red">D$20:D25</font>),SUM(<font color="Red">D$20:D25</font>)+D26</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C26</th><td style="text-align:left">=ABS(<font color="Blue">D26</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C27</th><td style="text-align:left">=ABS(<font color="Blue">D27</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D27</th><td style="text-align:left">=SUM(<font color="Blue">D20:D26</font>)</td></tr></tbody></table></td></tr></table><br />
 

tybaltlives

Active Member
Joined
Nov 6, 2006
Messages
414
PS - I can send you the excel file that demonstrates the technique if you PM me your email address. Mine is only two stacked sets of data but it wouldn't be any different for three - you just need to leave one set of data as clear.
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Thanks tybaltlives; 'm gonna pm you my email address. in the mean time trying the data you provided.
 

tybaltlives

Active Member
Joined
Nov 6, 2006
Messages
414
OK. I can send you the file once I get your email. I think I worked out a way to do it with two visible data sets and one invisible one. That will be in the file I send you along with the existing waterfall charts.

-R
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
I have sent the updated chart using your logic...
now I want my chart to calculate the correct totals.
It is almost whatt I want now :)
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Jon Peltier, thanks alot for directing me to your site. I'm fan of your site.
Finally got it working...
:) Pedie
 

Watch MrExcel Video

Forum statistics

Threads
1,095,546
Messages
5,445,116
Members
405,317
Latest member
gcallaway

This Week's Hot Topics

Top