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.

excel-waterfall-chart-template-with-negative-values

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.

first-method-of-creating-a-waterfall-chart-in-excel

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.

correct-method-of-creating-a-waterfall-chart-in-excel

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=""> <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?

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