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.