 Excel Version

 2013
Awhile ago I was working for a company managing their budgets. My boss knew I was good with Excel and sent me a copy of a waterfall chart that was created by another person no longer employed by them. Apparently a few people tried to make changes to the chart to reflect the current budget and failed. I thought, because I had created thousands of charts before this, that I was going to return the update in a matter of minutes. I was wrong. I bashed the numbers into place after looking at the monster from every angle. It didn't seem intuitive. Over the next few weeks I was asked to make more changes; each change took me up to 30 minutes based on the amount of changes. It wasn't too hard to make value changes, but it was hard to add or subtract the number of items. I ended up with this. It can handle anywhere from 3 to 18 items. The chart ranges are dynamic based on the count of Descriptions. The macro makes all of the data label changes. It also reduces the font size depending on the number of items.
This goes in the SHEET level module area. It's for the button that updates the chart
Paste this into a standard module.
Dynamic Waterfall Chart
I'll try to keep this link available as long as possible.
Book1  

A  B  C  D  E  F  G  H  I  J  K  
2  Waterfall Chart Example  
3  Chart Title 2  Series1  Series2  Series3  Series4  
4  Waterfall Graph Data (DO NOT EDIT)  
5  Description  Costs  Costs  Base  Budget  Additions  Reductions  Point  
6  Previous Cost  81.2  81.2    81.2      1  
7  NonRecurring Scope  (39.8)  (39.8)  41.4      39.8  2  
8  Escalation  3.7  3.7  41.4    3.7    3  
9  New NonRecurring Scope  25.6  25.6  45.1    25.6    4  
10  New Recurring Costs  0.3  0.3  70.7    0.3    5  
11  Reduction 2  (3.0)  (3.0)  68.0      3.0  6  
12  Reduction 3  (6.0)  (6.0)  62.0      6.0  7  
13  Reduction 4  (9.0)  (9.0)  53.0      9.0  8  
14  Addition 4  2.0  2.0  53.0    2.0    9  
15  Addition 5  4.0  4.0  55.0    4.0    10  
16  Contingency  6.0  6.0  59.0    6.0    11  
17  Final  65.0  65.0    65.0      12  
18  
19  
20  
21  
22  
23  
24  Check (Should = 0):  0.0  
Cost Comparison 
Cell Formulas  

Range  Formula  
F6, F7:F23  F6  =OFFSET(Costs_hdr,ROW(F6)ROW($F$5),0) 
H6  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),0,"")) 
J6, J7:J23  J6  =IF(ROW(J6)ROW($J$5)<ICount,IF(F6>0,0,F6),IF(ROW(J6)ROW($J$5)=ICount,0,"")) 
K6, K7:K23  K6  =IF(F6<>0,ROW(K6)ROW($K$5),"") 
G7  G7  =IF(J7>0,H6J7,H6) 
H7:H23  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),0,"")) 
I7:I23  I7  =IF(ROW(I7)ROW($I$5)<ICount,IF(F7>0,F7,0),IF(ROW(I7)ROW($I$5)=ICount,0,"")) 
G8:G23  G8  =IF(ROW(G8)ROW($G$5)<ICount,IF(AND(I8>0,J7>0),G7,IF(AND(I7>0,J8>0),G7+I7J8,IF(AND(J8>0,J7>0),G7J8,G7+I7))),IF(ROW(G8)ROW($G$5)=ICount,0,"")) 
C17  C17  =SUM(C6:C16) 
C24  C24  =SUM(OFFSET(Costs_hdr,1,0,ICount1,1))OFFSET(Costs_hdr,ICount,0) 
Named Ranges  

Name  Refers To  Cells 
Additions  =OFFSET(Additions_hdr,1,0,ICount,1)  I7:I17, G8:G18 
Additions_hdr  ='Cost Comparison'!$I$5  I7:I23 
Base  =OFFSET(Base_hdr,1,0,ICount,1)  G8:G18 
Base_hdr  ='Cost Comparison'!$G$5  G8:G23 
Budget  =OFFSET(Budget_hdr,1,0,ICount,1)  G7, H6:H17 
Budget_hdr  ='Cost Comparison'!$H$5  H6:H23 
Costs_hdr  ='Cost Comparison'!$C$5  F6:F23, C24 
ICount  ='Cost Comparison'!$C$25  J6, H6:H7, I7:J7, G8:J23, C24 
Reductions  =OFFSET(Reductions_hdr,1,0,ICount,1)  J6:J17, G7:G18 
Reductions_hdr  ='Cost Comparison'!$J$5  J6:J23 
Cells with Conditional Formatting  

Cell  Condition  Cell Format  Stop If True  
C6:C23  Expression  =AND(ROW(C6)ROW($C$5)>1,ROW(C6)ROW($C$5)<ICount,C6>0)  text  YES 
C6:C23  Expression  =AND(ROW(C6)ROW($C$5)>1,ROW(C6)ROW($C$5)<ICount,C6<0)  text  NO 
C24  Cell Value  <>0  text  NO 
C24  Cell Value  =0  text  NO 
This goes in the SHEET level module area. It's for the button that updates the chart
VBA Code:
Private Sub RefreshDataLabels_btn_Click()
Call Main.ChartLabelsRefresh
End Sub
Paste this into a standard module.
VBA 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 'I have the sheet protected without a password
'so people don't edit the wrong area
Set PrevCel = ActiveCell 'Store the selected cells
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
'You will have to change this based on your chart name
Sht.ChartObjects("Chart 3").Activate
'Delete all datalabels before adding them back in
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 nonrelated 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
'Resize fonts based on the number of axes
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
'Make the sheet protected again
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
'Select the cells the user had selected
PrevCel.Select
End Sub
Dynamic Waterfall Chart
I'll try to keep this link available as long as possible.