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. 
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.
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.
September 27th, 2010 at 6:19 pmExcellent solution to urgent waterfall graph solution.
October 6th, 2010 at 10:34 amVery helpful.
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….
December 10th, 2010 at 11:49 pmThis is an excellent waterfall template. Found it extremely useful for incorporating negative values into the chart
April 28th, 2011 at 5:41 amThis is really excellent! I have tried many time to design one with negative to positive value but failed. It really save me. Many thanks!!!
June 1st, 2011 at 11:05 amThis is the BEST CHART that I have seen anywhere – WELL DONE…!!!!!!
How would I add is budget numbers?
June 6th, 2011 at 7:21 pmIt’s excellent, and easy to modify. Thanks for that
September 27th, 2011 at 5:26 amOutstanding work, thanks.
November 2nd, 2011 at 6:31 amOne 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
May 12th, 2012 at 10:09 amIt 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.
May 28th, 2012 at 2:01 amI’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.)
May 2nd, 2013 at 4:07 pmExcellent template gents.
Thank you for your efforts.
May 12th, 2014 at 4:41 amGreat and Awasome! very helpful!
As Cesar wrote, I can’t identify the columns H, I, D used on your formulas. Thanks for your contribution!
May 22nd, 2014 at 11:56 amThanks, ready to use template, really powerfull
June 16th, 2014 at 5:30 amReally 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?
September 10th, 2014 at 6:27 pmLegends !!! Works really well. Thanks.
October 15th, 2014 at 1:24 amA great set of formulas to plot out negatives – thank you.
January 25th, 2015 at 2:15 pmTo make the formulas work:
Actual Values = D
Crossover = H
Datum = I
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:
=IF(I3*D4>0;D4+I3;D4+I3)
=D4+I3
doesn’t it give the same solution with less effort and resources?
February 10th, 2015 at 10:17 am