Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

negative waterfall chart

Hello:

Anyone have any ideas how to make a waterfall chart that can break below the
Y-axis? Jon Peltier has a good example of an automatic waterfall chart, but
I can't figure out how to make it go negative.

Thanks!


Post your answer or comment

comments powered by Disqus
Hello,

I need VBA code to draw waterfall charts.

I have been reading around (especially http://peltiertech.com/, this forum and http://www.mrexcel.com) but could not find what I need.

I have written a macro (which need some polishing, especially visually) but I cannot find how to make the bottom part of the stacked columns chart invisible.

I have tried recording the macro while doing it, but it did not show up.

I need this code to integrate it into a larger routine which will generate several waterfall charts automatically (at least that's what I would like).

I am attaching the code I have so far (so you can see it in action), which is almost there, the only missing bit is the transparency for the bottom part of the stacks. I also copy it below.

Could anyone help? It would be MUCH appreciated

Thank you in advance


	VB:
	
 Draw() 
     
    Dim SheetName As String 
    Dim Data1Col As Integer, Data2Col As Integer, LabelsCol As Integer 
    Dim FirstRow As Long, LastRow As Long 
     
    LabelsCol = 1 
    Data1Col = 2 
    Data2Col = 3 
    FirstRow = 3 
    LastRow = 23 
     
    SheetName = "Global" 
     
    DrawWaterfallChart SheetName, Data1Col, Data2Col, LabelsCol, FirstRow, LastRow 
     
End Sub 
_______________________________________________________________________________ 
 
Public Sub DrawWaterfallChart(SheetName As String, Data1Col As Integer, Data2Col As Integer, LabelsCol As Integer, FirstRow
As Long, LastRow As Long) 
     
    Dim rng1 As Range, rng2 As Range, rnglabels As Range, rngGlobal As Range 
     
    Dim myChtObj As ChartObject 
    Dim iColumn As Long 
     
    Dim plus() As Double 
    Dim minus() As Double 
    Dim basement() As Double 
    Dim labels() As String 
    Dim Height As Long 
    Height = LastRow - FirstRow + 1 
    Dim Initial As Double 
     
     'Let's put the data in arrays because in the final application the data will be coming from arrays
     
    Redim plus(1 To Height) 
    Redim minus(1 To Height) 
    Redim basement(1 To Height) 
    Redim labels(1 To Height) 
     
    Dim Row As Long, Col As Integer 
     
    For Row = 1 To Height 
        If Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col) > Sheets(SheetName).Cells(FirstRow + Row - 1, Data1Col) Then

            plus(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col) - Sheets(SheetName).Cells(FirstRow + Row - 1,
Data1Col) 
            minus(Row) = 0 
        Else 
            plus(Row) = 0 
            minus(Row) = -Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col) + Sheets(SheetName).Cells(FirstRow + Row - 1,
Data1Col) 
        End If 
    Next Row 
     
    For Row = 1 To Height 
        basement(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, Data1Col) + plus(Row) - minus(Row) 
        labels(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, LabelsCol) 
    Next Row 
     
    Set myChtObj = Sheets(SheetName).ChartObjects.Add(Left:=250, Width:=375, Top:=75, Height:=225) 
     
    Dim Invisible As Series 
     
    Dim Positive As Series 
     
    Dim Negative As Series 
     
     ' Add the chart
    With myChtObj.Chart 
        .ChartArea.Fill.Visible = False 
        .PlotArea.Format.Fill.Solid 
        .PlotArea.Format.Fill.Transparency = 1 
        .HasTitle = True 
        .ChartTitle.Text = "Title" 
         
        .Axes(xlCategory, xlPrimary).HasTitle = False 
        .Axes(xlValue, xlPrimary).HasTitle = True 
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Units" 
         
        With .Legend 
            .Top = 57 
            .Height = 248 
            .Left = 728 
            .Width = 155 
        End With 
         
        With .PlotArea 
            .Top = 47 
            .Height = 284 
            .Left = 30 
            .Width = 687 
        End With 
         
         ' Make Column Stacked chart
        .ChartType = xlColumnStacked 
         ' Add series
        Set Invisible = .SeriesCollection.NewSeries 
        With Invisible 
            .Values = basement 
            .XValues = labels 
            .Name = "Base" 
            With .Border 
                .ColorIndex = 13 
                .Weight = xlMedium 
                .LineStyle = xlNone 
            End With 
             
             'Marker controls
             '        .MarkerBackgroundColorIndex = xlNone
            .Format.Line.Transparency = 0 
            .MarkerStyle = xlNone 
        End With 
         
        Set Positive = .SeriesCollection.NewSeries 
        With Positive 
            .Values = plus 
            .XValues = labels 
            .Name = "Plus" 
        End With 
         
        Set Negative = .SeriesCollection.NewSeries 
        With Negative 
            .Values = minus 
            .XValues = labels 
            .Name = "Minus" 
        End With 
         
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Dear experts

