How to create waterfall chart in excel in 2 minutes




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.

wrong-waterfall-chart

So what is a dimwit to do……well……we made our own version of the waterfall chart.
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:
waterfall-chart-formulas

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 !!!

waterfall-chart-template


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jon Peltier wrote:

    “[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:
    http://peltiertech.com/Utility/WaterfallUtility.html

  2. Ajay wrote:

    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,

  3. MatthewR wrote:

    Very informative. Thanks for the stepwise walkthru…helped a ton.

  4. Ajay wrote:

    @Matthew – Glad I could be of help and welcome to da TaB

  5. biman wrote:

    thanks for the descriptive tips on waterfall charts..truly inspirational

  6. Ajay wrote:

    Thanks biman. Welcome to da TaB and glad this was of help :-)

  7. lernr wrote:

    Very 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!

  8. Ajay wrote:

    lernr – Thanks and Welcome to da TaB !

  9. Federico wrote:

    How can i adjust through VBA code the width of the columns?
    Is it possible?
    Thank you!!!

  10. Ajay wrote:

    @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.

    1
    2
    3
    4
    5
    6
    Sub Waterfall_Width_Change()
    For Each grp In ActiveChart.ChartGroups
            grp.Overlap = 100
            grp.GapWidth = 70
    Next grp
    End Sub

    Regards,

  11. Federico wrote:

    @ Ajay: thank you for your kind reply.
    But 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

  12. Ajay wrote:

    @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). :roll: 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.
    Regards,

  13. Federico wrote:

    Sure! how can I send to u?

  14. Ajay wrote:

    @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,

  15. Federico wrote:

    I’ve sent it right now!!!!

    Thank you for your reply!

    Federico

  16. Kerri wrote:

  17. Ajay wrote:

    @Kerri – Thanks. You rock too :-)

  18. Zidane wrote:

    My boss sharted his pants when i delivered this beauty… Cheers mate

  19. Alek wrote:

    absolutely brilliant and simple step by step guide. I have looking for this for a long time….thanks

  20. Albert wrote:

    Very useful, lean a lot

  21. Hugh wrote:

    Cheers, saved me heaps of time, Thanks

  22. Dan wrote:

    Thanks 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?

  23. yd wrote:

    awesome 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

  24. john.caulfield@mousetraining.co.uk wrote:

    Hunuted for this example a coupble of yearsa ago and found nothing but this is exactly what I was looking for thanks

  25. Jay wrote:

    Great 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.

  26. filder wrote:

  27. Pritam wrote:

    Thank Ajay. Was trying to do this by myself but kept getting stuck. This really helped clear the basics.

  28. Jodie Hicken wrote:

    Thanks, very easy to understand

  29. Manuel wrote:

    Fantastic!!! helps a lot.

  30. Brian wrote:

    Fantastic, 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.

  31. Edo wrote:

    You 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.

  32. Carel Viljoen wrote:

    Extremely quick and painless… nice one guys.

  33. Carel Viljoen wrote:

    Edo – just tried yours as well. Beautiful in its simplicity.

  34. Carel Viljoen wrote:

    Three 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.

  35. Di Kelly wrote:

    Superb – thank you!

  36. tomtom wrote:

    Hello 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..
    Looking forward to hear from u.

  37. mlh wrote:

    This does not correct label the datapoints. Negative values are shown as positive.

  38. Jack wrote:

    Dear Tomtom,
    To get automatic coloring of both positive and negative effects, you can try this free template: http://www.waterfall-chart.com !
    Hope it helps
    Jack

  1. How to Create a Dashboard in Excel | Excel & VBA - da Tab Is On wrote:

    [...] the data point you want to show. If you want to show the profit and loss statement, use a waterfall chart (if needed). If it’s a project timeline, use a gantt chart. While using a chart in your Excel [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel