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

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

## 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=""> <s> <strike> <strong>

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.

 123456 Sub Waterfall_Width_Change() For Each grp In ActiveChart.ChartGroups         grp.Overlap = 100         grp.GapWidth = 70 Next grp End Sub

Regards,

11. Federico wrote:

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?
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). π 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!!!!

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:

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 […]