Sharing Waterfall Chart with Automatic Series and Points

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
I want to share a Waterfall Chart I created. A waterfall chart shows beginning and ending budgets with all the additions and reductions in between. The bars seem like they're floating. The spreadsheet allows the user to enter in the Descriptions and Costs with up to 18 points of budget alteration. There is a table that creates the series data for the chart.

If you've ever been asked to create one of these, I'm sure you know how much of a headache this is.

I'm going to give you the spreadsheet formulas and the VBA code to alter the chart. There is VBA code because of the data labels. On this type of chart, data labels are not easy to maintain when there are a fluctuating number of points. Part of the code also changes the X category font size depending on the number of cost points. I put a "Refresh Chart" button on the sheet to run the macro.

I'm going to provide a copy of this workbook as a link to my Google Drive. I'm not making any guarantees how long it will remain there.

https://drive.google.com/file/d/0B-iOpP_9yRXQbnBYTnZlbDd2RU0/view?usp=sharing

If you like this, please post to this thread. I welcome any suggestions.

Jeff




These are for the user to edit the chart titles
Excel 2010
BCD
2Waterfall Chart Example
3Chart Title 2

<tbody>
</tbody>
Cost Comparison



This is the table where the user enters the Descriptions and Costs

Excel 2010
BC
5DescriptionCosts
6Previous Cost81.2
7Non-Recurring Scope(39.8)
8Escalation3.7
9New Non-Recurring Scope25.6
10New Recurring Costs0.3
11Reduction 2(3.0)
12Reduction 3(6.0)
13Reduction 4(9.0)
14Addition 42.0
15Addition 54.0
16Addition 66.0
17Final65.0
18
19
20
21
22
23
24Check (Should = 0):0.0
25Number of Items:12.0
26Number of Possible Items:18.0
27

<tbody>
</tbody>
Cost Comparison

Worksheet Formulas
CellFormula
C17=SUM(C6:C16)
C24=SUM(OFFSET(Costs_hdr,1,0,ICount-1,1))-OFFSET(Costs_hdr,ICount,0)
C25=COUNTA(Descriptions)
C26=ROW($C$24)-ROW(Costs_hdr)-1

<tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
Costs_hdr='Cost Comparison'!$C$5
Descriptions=OFFSET(Descriptions_hdr,1,0,COUNTA(OFFSET(Descriptions_hdr,1,0,'Cost Comparison'!PCount,1)),1)
ICount='Cost Comparison'!$C$25

<tbody>
</tbody>

<tbody>
</tbody>
PCount ='Cost Comparison'!$C$26


This is the table that the chart refers to get all the data
Excel 2010
FGHIJK
3Series1Series2Series3Series4
4Waterfall Graph Data (DO NOT EDIT)
5CostsBaseBudgetAdditionsReductionsPoint
6 81.2 - 81.2 - -1
7 (39.8) 41.4 - - 39.82
8 3.7 41.4 - 3.7 -3
9 25.6 45.1 - 25.6 -4
10 0.3 70.7 - 0.3 -5
11 (3.0) 68.0 - - 3.06
12 (6.0) 62.0 - - 6.07
13 (9.0) 53.0 - - 9.08
14 2.0 53.0 - 2.0 -9
15 4.0 55.0 - 4.0 -10
16 6.0 59.0 - 6.0 -11
17 65.0 - 65.0 - -12
18
19
20
21
22
23

<tbody>
</tbody>
Cost Comparison

