Waterfall charts, also called bridge or stepped charts, have become popular in recent years, particularly when displaying the output of a financial model. A waterfall chart displays very effectively the incremental 
impact of each period of time, or unit. It is a type of bar chart where the value of the second bar generally begins where the first one finished.
Extracted from Chapter 12 of Using Excel for Business Analysis, Second Edition. Download the accompanying workbook.
Despite their popularity, waterfall charts are not yet a standard chart type in Excel and to create one can get very complicated. Many modellers have their own templates for creating them, as – let’s face it – most dashboard and board papers nowadays normally include some kind of waterfall chart! In this article, we will cover two methods of creating these kinds of charts:
1. Firstly the “dummy stack” method which is simple to create, but fairly limited in the data it can show as it’s restricted to positive values only.
2. A superior method is to use up/down bars which are a little trickier to build, but far more versatile in the data it can display.
If the incremental cash amounts for each month are always positive this makes a waterfall chart very simple so it will only take a few minutes to build, as there are no values below the x‐axis. First, we’ll take a look at creating a very simple waterfall chart using the Dummy Stack Method, then we’ll tackle some more complex charts using Up/Down Bars.
Creating a Basic Waterfall Chart Using the Dummy Stack Method
We’d like to see the incremental contribution of each month individually, which will add a new dimension to the analysis. Contrary to popular belief, you don’t need fancy software to build a basic waterfall chart. They are really not that difficult to build in Excel and are achieved by a few little tricks, as shown in the following list of 11 steps.
1. Open a blank worksheet and start by entering the dates and your cash amount in columns A and C as shown, leaving column B blank. Alternatively, you can download the accompanying workbook.
2. In column B, we need to create a dummy series, or “dummy stack,” which will be used to push each cash series bar up to where the last one ended. So column B will contain the cumulative balance from the previous period. You’ll see what I mean in a minute.
3. We need to make cell B3 a cumulative balance, so the formula in cell B3 should be =B2+C2. Copy B3 down to B7. Let’s label it the Base column because it’s effectively where the bottom of each column starts. It should now look something like Figure 2.
Figure 2
Figure 3
Figure 4
9. You can remove the background gridlines by clicking on them and deleting.
10. If there is a border around the white series, you can remove this by going into Format Data Series again and selecting the No Border option.
11. Your waterfall chart should look something like Figure 5.
Figure 5
Creating a Waterfall Chart Using Up/Down Bars
What if your cash forecast changed, however, and in April you are expecting a poor month, with a loss amount of $500? See the following list of data:
Simply changing the data on your existing waterfall chart model will not display the chart correctly. Entering a negative value will cause the series to display below the x-axis, which is not correct. See Figure 6.
Figure 6
There are a few ways of correcting this, and most methods can get very complicated and difficult to maintain. If we continue with the stacked column method for this example, the formulas get quite complicated. Below is a completely different method of 10 steps to create a waterfall chart using Line charts and Up/Down Bars, which is a far simpler and more robust method.
1. Set up your data in vertical rows like we did in the last example, except this time we don’t need to leave column B blank for the dummy stack.
2. This time, we need to calculate the “Upper” and “Lower” points for the boundaries of our Up/Down Bars. To create the Upper number, create a cumulative formula in column C. Your formula in cell C3 should be =SUM($B$2:B3). Copy it down the column and your model should look something like Figure 7 so far.
Figure 7
3. Now we can calculate the Lower number by subtracting the Cash amount from the Upper number. Your formula in cell D2 should be =C2-B2. Copy it down the column.
4. Next, create a line chart using the Upper and Lower numbers. We don’t need to show the cash amount on the chart. The easiest way to do this is to hold down the control key to highlight nonconsecutive ranges, and then create the line chart. Your model should look something like Figure 8.
Figure 8
5. Click on the series legend and delete it.
6. Change the title to “Cash Forecast Waterfall”
7. Now add the Up/Down bars to the line chart by clicking on the chart, and select Up/Down bars from the Chart Elements option that appears, if you are using Excel 2013. For previous versions of Excel, then choose Up/Down Bars from the Layout tab on the Ribbon that appears when the chart is selected. For Excel 2011 on Mac, choose the Chart Layout tab to access the Up/Down Bars in the analysis section of the Ribbon.
8. The Up/Down bars will appear between the line series, as shown in Figure 9.
9. Click on each line, right-hand click and select Format Data Series. Under Line or Line Color, select No Line. On Excel 2011 for Mac, Control+Click each line, go to Format Data Series, and under the Line option on the dialog box change Color from Automatic to No Line.
10. Change the color of the bars (they automatically default to black and white) if you wish. This cannot be done through the color scheme, you need to select each series and manually change the color, because they are not ordinary series; rather they depict the difference between the two lines. Although this looks like a column chart, it is still in fact a line chart! Your chart should look something like Figure 10.
Figure 10
Creating a Complex Waterfall Chart (with Positive and Negative Values Crossing the X-Axis)
You may have noticed that the charts we created above all remain as positive total values. The Dummy Stack method will simply not work if the columns are below the x-axis. To create a waterfall chart that will handle both positive and negative values, as well as a balance that crosses the x-axis using the Dummy Stack method, we’d need to create much more complex calculations. Fortunately, the Up/Down bar method is much simpler.
Using exactly the same method as described above, we can create a more complex waterfall chart that includes both positive and negative values, and crosses the x-axis. See Figure 11 for the completed chart.
Figure 11
