For Jon Peltier

[dee]

Hi Jon,

I have posted tonight a couple of times re waterfall charts. I am blonde
and exhausted, so maybe I don't get it...

I have:

Supplier Spent
Supplier1 149
Supplier2 140
Supplier3 75
Supplier4 23
Supplier5 13
Supplier6 11
Supplier7 42
Total 453

and wish to create a chart that shows the first value (149) as a column
beginning at 0. Then, the second column (140) beginning just above the first
column and so on. At the far right, I wish to see the total column.

I tried this (which I found at a Web site):

Supplier Spent Invisible
Supplier1 149 304
Supplier2 140 164
Supplier3 75 89
Supplier4 23 66
Supplier5 13 53
Supplier6 11 42
Supplier7 42 0
Total 453

But it didn't work... just kept my columns all starting at 0...

What am I missing?

I was also wondering if I could display both the value and the percentage of
the total above each bar without using an add-in.

Thanks so very much for any help you can provide.

 

[Jon Peltier]

You were probably on the right track. I rearranged your columns and added
some. The following is in A1:F9 (read it with a nonproportional font, like
Courier).

Supplier Invisible Spent Total Cumulative Label
Supplier1 0 149 149 149¶(32.9%)
Supplier2 149 140 289 140¶(30.9%)
Supplier3 289 75 364 75¶(16.6%)
Supplier4 364 23 387 23¶(5.1%)
Supplier5 387 13 400 13¶(2.9%)
Supplier6 400 11 411 11¶(2.4%)
Supplier7 411 42 453 42¶(9.3%)
Total 453 453 453

Columns A and C are your original data. Cell B2 contains zero. Cell B3
contains the formula

=SUM(C$2:C2)

which is filled down to B8. Cell D9 contains this formula:

 

[Jon Peltier]

Dee -

You were probably on the right track. I rearranged your columns and added a
few. The following is in A1:F9 (read it with a nonproportional font, like
Courier).

Supplier Invisible Spent Total Cumulative Label
Supplier1 0 149 149 149¶(32.9%)
Supplier2 149 140 289 140¶(30.9%)
Supplier3 289 75 364 75¶(16.6%)
Supplier4 364 23 387 23¶(5.1%)
Supplier5 387 13 400 13¶(2.9%)
Supplier6 400 11 411 11¶(2.4%)
Supplier7 411 42 453 42¶(9.3%)
Total 453 453 453

Columns A and C are your original data. Cell B2 contains zero. Cell B3
contains the formula

=SUM(C$2:C2)

which is filled down to B8. Cell D9 contains this formula:

=SUM(C2:C8)

Cell E2 contains this formula:

=SUM(C$2:C2)

(that's right, same as B3), which is filled down to E8. Cells E9 and F9
contain this formula:

=D9

Cell F2 contains

=C2&CHAR(10)&"("&TEXT(C2/$D$9,"0.0%")&")"

which is filled down to F8. CHAR(10) produces a line feed, which I've
represented as ¶ in my table above, but which appears as a line feed in the
cell (if wrapping is allowed), or as a small rectangle, or sometimes as
nothing.

Select A1:E8 and create a stacked column chart. Select the Cumulative
series, go to Chart menu > Chart Type, and select a line chart. I usually
tell people here to use one of these add-ins to add the labels from column F
to the Cumulative series, using the 'above' alignment. You don't want to use
an add-in, so here's the manual approach.

1. Double click the Cumulative series.
2. On the Data Labels tab, select any of the settings.
3. Double click the new labels.
4. On the Alignment tab, choose the Label Position 'Above'.
5. Select the first label (one click to select the series of labels, another
click to select a single label).
6. Type = in the formula bar, and click on cell F2.
7. Repeat steps 5 and 6 for the rest of the labels, using the cells F3:F9
for the remaining labels.

Now double click the Cumulative line chart series, and on the Patterns tab
choose No Line and No Marker. Double click the Invisible series, and on the
Patterns tab, choose No Border and No Area Fill to make it invisible.

- Jon