Worksheet Formulas
CellFormula
H6=IF(OR(ROW(H6)-ROW($H$5)=1,ROW(H6)-ROW($H$5)=ICount),C6,IF(AND(ROW(H6)-ROW($H$5)>1,ROW(H6)-ROW($H$5)<icount< font="">),0,""</icount<>))
J6=IF(<font color="Blue">ROW(J6)-ROW($J$5)<icount,if(<font color="Red">F6>0,0,-F6ROW(J6)-ROW($J$5)=ICount,0,""</icount,if(
K6=IF(F6<>0,ROW(K6)-ROW($K$5),"")
F6=OFFSET(Costs_hdr,ROW(F6)-ROW($F$5),0)
F7=OFFSET(Costs_hdr,ROW(F7)-ROW($F$5),0)
F8=OFFSET(Costs_hdr,ROW(F8)-ROW($F$5),0)
F9=OFFSET(Costs_hdr,ROW(F9)-ROW($F$5),0)
F10=OFFSET(Costs_hdr,ROW(F10)-ROW($F$5),0)
F11=OFFSET(Costs_hdr,ROW(F11)-ROW($F$5),0)
F12=OFFSET(Costs_hdr,ROW(F12)-ROW($F$5),0)
F13=OFFSET(Costs_hdr,ROW(F13)-ROW($F$5),0)
F14=OFFSET(Costs_hdr,ROW(F14)-ROW($F$5),0)
F15=OFFSET(Costs_hdr,ROW(F15)-ROW($F$5),0)
F16=OFFSET(Costs_hdr,ROW(F16)-ROW($F$5),0)
F17=OFFSET(Costs_hdr,ROW(F17)-ROW($F$5),0)
F18=OFFSET(Costs_hdr,ROW(F18)-ROW($F$5),0)
F19=OFFSET(Costs_hdr,ROW(F19)-ROW($F$5),0)
F20=OFFSET(Costs_hdr,ROW(F20)-ROW($F$5),0)
F21=OFFSET(Costs_hdr,ROW(F21)-ROW($F$5),0)
F22=OFFSET(Costs_hdr,ROW(F22)-ROW($F$5),0)
F23=OFFSET(Costs_hdr,ROW(F23)-ROW($F$5),0)
G7=IF(J7>0,H6-J7,H6)
H7=IF(OR(ROW(H7)-ROW($H$5)=1,ROW(H7)-ROW($H$5)=ICount),F7,IF(AND(ROW(H7)-ROW($H$5)>1,ROW(H7)-ROW($H$5)<icount< font="">),0,""</icount<>))
I7=IF(<font color="Blue">ROW(I7)-ROW($I$5)<icount,if(<font color="Red">F7>0,F7,0ROW(I7)-ROW($I$5)=ICount,0,""</icount,if(
J7=IF(<font color="Blue">ROW(J7)-ROW($J$5)<icount,if(<font color="Red">F7>0,0,-F7ROW(J7)-ROW($J$5)=ICount,0,""</icount,if(
K7=IF(F7<>0,ROW(K7)-ROW($K$5),"")
G8=IF(<font color="Blue">ROW(G8)-ROW($G$5)<icount,if(<font color="Red">AND(I8>0,J7>0),G7,IF(AND(I7>0,J8>0),G7+I7-J8,IF(AND(J8>0,J7>0),G7-J8,G7+I7))ROW(G8)-ROW($G$5)=ICount,0,""</icount,if(
H8=IF(OR(ROW(H8)-ROW($H$5)=1,ROW(H8)-ROW($H$5)=ICount),F8,IF(AND(ROW(H8)-ROW($H$5)>1,ROW(H8)-ROW($H$5)<icount< font="">),0,""</icount<>))
I8=IF(<font color="Blue">ROW(I8)-ROW($I$5)<icount,if(<font color="Red">F8>0,F8,0ROW(I8)-ROW($I$5)=ICount,0,""</icount,if(
J8=IF(<font color="Blue">ROW(J8)-ROW($J$5)<icount,if(<font color="Red">F8>0,0,-F8ROW(J8)-ROW($J$5)=ICount,0,""</icount,if(
K8=IF(F8<>0,ROW(K8)-ROW($K$5),"")
G9=IF(<font color="Blue">ROW(G9)-ROW($G$5)<icount,if(<font color="Red">AND(I9>0,J8>0),G8,IF(AND(I8>0,J9>0),G8+I8-J9,IF(AND(J9>0,J8>0),G8-J9,G8+I8))ROW(G9)-ROW($G$5)=ICount,0,""</icount,if(
H9=IF(OR(ROW(H9)-ROW($H$5)=1,ROW(H9)-ROW($H$5)=ICount),F9,IF(AND(ROW(H9)-ROW($H$5)>1,ROW(H9)-ROW($H$5)<icount< font="">),0,""</icount<>))
I9=IF(<font color="Blue">ROW(I9)-ROW($I$5)<icount,if(<font color="Red">F9>0,F9,0ROW(I9)-ROW($I$5)=ICount,0,""</icount,if(
J9=IF(<font color="Blue">ROW(J9)-ROW($J$5)<icount,if(<font color="Red">F9>0,0,-F9ROW(J9)-ROW($J$5)=ICount,0,""</icount,if(
K9=IF(F9<>0,ROW(K9)-ROW($K$5),"")
G10=IF(<font color="Blue">ROW(G10)-ROW($G$5)<icount,if(<font color="Red">AND(I10>0,J9>0),G9,IF(AND(I9>0,J10>0),G9+I9-J10,IF(AND(J10>0,J9>0),G9-J10,G9+I9))ROW(G10)-ROW($G$5)=ICount,0,""</icount,if(
H10=IF(OR(ROW(H10)-ROW($H$5)=1,ROW(H10)-ROW($H$5)=ICount),F10,IF(AND(ROW(H10)-ROW($H$5)>1,ROW(H10)-ROW($H$5)<icount< font="">),0,""</icount<>))
I10=IF(<font color="Blue">ROW(I10)-ROW($I$5)<icount,if(<font color="Red">F10>0,F10,0ROW(I10)-ROW($I$5)=ICount,0,""</icount,if(
J10=IF(<font color="Blue">ROW(J10)-ROW($J$5)<icount,if(<font color="Red">F10>0,0,-F10ROW(J10)-ROW($J$5)=ICount,0,""</icount,if(
K10=IF(F10<>0,ROW(K10)-ROW($K$5),"")
G11=IF(<font color="Blue">ROW(G11)-ROW($G$5)<icount,if(<font color="Red">AND(I11>0,J10>0),G10,IF(AND(I10>0,J11>0),G10+I10-J11,IF(AND(J11>0,J10>0),G10-J11,G10+I10))ROW(G11)-ROW($G$5)=ICount,0,""</icount,if(
H11=IF(OR(ROW(H11)-ROW($H$5)=1,ROW(H11)-ROW($H$5)=ICount),F11,IF(AND(ROW(H11)-ROW($H$5)>1,ROW(H11)-ROW($H$5)<icount< font="">),0,""</icount<>))
I11=IF(<font color="Blue">ROW(I11)-ROW($I$5)<icount,if(<font color="Red">F11>0,F11,0ROW(I11)-ROW($I$5)=ICount,0,""</icount,if(
J11=IF(<font color="Blue">ROW(J11)-ROW($J$5)<icount,if(<font color="Red">F11>0,0,-F11ROW(J11)-ROW($J$5)=ICount,0,""</icount,if(
K11=IF(F11<>0,ROW(K11)-ROW($K$5),"")
G12=IF(<font color="Blue">ROW(G12)-ROW($G$5)<icount,if(<font color="Red">AND(I12>0,J11>0),G11,IF(AND(I11>0,J12>0),G11+I11-J12,IF(AND(J12>0,J11>0),G11-J12,G11+I11))ROW(G12)-ROW($G$5)=ICount,0,""</icount,if(
H12=IF(OR(ROW(H12)-ROW($H$5)=1,ROW(H12)-ROW($H$5)=ICount),F12,IF(AND(ROW(H12)-ROW($H$5)>1,ROW(H12)-ROW($H$5)<icount< font="">),0,""</icount<>))
I12=IF(<font color="Blue">ROW(I12)-ROW($I$5)<icount,if(<font color="Red">F12>0,F12,0ROW(I12)-ROW($I$5)=ICount,0,""</icount,if(
J12=IF(<font color="Blue">ROW(J12)-ROW($J$5)<icount,if(<font color="Red">F12>0,0,-F12ROW(J12)-ROW($J$5)=ICount,0,""</icount,if(
K12=IF(F12<>0,ROW(K12)-ROW($K$5),"")
G13=IF(<font color="Blue">ROW(G13)-ROW($G$5)<icount,if(<font color="Red">AND(I13>0,J12>0),G12,IF(AND(I12>0,J13>0),G12+I12-J13,IF(AND(J13>0,J12>0),G12-J13,G12+I12))ROW(G13)-ROW($G$5)=ICount,0,""</icount,if(
H13=IF(OR(ROW(H13)-ROW($H$5)=1,ROW(H13)-ROW($H$5)=ICount),F13,IF(AND(ROW(H13)-ROW($H$5)>1,ROW(H13)-ROW($H$5)<icount< font="">),0,""</icount<>))
I13=IF(<font color="Blue">ROW(I13)-ROW($I$5)<icount,if(<font color="Red">F13>0,F13,0ROW(I13)-ROW($I$5)=ICount,0,""</icount,if(
J13=IF(<font color="Blue">ROW(J13)-ROW($J$5)<icount,if(<font color="Red">F13>0,0,-F13ROW(J13)-ROW($J$5)=ICount,0,""</icount,if(
K13=IF(F13<>0,ROW(K13)-ROW($K$5),"")
G14=IF(<font color="Blue">ROW(G14)-ROW($G$5)<icount,if(<font color="Red">AND(I14>0,J13>0),G13,IF(AND(I13>0,J14>0),G13+I13-J14,IF(AND(J14>0,J13>0),G13-J14,G13+I13))ROW(G14)-ROW($G$5)=ICount,0,""</icount,if(
H14=IF(OR(ROW(H14)-ROW($H$5)=1,ROW(H14)-ROW($H$5)=ICount),F14,IF(AND(ROW(H14)-ROW($H$5)>1,ROW(H14)-ROW($H$5)<icount< font="">),0,""</icount<>))
I14=IF(<font color="Blue">ROW(I14)-ROW($I$5)<icount,if(<font color="Red">F14>0,F14,0ROW(I14)-ROW($I$5)=ICount,0,""</icount,if(
J14=IF(<font color="Blue">ROW(J14)-ROW($J$5)<icount,if(<font color="Red">F14>0,0,-F14ROW(J14)-ROW($J$5)=ICount,0,""</icount,if(
K14=IF(F14<>0,ROW(K14)-ROW($K$5),"")
G15=IF(<font color="Blue">ROW(G15)-ROW($G$5)<icount,if(<font color="Red">AND(I15>0,J14>0),G14,IF(AND(I14>0,J15>0),G14+I14-J15,IF(AND(J15>0,J14>0),G14-J15,G14+I14))ROW(G15)-ROW($G$5)=ICount,0,""</icount,if(
H15=IF(OR(ROW(H15)-ROW($H$5)=1,ROW(H15)-ROW($H$5)=ICount),F15,IF(AND(ROW(H15)-ROW($H$5)>1,ROW(H15)-ROW($H$5)<icount< font="">),0,""</icount<>))
I15=IF(<font color="Blue">ROW(I15)-ROW($I$5)<icount,if(<font color="Red">F15>0,F15,0ROW(I15)-ROW($I$5)=ICount,0,""</icount,if(
J15=IF(<font color="Blue">ROW(J15)-ROW($J$5)<icount,if(<font color="Red">F15>0,0,-F15ROW(J15)-ROW($J$5)=ICount,0,""</icount,if(
K15=IF(F15<>0,ROW(K15)-ROW($K$5),"")
G16=IF(<font color="Blue">ROW(G16)-ROW($G$5)<icount,if(<font color="Red">AND(I16>0,J15>0),G15,IF(AND(I15>0,J16>0),G15+I15-J16,IF(AND(J16>0,J15>0),G15-J16,G15+I15))ROW(G16)-ROW($G$5)=ICount,0,""</icount,if(
H16=IF(OR(ROW(H16)-ROW($H$5)=1,ROW(H16)-ROW($H$5)=ICount),F16,IF(AND(ROW(H16)-ROW($H$5)>1,ROW(H16)-ROW($H$5)<icount< font="">),0,""</icount<>))
I16=IF(<font color="Blue">ROW(I16)-ROW($I$5)<icount,if(<font color="Red">F16>0,F16,0ROW(I16)-ROW($I$5)=ICount,0,""</icount,if(
J16=IF(<font color="Blue">ROW(J16)-ROW($J$5)<icount,if(<font color="Red">F16>0,0,-F16ROW(J16)-ROW($J$5)=ICount,0,""</icount,if(
K16=IF(F16<>0,ROW(K16)-ROW($K$5),"")
G17=IF(<font color="Blue">ROW(G17)-ROW($G$5)<icount,if(<font color="Red">AND(I17>0,J16>0),G16,IF(AND(I16>0,J17>0),G16+I16-J17,IF(AND(J17>0,J16>0),G16-J17,G16+I16))ROW(G17)-ROW($G$5)=ICount,0,""</icount,if(
H17=IF(OR(ROW(H17)-ROW($H$5)=1,ROW(H17)-ROW($H$5)=ICount),F17,IF(AND(ROW(H17)-ROW($H$5)>1,ROW(H17)-ROW($H$5)<icount< font="">),0,""</icount<>))
I17=IF(<font color="Blue">ROW(I17)-ROW($I$5)<icount,if(<font color="Red">F17>0,F17,0ROW(I17)-ROW($I$5)=ICount,0,""</icount,if(
J17=IF(<font color="Blue">ROW(J17)-ROW($J$5)<icount,if(<font color="Red">F17>0,0,-F17ROW(J17)-ROW($J$5)=ICount,0,""</icount,if(
K17=IF(F17<>0,ROW(K17)-ROW($K$5),"")
G18=IF(<font color="Blue">ROW(G18)-ROW($G$5)<icount,if(<font color="Red">AND(I18>0,J17>0),G17,IF(AND(I17>0,J18>0),G17+I17-J18,IF(AND(J18>0,J17>0),G17-J18,G17+I17))ROW(G18)-ROW($G$5)=ICount,0,""</icount,if(
H18=IF(OR(ROW(H18)-ROW($H$5)=1,ROW(H18)-ROW($H$5)=ICount),F18,IF(AND(ROW(H18)-ROW($H$5)>1,ROW(H18)-ROW($H$5)<icount< font="">),0,""</icount<>))
I18=IF(<font color="Blue">ROW(I18)-ROW($I$5)<icount,if(<font color="Red">F18>0,F18,0ROW(I18)-ROW($I$5)=ICount,0,""</icount,if(
J18=IF(<font color="Blue">ROW(J18)-ROW($J$5)<icount,if(<font color="Red">F18>0,0,-F18ROW(J18)-ROW($J$5)=ICount,0,""</icount,if(
K18=IF(F18<>0,ROW(K18)-ROW($K$5),"")
G19=IF(<font color="Blue">ROW(G19)-ROW($G$5)<icount,if(<font color="Red">AND(I19>0,J18>0),G18,IF(AND(I18>0,J19>0),G18+I18-J19,IF(AND(J19>0,J18>0),G18-J19,G18+I18))ROW(G19)-ROW($G$5)=ICount,0,""</icount,if(
H19=IF(OR(ROW(H19)-ROW($H$5)=1,ROW(H19)-ROW($H$5)=ICount),F19,IF(AND(ROW(H19)-ROW($H$5)>1,ROW(H19)-ROW($H$5)<icount< font="">),0,""</icount<>))
I19=IF(<font color="Blue">ROW(I19)-ROW($I$5)<icount,if(<font color="Red">F19>0,F19,0ROW(I19)-ROW($I$5)=ICount,0,""</icount,if(
J19=IF(<font color="Blue">ROW(J19)-ROW($J$5)<icount,if(<font color="Red">F19>0,0,-F19ROW(J19)-ROW($J$5)=ICount,0,""</icount,if(
K19=IF(F19<>0,ROW(K19)-ROW($K$5),"")
G20=IF(<font color="Blue">ROW(G20)-ROW($G$5)<icount,if(<font color="Red">AND(I20>0,J19>0),G19,IF(AND(I19>0,J20>0),G19+I19-J20,IF(AND(J20>0,J19>0),G19-J20,G19+I19))ROW(G20)-ROW($G$5)=ICount,0,""</icount,if(
H20=IF(OR(ROW(H20)-ROW($H$5)=1,ROW(H20)-ROW($H$5)=ICount),F20,IF(AND(ROW(H20)-ROW($H$5)>1,ROW(H20)-ROW($H$5)<icount< font="">),0,""</icount<>))
I20=IF(<font color="Blue">ROW(I20)-ROW($I$5)<icount,if(<font color="Red">F20>0,F20,0ROW(I20)-ROW($I$5)=ICount,0,""</icount,if(
J20=IF(<font color="Blue">ROW(J20)-ROW($J$5)<icount,if(<font color="Red">F20>0,0,-F20ROW(J20)-ROW($J$5)=ICount,0,""</icount,if(
K20=IF(F20<>0,ROW(K20)-ROW($K$5),"")
G21=IF(<font color="Blue">ROW(G21)-ROW($G$5)<icount,if(<font color="Red">AND(I21>0,J20>0),G20,IF(AND(I20>0,J21>0),G20+I20-J21,IF(AND(J21>0,J20>0),G20-J21,G20+I20))ROW(G21)-ROW($G$5)=ICount,0,""</icount,if(
H21=IF(OR(ROW(H21)-ROW($H$5)=1,ROW(H21)-ROW($H$5)=ICount),F21,IF(AND(ROW(H21)-ROW($H$5)>1,ROW(H21)-ROW($H$5)<icount< font="">),0,""</icount<>))
I21=IF(<font color="Blue">ROW(I21)-ROW($I$5)<icount,if(<font color="Red">F21>0,F21,0ROW(I21)-ROW($I$5)=ICount,0,""</icount,if(
J21=IF(<font color="Blue">ROW(J21)-ROW($J$5)<icount,if(<font color="Red">F21>0,0,-F21ROW(J21)-ROW($J$5)=ICount,0,""</icount,if(
K21=IF(F21<>0,ROW(K21)-ROW($K$5),"")
G22=IF(<font color="Blue">ROW(G22)-ROW($G$5)<icount,if(<font color="Red">AND(I22>0,J21>0),G21,IF(AND(I21>0,J22>0),G21+I21-J22,IF(AND(J22>0,J21>0),G21-J22,G21+I21))ROW(G22)-ROW($G$5)=ICount,0,""</icount,if(
H22=IF(OR(ROW(H22)-ROW($H$5)=1,ROW(H22)-ROW($H$5)=ICount),F22,IF(AND(ROW(H22)-ROW($H$5)>1,ROW(H22)-ROW($H$5)<icount< font="">),0,""</icount<>))
I22=IF(<font color="Blue">ROW(I22)-ROW($I$5)<icount,if(<font color="Red">F22>0,F22,0ROW(I22)-ROW($I$5)=ICount,0,""</icount,if(
J22=IF(<font color="Blue">ROW(J22)-ROW($J$5)<icount,if(<font color="Red">F22>0,0,-F22ROW(J22)-ROW($J$5)=ICount,0,""</icount,if(
K22=IF(F22<>0,ROW(K22)-ROW($K$5),"")
G23=IF(<font color="Blue">ROW(G23)-ROW($G$5)<icount,if(<font color="Red">AND(I23>0,J22>0),G22,IF(AND(I22>0,J23>0),G22+I22-J23,IF(AND(J23>0,J22>0),G22-J23,G22+I22))ROW(G23)-ROW($G$5)=ICount,0,""</icount,if(
H23=IF(OR(ROW(H23)-ROW($H$5)=1,ROW(H23)-ROW($H$5)=ICount),F23,IF(AND(ROW(H23)-ROW($H$5)>1,ROW(H23)-ROW($H$5)<icount< font="">),0,""</icount<>))
I23=IF(<font color="Blue">ROW(I23)-ROW($I$5)<icount,if(<font color="Red">F23>0,F23,0ROW(I23)-ROW($I$5)=ICount,0,""</icount,if(
J23=IF(<font color="Blue">ROW(J23)-ROW($J$5)<icount,if(<font color="Red">F23>0,0,-F23ROW(J23)-ROW($J$5)=ICount,0,""</icount,if(
K23=IF(F23<>0,ROW(K23)-ROW($K$5),"")

<tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
Costs_hdr='Cost Comparison'!$C$5
ICount='Cost Comparison'!$C$25

<tbody>
</tbody>

<tbody>
</tbody>



These are all the named ranges that are on this sheet.


Workbook Defined Names
NameRefers To
Additions=OFFSET(Additions_hdr,1,0,ICount,1)
Additions_hdr='Cost Comparison'!$I$5
Base=OFFSET(Base_hdr,1,0,ICount,1)
Base_hdr='Cost Comparison'!$G$5
Budget=OFFSET(Budget_hdr,1,0,ICount,1)
Budget_hdr='Cost Comparison'!$H$5
Costs_hdr='Cost Comparison'!$C$5
Descriptions=OFFSET(Descriptions_hdr,1,0,COUNTA(OFFSET(Descriptions_hdr,1,0,'Cost Comparison'!PCount,1)),1)
Descriptions_hdr='Cost Comparison'!$B$5
Reductions=OFFSET(Reductions_hdr,1,0,ICount,1)
Reductions_hdr='Cost Comparison'!$J$5

<tbody>
</tbody>

<tbody>
</tbody>

Worksheet Defined Names
NameRefers To
'Cost Comparison'!PCount='Cost Comparison'!$C$26
'Cost Comparison'!WaterfallTitle1='Cost Comparison'!$B$2:$C$2
'Cost Comparison'!WaterfallTitle2='Cost Comparison'!$B$3:$C$3

<tbody>
</tbody>

<tbody>
</tbody>


Now for the graph series

Series one Name: ='Cost Comparison'!$G$5
Series one values: ='Waterfall Chart with Automatic 3 to 18 Points.xlsm'!Base

Series two name: ='Cost Comparison'!$H$5
Series two values: ='Waterfall Chart with Automatic 3 to 18 Points.xlsm'!Budget

Series three name: ='Cost Comparison'!$I$5
Series three values: ='Waterfall Chart with Automatic 3 to 18 Points.xlsm'!Additions

Series four name: ='Cost Comparison'!$J$5
Series four values: ='Waterfall Chart with Automatic 3 to 18 Points.xlsm'!Reductions

Horizontal Axis (X) names: ='Waterfall Chart with Automatic 3 to 18 Points.xlsm'!Descriptions

Notice the name of the workbook in the "Values". You will need to change that to the name of your workbook.

You will need to format series one fill to "No Fill" and border color to "No Line".



Here is the code to change the data labels. This one SUB took me most of a day to complete.
Code:
Sub ChartLabelsRefresh()

  Dim Sht As Worksheet
  Dim X As Long
  Dim iCnt As Long
  Dim Y As Long
  Dim idx() As Integer
  Dim R As Range
  Dim Cel As Range
  Dim Chrt As ChartObject
  Dim SC As Series
  Dim aChrt As ChartObject
  Dim PrevCel As Range
  
  
  Set Sht = ActiveSheet
  Sht.Unprotect
  Set PrevCel = ActiveCell
  
  iCnt = Range("ICount").Value                          'Count of points on Waterfall Chart
  ReDim idx(1 To iCnt)                                  'Index to store which series a point belongs
  Set Cel = Range("Costs_hdr")
  Set R = Range(Cel.Offset(1, 0), Cel.Offset(iCnt, 0))
  X = 0
  For Each Cel In R                                      'Get each value and determine which series
    X = X + 1
    If X = 1 Or X = iCnt Then                            'First point or Last Point belong to series 2
      idx(X) = 2
    ElseIf Cel.Value < 0 Then                            'Negative points belong to series 4
      idx(X) = 4
    ElseIf Cel.Value > 0 Then                            'Positive points belong to series 3
      idx(X) = 3
    End If
  Next Cel
  
  
  Sht.ChartObjects("Chart 3").Activate
  For X = 2 To 4
    Set SC = ActiveChart.SeriesCollection(X)
    If SC.HasDataLabels = True Then
      SC.DataLabels.Delete
    End If
  Next X
  
  'Add datalabels for each series
  'Delete series datalabels from non-related points
  'Use idx() to check wich series each point belongs
  For Y = 2 To 4
    Set SC = ActiveChart.SeriesCollection(Y)
    SC.ApplyDataLabels ShowValue:=True
    SC.HasDataLabels = True
    
    With SC.DataLabels.Format.TextFrame2.TextRange.Font.Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(0, 0, 0)
      .Transparency = 0
      .Solid
    End With
    
    For X = 1 To iCnt
      If idx(X) <> Y Then
        SC.Points(X).DataLabel.Delete
      End If
    Next X
  Next Y
  
  Select Case iCnt
    Case Is > 16
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 7
    Case Is > 13
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 9
    Case Is > 10
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 11
    Case Is > 7
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 12
    Case Is <= 7
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 13
  End Select
  
  
  With Sht
    .EnableOutlining = True
    .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True _
      , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
      AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, AllowFiltering _
    :=True, userInterfaceOnly:=True
  End With
  PrevCel.Select
    
    
    
End Sub
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Watch MrExcel Video

Forum statistics

Threads
1,114,280
Messages
5,546,948
Members
410,764
Latest member
Dedeke
Top