I saw how to make a waterfall chart on this web-site

http://peltiertech.com/Excel/Charts/Waterfall.html

I had one small problem which hopefully someone can clarify for me

in that illustration in Col D we have the minus column

now I want the values to be shown in the chart against the respective col...therefore the values in the minus col should come as for eg (3000)

now since the values are positive in the chart they are displayed as positive numbers on the chart

consequently if the numbers are changed to negative in the sheet then the chart gets spoiled as those columns get plotted below the X-axis

how is it possible that one may display the values on the chart as negative numbers (for Col D only) and still ensure that the columns get plotted above the X-axis

Thanx in advance

and oh yes a happy happy new year to all of you

TIA

opan

Hi,

Make you Financials transparant!

Show your variances in income in a waterfall chart. Where do we come from, where do we stand.

By creating two series colours can be influenced ( red is negative, blue is positive )

Very simple approach yet very effective!
Graphs speak more than figures

Greetings,

Jack

Hi ,

I have trying to adaprt a waterfall chart i found on HTML Code: 
 but I am having a few problems...

I was expecting to see negative bar charts however they all start from zero - can anyone see what I am doing wrong?

Hi Everyone,

I am building this waterfall chart. I'd like to put conditional color formating to change the vertical bar automatically e.g. if it is positive, the bar color is green and if it is negative the bar color is red.

Can anyone help me

Thanks

Henry

Hi All,
I have modified a friend's waterfall chart so that it can basically be used with any data positive or negative.
The main problem im having is data labels - I want them in the middle of the bars.
If I change the data, especially when it crosses the 0 line, my data labels all require manual manipulation.
Can anyone think of a good way to have the data labels centred in the bars?
I have attached a spreadsheet to have a play with.
Many thanks,
Nick

Hello,

I need VBA code to draw waterfall charts.

