Categorized | excel chart, excel tips

Excel Waterfall Chart Template with Negative Values

If all you need to do is to get a copy of the Excel Waterfall Chart Template with Negative Values then you can simply download it here.


For those interested in knowing how to create a waterfall chart in excel, please read on.

How do I create a waterfall chart in excel?

There are two ways to create a waterfall chart in Excel. The first is to simply lay out a grid of number and create a column chart based on it. The trick here lies in knowing which cells in the grid to keep blank and which ones to populate with numbers. However I personally find it time consuming and error prone.


The second method, and the one that I prefer is to create a 4 column layout with specific formulas in each. In this method, you will need to copy the 4 cells downwards to as many rows as there are data points to be plotted. This takes the guesswork involved in the first step of knowing which cells to keep blank and which ones to populate.


You can take the numbers you want to plot and create a grid using the formulas as shown in the diagram. Essentially the graph now has four distinct components:
1. The Plot: The value that you want to plot.
2. The Padding: Column charts in excel by default start from 0 on the x-axis. However, since each of values in a waterfall chart begins from where the previous one ended and not from 0, you will need to ensure that a +ve (or a -ve) “padding” is provided to each of the values. That way they the new plot can begin from where the previous plot ended and not from the 0 on the x-axis.
3. The Crossover: Oftentimes while creating a waterfall chart, we encounter a situation where the plot value moves across the X-Axis. For example, if the first figure was 500 and the second figure is -2000, then the first plot will go up by 500 units and the second one will go down 2000 units. The starting point for the second plot will be +500 and the ending point will be -1500 units. In such a case, some part of the second plot will be above X-axis and some below. The workaround to this problem is to use the actual plot value (in Point 2) to plot first half of the actual value (the positive 500 of the 2000 units that you want to plot) and use the Crossover value to show the remaining portion (-1500 out of the 2000). Hence using the plot value and the crossover you can show a single bar moving from the above to x-axis to below it or vice versa.
4. The datum: The datum is simply the point where one plot ends and the other one begins. Datum is simply the sum of all preceding plot values. For example, if three successive values are 30, 50 and -100, the datum will be 30+50-100 = -20.

Once you have the grid layout as shown in the diagram above, select the range consisting of padding, plot and crossover values. Then from the menubar select ‘Insert’ -> ‘Chart’ and select the stacked column chart. initial-default-waterfall-chart-in-excel Your initial waterfall chart will come out looking out something like as shown in this figure. You can now double click and clear the grey background, remove column borders, change the color of padding to transparent and color the crossover values in the same shade as the plot values. Your chart should come out looking like a proper waterfall chart like the one shown above.

How to incorporate negative values in the waterfall chart?

The litmus test for any waterfall chart is whether it can handle negative values. Often times you will find examples of charts where the moment you make some values negative, the chart simply devolves in to Excel’s default column chart type. If you use the formulas shown above for plotting the padding and crossover, you should be able to overcome this problem easily.

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

