Thanks:  0
Likes:  0

# Thread: Waterfall Chart - 3 items

1. ## Waterfall Chart - 3 items

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.

2. ## Re: Waterfall Chart - 3 items

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
12e 21510
13f2255
14end0230
15
16
17
18If there are negative values - need an extra column.
19clearabsvisible
20start0100100
21a1003030
22b1301515
23c1451010
24d1505-5
25e 14010-10
26f12020-20
27end0120120

Sheet1

Worksheet Formulas
CellFormula
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)

3. ## Re: Waterfall Chart - 3 items

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.

4. ## Re: Waterfall Chart - 3 items

Thanks tybaltlives; 'm gonna pm you my email address. in the mean time trying the data you provided.

5. ## Re: Waterfall Chart - 3 items

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

6. ## Re: Waterfall Chart - 3 items

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

7. ## Re: Waterfall Chart - 3 items

My tutorial on waterfall charts in Excel includes a compound waterfall example:
Excel Waterfall Charts (Bridge Charts)
This may have saved a little time.

8. ## Re: Waterfall Chart - 3 items

Jon Peltier, thanks alot for directing me to your site. I'm fan of your site.
Finally got it working...
Pedie

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•