I have been reading around (especially http://peltiertech.com/, this forum and http://www.ozgrid.com) but could not find what I need.

I have written a macro (which need some polishing, especially visually) but I cannot find how to make the bottom part of the stacked columns chart invisible.

I have tried recording the macro while doing it, but it did not show up.

I need this code to integrate it into a larger routine which will generate several waterfall charts automatically (at least that's what I would like).

I am attaching the code I have so far (so you can see it in action), which is almost there, the only missing bit is the transparency for the bottom stacks. I also copy the code below.

Could anyone help? It would be MUCH appreciated

Thank you in advance

Public Sub Draw()

Dim SheetName As String
Dim Data1Col As Integer, Data2Col As Integer, LabelsCol As Integer
Dim FirstRow As Long, LastRow As Long

LabelsCol = 1
Data1Col = 2
Data2Col = 3
FirstRow = 3
LastRow = 23

SheetName = "Global"

DrawWaterfallChart SheetName, Data1Col, Data2Col, LabelsCol, FirstRow, LastRow

End Sub
_______________________________________________________________________________

Public Sub DrawWaterfallChart(SheetName As String, Data1Col As Integer, Data2Col As Integer, LabelsCol As Integer, FirstRow
As Long, LastRow As Long)

Dim rng1 As Range, rng2 As Range, rnglabels As Range, rngGlobal As Range

Dim myChtObj As ChartObject
Dim iColumn As Long

Dim plus() As Double
Dim minus() As Double
Dim basement() As Double
Dim labels() As String
Dim Height As Long
Height = LastRow - FirstRow + 1
Dim Initial As Double

'Let's put the data in arrays because in the final application the data will be coming from arrays

ReDim plus(1 To Height)
ReDim minus(1 To Height)
ReDim basement(1 To Height)
ReDim labels(1 To Height)

Dim Row As Long, Col As Integer

For Row = 1 To Height
    If Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col) > Sheets(SheetName).Cells(FirstRow + Row - 1, Data1Col) Then
        plus(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col) - Sheets(SheetName).Cells(FirstRow + Row - 1,
Data1Col)
        minus(Row) = 0
    Else
        plus(Row) = 0
        minus(Row) = -Sheets(SheetName).Cells(FirstRow + Row - 1, Data2Col) + Sheets(SheetName).Cells(FirstRow + Row - 1,
Data1Col)
    End If
Next Row

For Row = 1 To Height
    basement(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, Data1Col) + plus(Row) - minus(Row)
    labels(Row) = Sheets(SheetName).Cells(FirstRow + Row - 1, LabelsCol)
Next Row

Set myChtObj = Sheets(SheetName).ChartObjects.Add(Left:=250, Width:=375, Top:=75, Height:=225)

Dim Invisible As Series
    
Dim Positive As Series
    
Dim Negative As Series

' Add the chart
With myChtObj.Chart
    .ChartArea.Fill.Visible = False
    .PlotArea.Format.Fill.Solid
    .PlotArea.Format.Fill.Transparency = 1
    .HasTitle = True
    .ChartTitle.Text = "Title"
        
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Units"

    With .Legend
        .Top = 57
        .Height = 248
        .Left = 728
        .Width = 155
    End With

    With .PlotArea
        .Top = 47
        .Height = 284
        .Left = 30
        .Width = 687
    End With

    ' Make Column Stacked chart
    .ChartType = xlColumnStacked
    ' Add series
    Set Invisible = .SeriesCollection.NewSeries
    With Invisible
        .Values = basement
        .XValues = labels
        .Name = "Base"
        With .Border
            .ColorIndex = 13
            .Weight = xlMedium
            .LineStyle = xlNone
        End With

        'Marker controls
'        .MarkerBackgroundColorIndex = xlNone
        .Format.Line.Transparency = 0
        .MarkerStyle = xlNone
    End With
    
    Set Positive = .SeriesCollection.NewSeries
    With Positive
        .Values = plus
        .XValues = labels
        .Name = "Plus"
    End With
            
    Set Negative = .SeriesCollection.NewSeries
    With Negative
        .Values = minus
        .XValues = labels
        .Name = "Minus"
    End With
    
End With

End Sub


Hi There,

I am trying to get the famous waterfall chart to work for my data.
For 2 days now I have been reading and creating dummy series
....
http://www.tushar-mehta.com/excel/ch...fall/index.htm
http://peltiertech.com/Excel/Charts/Waterfall.html
http://peltiertech.com/Excel/Charts/Waterfall2.html
http://peltiertech.com/Excel/Charts/...ies.html#Falls
http://peltiertech.com/Excel/Charts/...ies.html#Fall2
http://peltiertech.com/Excel/Charts/...eAndBelow.html

Jon, Tushar, Frank, ...
Can you please put me on track?
Amount
Profit 2001 -3,81
Trade P 5,71
Prime Costs 1,20
Conversion Costs -0,71
A&P 1,41
Other F&S 2,72
G&A -1,12
volume effect -15,27
price effect 11,35
Profit 2004 1,49

Starting with a negative value and crossing the x-axis is the killer... :o(
Cheers Sige

Hi,

Is anyone able to offer advice on how to construct a waterfall chart with variable column widths?

Regards,
Chris

Good morning,

I am needing to reflect some of my work in a waterfall chart, but do not have that as an option in my list of charts. Is there a down load I can get?

Thanks for taking time to help!

I'm working on implementing a waterfall chart into some month end cash flows charts and am having trouble with the scale. I have an inflow that is infinitely bigger than all the other inflows and am wondering how to "hide" a portion of the graph so all the inflows show up on the graph. Just for examples sake let's say, most of my inflows are $1000 but one is $1,000,000. Is there a way to insert a break in the graph to cut out $300,000-$700,000?

I am using Microsoft Excel 2003

I'll try my best to explain. Currently I have a list of tasks in excel. Each task has a number that correlates to a particular risk associated with that task and the estimated completion date of the task. When a task is completed the risk lowers to the planned 'number'.

....Mitigation Plan..........Date ......................Plan
1) Risk identified...........December-04..........0.9
2) Action A....................February-05............0.7
3) Action B....................March-05 ................0.6
4) Action C....................April-05...................0.5
5) Action D....................June-05...................0.2

When Action A completes - the risk decreases from .9 to .7. I have been trying to create a macro that will read in the task, dates and plan and have been trying to represent those in a waterfall chart using a macro instead of drawing it(which is starting to be a pain). I am not having much success. My overall goal is to have a template for someone to use and be able to enter the tasks, dates and risk plan and to be able to display a chart with the description of task below the chart. If anyone has any advice it would be much appreciated.

Are there any add-ins available to make waterfall charts?
Thanks!

Hi,

I want to format series 2 of my waterfall chart with the following.

If the next data point is greater than the previous, then fill green, if less than previous, fill red, if the same then fill black.

Is there a code for this please?

Thanks

I am looking for software tools and/or techniques for creating cascading and
waterfall charts . I'm guessing that regular Excel does not do this. What's
the best answer?