Comments and Trackbacks

  1. Marc wrote:

    This is by far the best waterfall template I’ve seen. Way to go! I’ve modified it using your XY chart label trick so that I can put useful numbers above each waterfall item. Very, very, very helpful.

  2. Dinesh wrote:

    Excellent solution to urgent waterfall graph solution.
    Very helpful.

  3. Mike C. wrote:

    Very helpful! One question: how can I automatically assign a color (green) to a displayed block showing a positive direction and another color (red) to a block showing a negative direction?

    I’d like also to leave the starting point and the end point in the same color, say gray….

  4. Vic wrote:

    This is an excellent waterfall template. Found it extremely useful for incorporating negative values into the chart

  5. Lossen wrote:

    This is really excellent! I have tried many time to design one with negative to positive value but failed. It really save me. Many thanks!!!

  6. Denis J. wrote:

    This is the BEST CHART that I have seen anywhere – WELL DONE…!!!!!!

    How would I add is budget numbers?

  7. Christian wrote:

    It’s excellent, and easy to modify. Thanks for that

  8. James wrote:

    Outstanding work, thanks.

  9. aaron spencer wrote:

    One question: how can I automatically assign a color (green) to a displayed block showing a positive direction and another color (red) to a block showing a negative direction?

    same question another person had

  10. Cesar wrote:

    It is exactly what I was looking for, but I can’t identify the columns H, I, D used on your formulas, padding is what column? same for plot, crossover and datum. Thank you very much for your time, it is very helpful.

  11. Richard wrote:

    I’ve used your template many times and it’s been invaluable. However, for the first time I used it with negative crossover and noticed that if I have the data labels showing then the labels are split showing a + value, 0, and a – value. How can I have the data label show it’s actual value? (In you template for example I see 1300, 0 and -2200 instead of 3500.)

  12. Allan Gray wrote:

    Excellent template gents.

    Thank you for your efforts.

  13. Raul Benavente wrote:

    Great and Awasome! very helpful!

    As Cesar wrote, I can’t identify the columns H, I, D used on your formulas. Thanks for your contribution!

  14. JR wrote:

    Thanks, ready to use template, really powerfull

  15. Walt wrote:

    Really cool and simple solution to a regular problem. Used it with some kind of adaptation at work.

    Just one question: Is there a easy way to put the values on top or below the bars but with the right sign?

  16. Wardie wrote:

    Legends !!! Works really well. Thanks.

  17. Julie wrote:

    A great set of formulas to plot out negatives – thank you.
    To make the formulas work:
    Actual Values = D
    Crossover = H
    Datum = I

  18. Lagren wrote:

    Good tutorial, but actually I have one question.

    What’s the point of doing conditional formula, while both results are exactly the same, for me it seems pointless, but maybe someone could explain me the purport of it.

    Just look:

    doesn’t it give the same solution with less effort and resources?

  19. fayley wrote:

    “I can’t seem to get this working. I’ve assumed
    Actual Values = D
    Crossover = H
    Dataum = I
    …. but it is returning all 0 results.
    Is everyone sure that these are the correct references?”

  20. Ravi wrote:

    Awesome solution. The best chart I have ever seen.

  21. afolabi wrote:

    Thanks so much this solution was very hopefull..

  22. Zulay wrote:

    Can someone explain to me how the formulas goes?
    What are you referring in your formula to D or I or H columns??? would you mind be more specific?

  23. JJ wrote:

    You rock – thanks

  24. Joao Wacho wrote:

    I was with the same problem of another guys, find the columns D, H ann I.
    I did some changes and it works!

    Fist of all, “I3” is Zero (0) !
    Colum D: is the Actual Values (start with Sales values in the line 4)
    Colum I: Datum (at line 4 is only =d4, than use the formula explained)
    Colum H: Crossover, here you will use the zero on the I3 many times. no problem.
    Colum G: Plot
    Colum F: Padding
    Colum E: nothing

  25. Joao Wacho wrote:

    A coment about the last coment, i3 will be zero, but when the formula copy and paste to another linhes needs to change the line, to the formula in te line 5 the i3 will be i4, the 6000.

  26. DEv wrote:

    You genious, very helpful..solved my chart in one minutes… thanks mate

  27. Tom wrote:

    This is really clear and helpful. Thank you for the template to help drive home what is written in the explanation.

  28. Bupek wrote:

    This template is superb….perfect. Very very helpful

  29. Richard wrote:

    Thanks very much, this is the best template I’ve seen. Very easy to modify, works beautifully

  30. Chris wrote:

    Excellent! exactly what I was after, very smart.

  31. Chris wrote:

    This is really wonderful. By far the best I have seen

  32. John Cunningham wrote:

    Excellent tool. The best solution for waterfalls that dip below the x axis. It’s not hard to manually adjust the colors so that all increases have the same color and all decreases have the same color.

  33. BT wrote:

    Simpler Formulae –
    Padding (same): =IF(D4*I3>=0,I3,IF(H40,0,I3+D4))
    Plot: =IF(D4*I3>=0,D4,IF(I3*I4<0,I4,D4*-1))
    Crossover: =IF(I3*I4<0,I3,0)
    Datum: =I3+D4

    D = Actual Values
    F = Padding
    G = Plot
    I = Datum

  34. Jenni McWilliam wrote:

    thank you so much, this was straightforward to follow – you saved my life. 🙂


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


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel