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.
Excel Chart Maximum Y Axis Value and Axis Interval (Major Unit) 
Cricketing Heat Map 
Multiple Scale Chart – Adding Customized Scale to Each Category in a Chart 
Table Formula in Excel (Something I didn’t Know Till Yesterday) 
Named Range – A Few Quickies 
So How Many Of These Excel Shortcuts Do You Know, Punk ? 
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“I can’t seem to get this working. I’ve assumed
September 22nd, 2015 at 1:33 amActual Values = D
Crossover = H
Dataum = I
…. but it is returning all 0 results.
Is everyone sure that these are the correct references?”
Awesome solution. The best chart I have ever seen.
January 20th, 2016 at 11:02 pmThanks so much this solution was very hopefull..
September 21st, 2016 at 7:56 amCan someone explain to me how the formulas goes?
September 26th, 2016 at 9:07 amWhat are you referring in your formula to D or I or H columns??? would you mind be more specific?
Thanks,
You rock – thanks
September 28th, 2016 at 2:38 pmI 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) !
April 10th, 2017 at 10:18 amColum 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
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.
April 10th, 2017 at 12:19 pmYou genious, very helpful..solved my chart in one minutes… thanks mate
April 27th, 2017 at 11:15 amThis is really clear and helpful. Thank you for the template to help drive home what is written in the explanation.
June 13th, 2017 at 9:43 amThis template is superb….perfect. Very very helpful
October 1st, 2017 at 4:26 amThanks very much, this is the best template I’ve seen. Very easy to modify, works beautifully
October 11th, 2017 at 4:16 amExcellent! exactly what I was after, very smart.
January 17th, 2018 at 11:29 amThis is really wonderful. By far the best I have seen
October 14th, 2019 at 7:22 amExcellent 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.
April 20th, 2020 at 3:59 pmSimpler 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
June 17th, 2020 at 3:40 amF = Padding
G = Plot
I = Datum
thank you so much, this was straightforward to follow – you saved my life. 🙂
September 11th, 2020 at 7:17 am