Hi,

i found this forum searching for help with a task i got today in the morning. Maybe one of you can help me out...

I have to create a waterfall-chart with a variable column width.

Means: Thy Y-Axis represents total costs (high) for each categoy, the x-Axis represents the cost per order (width).

[name] [total cost = height(stacked as waterfall)] [cost per order = width]
A 35 2
B 20 5
C 15 3
D 10 5

Means for example B has the width 5 and the upper bound of 55 and begins at 35...

I found some tutorials for matrix-charts with variable column widht but none of them included waterfall function...

Can anybody help me?
Thank you very much.

Best greetings
Haubner

Hi folks,

Does anyone know how to draw the horizontal lines to connect the first
ending point to second starting point in waterfall chart? I asked this
question before and one of the experts showed me the site where I could
download the sample. I am not that good in excel vba coding. Therefore, I
can’t fully understand that. Could someone show me the code to draw the
horizontal lines (only) in waterfall chart.

Thanks in advance.

Tim.

Hi folks,

I have a Waterfall Chart in my spreadsheet. I need to disable the resize
function of the chart. Does anyone can show me the code (way) to disable
resize the chart?

Thanks in advance.

Tim.

Hi folks,

I need a help on my waterfall chart. I want to show both percentage and
value on my waterfall chart and move the value to the top of the bars (and
keep on the top of bars) instead of in the middle of the bars. Could anyone
show me how to do it? Any help will be deeply appreciated.

Thanks in advance.

Tim

Using EXCEL XP and I do not find a waterfall chart.
--
TLC

Is there a way to create stacked bar charts or waterfall charts with negative values?

So lets say instead of starting at 100 and moving up and down to an end point of 157,
we start with -100 and work our way to an end point of -24.

Hi, I need help with a stacked bar chart with negative figures.

I can't give you specific details on what it's for, but the scenario is something like this

We are given a budget to work around. Thus, we allocated the budget around as we saw fit.(Hypothetically, let's say category 1 uses $1000, category 2 uses $2000, and category 3 uses $3000).

However, we found out we used more in some areas, and less in others (Hypothetically, category 1 uses $1250, category 2 uses $1500, and category 3 uses $3500).

My boss wants me to find out the discrpency between the amount originally planned and the amount that is actually been used, broken down for each individual section (Which mean that, hypothetically, category 1 has +$250, category 2 has -$500, and category 3 has +$500).

Now, he wants me to combine all the data into a single chart. For those with purely positive numbers, it is easy enough, but for those with negative numbers, the graph didn't work out how I imagined it to be.

My plan was to put the original planned amount and the discrepency in the primary axis and the actual amount used in the secondary axis(with an invisible fill but a thick border to indicate the amount actually used). Thus, if the actual amount is more than the planned amount, it would appear such that the border envelops both the planned amount and the discrepency, but if the actual amount is less than the planned amount, the border would only cover a portion of the planned amount, with the discrepency being tacked on where the border left off.

Is there any way to create the chart in the way that I had imagined? If not, is there a system that could give me the same effect? I tried looking at a waterfall chart, but it appears that I need a lot of graphs, one for each different category, and my boss does not want that.

Any help would be appreciated. Thanks.

Also, I apologise if this section is not where I should post ths question.

Hello. I am really new with VBA and I am trying to figure something out. I´ve done a waterfall chart and I need a macro to do the following things with the source data (in order):

1.- Sort the data (Rows) between PROY an PPTO (PROY and PPTO values NOT included) in and ascending order (Based on column "Varia" value).

-------Proy ---- Acum. ------Varia
PROY-- 670
ONE --------- ------ 650-------- 20
TWO --------- ---- 650 ---------- 70
THREE --------- --- 293 -------- 427
FOUR --------- --- 252 ------- -41
PPTO ----------------- 252

2.- Once that is done, and I don´t know if this is posible, I need to colour the scaterred columns (only the ones in the middle of the chart - one, two , three, four-, not PROY and PPTO) in the chart according to their value (Red if negative and green if positive).

Thanks in advance for your help.

Cheers

PD: Sorry about my english :P

Simple question.
I have a Excel Charts (waterfall graph) which has let us say 10 items in the rows. the first one represents the intial and the last one represents finish. If I want to delete one item in between the first and last, the finish item vanishes. For example I have the following series and I want to delete item G from it. The Finish item vanishes from the graph

Intial 200
A 100 100
B 70 30
C 90 20
D 50 40
G 40 20
Finish 30

Please help....


No luck finding an answer? You could always try Google.