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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
ABCDEFG
1How to make a waterfall chart
2
3
4
5Three column method works if all the changes (Dark values) are positive
6clearvisible
7start0100
8a10050
9b15030
10c18020
11d20015
12e21510
13f2255
14end0230
15
16
17
18If there are negative values - need an extra column.
19clearabsvisible
20start0100100
21a1003030
22b1301515
23c1451010
24d1505-5
25e14010-10
26f12020-20
27end0120120
Sheet1
Cell Formulas
RangeFormula
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)
B21=IF(D21>=0,SUM(D$20:D20),SUM(D$20:D20)+D21)
B22=IF(D22>=0,SUM(D$20:D21),SUM(D$20:D21)+D22)
B23=IF(D23>=0,SUM(D$20:D22),SUM(D$20:D22)+D23)
B24=IF(D24>=0,SUM(D$20:D23),SUM(D$20:D23)+D24)
B25=IF(D25>=0,SUM(D$20:D24),SUM(D$20:D24)+D25)
B26=IF(D26>=0,SUM(D$20:D25),SUM(D$20:D25)+D26)
C14=SUM(C7:C13)
C20=ABS(D20)
C21=ABS(D21)
C22=ABS(D22)
C23=ABS(D23)
C24=ABS(D24)
C25=ABS(D25)
C26=ABS(D26)
C27=ABS(D27)
D27=SUM(D20:D26)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
Jon Peltier, thanks alot for directing me to your site. I'm fan of your site.
Finally got it working...
:) Pedie
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top