A waterfall chart is used to represent a set of figures when they all impact the same derived number. A waterfall chart helps to link the individual values to a whole. A profit and loss statement is a good example where the magnitude of each figure has an impact on a derived number (which happens to be Profit in this case).
A colleague of mine recently had a budgeting exercise assigned to him where he had to project the P&L figures for the next year and then show them as a waterfall chart. The existing template, to start with, had probably around 7 or 8 series, each being used to generate a set of graphs. (As you may have already guessed, a waterfall chart in excel uses the native stack graphs.) Now we did not particularly like what we were given and we did a bit of googling to see if the wheel had been invented before……it had been and on numerous occasions but we were looking for an actual example in excel and to our disappointment, that was something we did not find. What we did find, however were a large number of ‘part-waterfall-part-bar’ charts which would work well with a specific set of values but devolve into normal bar charts (or sometimes even simply break down) as soon as the values were moved around too much.
One such example is shown here.
So what is a dimwit to do……well……we made our own version of the waterfall chart.
To create the waterfall chart, we started off with a dummy set of numbers for the P&L.
We then added four additional set of formulas – one each for the actual number, the padding (or offset), for crossover (when the numbers move across the X-axis and finally the reference point – which I called the datum.). The actual waterfall chart actually uses only the first three as a series while the datum is just for the developer to keep track. (At the cost of added complexity, you can actually dissolve the datum in to the first three values and completely drop it.)
You may actually skip the rest if all you need to grab is a quick copy of the waterfall chart in excel.
Four distinct components of the Waterfall Chart:
1. The Padding: Since each of bars in a waterfall chart do not begin at 0, you have to offset them (elevate or depress) by a certain margin. How much to offset is determined by the datum.
2. The Plot: The value to plot. The trick here is to ensure that if the actual value to plot is -ve but it still does not go below zero (because the previous figure was way too positive), the figure used for actually plotting the bar has to be above the axis but moving southwards. And vice-versa. In such cases, the padding needs to be adjusted accordingly.
3. The crossover: Sometimes we will encounter a situation where the plot value is such that it moves across the X-Axis. For example, the first figure was 1000 and the second figure (the one we need to plot) is -2000. In such a case, some part of the bar will be above X-axis and some below. The workaround is to use the plot value (in Point 2) to plot half of the actual value and use the crossover value to plot the remaining portion. This is a crucial test for a waterfall chart – the cross over series should adjust automatically when the next value causes the movement across the axis, from the lower half to the upper or vice-versa.
4. The datum: The datum is the level which becomes the starting point for the next value to being from. The trick here is to make sure that when two successive values are 100 and 10, the third bar has to be from 110. But if the values are 100 and -10, the third bar has to begin from 90.
The formulas that we put for each one of the above components were:
Once you have the set of the four formulas, just extend them to the entire series that you want to plot. This now becomes the source data for the waterfall chart.
What remains now if to simply pick up the first three- the padding, the plot and the crossover – and insert a stacked graph in Excel. The resulting will be something similar to this one:
Remove the chart clutter. Double click on the series that represents padding and make it transparent and VIOLA…..your waterfall chart is ready to flow !!!
“[W]e did a bit of googling to see if the wheel had been invented before…”
I’ve had a Waterfall Chart Tutorial available for years:
http://peltiertech.com/Excel/Charts/Waterfall.html
This is the first link returned if you Google for excel waterfall charts.
A second tutorial covers cases where the bars cross the horizontal axis:
http://peltiertech.com/Excel/Charts/waterfallcrossing.html
I’ve even built a utility to make waterfall charts with a click of a button, with both free and professional versions:
October 8th, 2008 at 7:57 amhttp://peltiertech.com/Utility/WaterfallUtility.html
John :: I agree with you a 101%. Your tutorials regarding the creation of waterfall charts are very informative. Gurus like you, John Walkenbach, Debra Dalgleish, Charley Kyd, Tushar Mehta remain an inspiration.
However as I mentioned, “….we were looking for an actual example in excel and to our disappointment, that was something we did not find….”. To be precise to a fault, the first and the third links do appear as the first two links while searching for ‘waterfall chart’. However they fall short of presenting all the steps regarding the creation of a complete waterfall chart. The second link, which you’ve mentioned, does show the complete step by step process, does not appear in the search . You’d agree that some of the other links that result from such a search, do point to other attempts that fall short of expectations.
What I’ve tried to show here is to simplify the process of creation of such a chart by using just 4 simple yet consistent formulae that can simply be extended across nth rows at will and which also eliminate the guesswork involved in knowing which cells to keeping blank and which ones to populate. Whether that’s an improvement over earlier efforts……I leave that open for discussion.
Whatever it is, this certainly is NOT an attempt at presenting the concept as a new discovery. Regards,
October 8th, 2008 at 2:15 pmVery informative. Thanks for the stepwise walkthru…helped a ton.
June 13th, 2009 at 3:04 pm@Matthew – Glad I could be of help and welcome to da TaB
June 13th, 2009 at 11:47 pmthanks for the descriptive tips on waterfall charts..truly inspirational
July 7th, 2009 at 3:57 amThanks biman. Welcome to da TaB and glad this was of help
July 7th, 2009 at 5:07 amVery nice Ajay
For some reason, I didn’t see the free version of JP’s Utility on the page.
This is certainly something I can easily use, with the benefit that the explanation is pretty clear.
Big Ups!
October 20th, 2009 at 2:37 pmlernr – Thanks and Welcome to da TaB !
October 20th, 2009 at 11:46 pmHow can i adjust through VBA code the width of the columns?
November 26th, 2009 at 10:43 amIs it possible?
Thank you!!!
@Federico – You can use the following code to change the width of the columns of the waterfall chart. You may need to hack the code a bit though to suit your requirements.
2
3
4
5
6
For Each grp In ActiveChart.ChartGroups
grp.Overlap = 100
grp.GapWidth = 70
Next grp
End Sub
Regards,
November 26th, 2009 at 12:27 pm@ Ajay: thank you for your kind reply.
November 27th, 2009 at 3:15 amBut I think that i nedd something different cause I need to set for every coloumn the width.
Cause in my case i have two variables one on “x” axis and one on “y” axis and i must set both.
Could you help me in this way?
Thank you in advance
Regards
@Federico – Would it be possible for you to send me a screen shot or a file that I can look at. I did not get the point about the two variables (one on “x” axis and one on “y” axis).
I thought the waterfall chart had only one type of bars -either horizontal (along the x-axis) or vertical (along the y-axis). Help me understand this better.
November 27th, 2009 at 4:52 amRegards,
Sure! how can I send to u?
November 27th, 2009 at 5:15 am@Federico – Send me an email at databison | at | gmail.com with the attachment (the waterfall chart that you are trying to modify) and mention how you would like to finally look like. Let me take a look.
Regards,
November 27th, 2009 at 7:30 amI’ve sent it right now!!!!
Thank you for your reply!
Federico
November 27th, 2009 at 8:08 amYou rock!
January 6th, 2010 at 2:51 pm@Kerri – Thanks. You rock too
January 6th, 2010 at 3:06 pmMy boss sharted his pants when i delivered this beauty… Cheers mate
May 10th, 2010 at 4:09 pmabsolutely brilliant and simple step by step guide. I have looking for this for a long time….thanks
September 5th, 2010 at 9:25 amVery useful, lean a lot
December 17th, 2010 at 9:37 amCheers, saved me heaps of time, Thanks
January 6th, 2011 at 11:58 pmThanks for the information, it was very helpful. I was wondering how to change the color of the columns for the expense items to a different color than the columns for the revenue items?
January 19th, 2011 at 4:52 pmawesome stuff, just replicated in excel and used my own data, this certainly rocks.
i have also been using vba codes tutorials such as pivots. thanks team
February 25th, 2011 at 9:01 amHunuted for this example a coupble of yearsa ago and found nothing but this is exactly what I was looking for thanks
April 4th, 2011 at 6:59 amGreat template for the chart itself, with one big shortcoming. I don’t see how you can include labels on the plots. It won’t show negative plots with a negative label. Youd have to manually edit them as far as I can tell.
June 14th, 2011 at 2:14 pm-
November 2nd, 2011 at 3:25 amThank Ajay. Was trying to do this by myself but kept getting stuck. This really helped clear the basics.
March 12th, 2012 at 7:26 amThanks, very easy to understand
March 14th, 2012 at 12:25 pmFantastic!!! helps a lot.
September 18th, 2012 at 6:02 pmFantastic, exactly what our GM is after. Have not played with the numbers yet. But it is great to be able to utilise the formulas for our own purposes.
Cheers.
January 2nd, 2013 at 9:29 pmYou can reach the same result (with separate colors for positive and negative contribution!) in an easier way with the chart “stock open-high-low-close”:
value = is the value to plot, do not select for the chart!
open = close[-1]
high = close
low = close[-1]
close = value[-1] + value
In the first line value[-1] = 0
That’s it. Try.
April 8th, 2013 at 4:05 amExtremely quick and painless… nice one guys.
October 11th, 2013 at 8:07 amEdo – just tried yours as well. Beautiful in its simplicity.
October 11th, 2013 at 8:20 amThree little tricks w.r.t. Edo’s technique…
1 – Do not bother populating either the High or Low values – you can leave these columns blank;
2 – The Close value should actually be Open + Value;
3 – In Excel 2007/10/13, to reduce the width between the bars – i.e. to achieve the same look as the modified column chart in the original example – create the chart, change the chart type to Clustered Column, amend the gap between the columns as desired and change back to the Open-High-Low-Close format.
October 11th, 2013 at 8:32 amSuperb – thank you!
February 20th, 2014 at 10:38 amHello great template. Quick question how can I show the plotted data (one series) in two different colour, like one color for positive and another color for negative..
March 20th, 2014 at 7:15 pmLooking forward to hear from u.
This does not correct label the datapoints. Negative values are shown as positive.
April 10th, 2014 at 4:24 pmDear Tomtom,
May 9th, 2014 at 6:05 amTo get automatic coloring of both positive and negative effects, you can try this free template: http://www.waterfall-chart.com !
Hope it helps
Jack