Waterfall Chart - 3 items

**pedie** · Jan 8th, 2014, 01:18 PM

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.

**tybaltlives** · Jan 8th, 2014, 01:45 PM

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
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)

**tybaltlives** · Jan 8th, 2014, 01:49 PM

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** · Jan 8th, 2014, 02:04 PM

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

**tybaltlives** · Jan 8th, 2014, 02:17 PM

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** · Jan 8th, 2014, 03:21 PM

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

**Jon Peltier** · Jan 17th, 2014, 03:29 PM

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