A waterfall chart shows the impact of a series of changes on an initial value to produce a final value, for example changes in a budget or forecast between two years. Unfortunately, this is not a standard chart in Excel’s selection so we will look at how to create it.

What is a Waterfall Chart

The easiest way to understand what a waterfall chart is, is to simply show you one!

Waterfall Chart Example

The changes that increase the final value are usually shown in one colour (e.g. green) and the changes that decrease the final value are usually shown in another colour (e.g. red).

How to Create a Waterfall Chart

There are two basic approaches for drawing a waterfall chart in Excel:

• Use a stacked column chart using three data sets: first data set specifies the start of the bar (hide this series as we did in the genealogy chart), the second set is used for the increases and the third set is used for the decreases. The trick is to create the data for your chart so that it correctly works out the values for the first set of data. Here are some examples on the internet of using this method: Contextures’ Excel waterfall chart and AbleBits’ waterfall chart.
• Tweak a candlestick chart so that it looks like a waterfall chart.  Candlestick charts  are used in trading (e.g. share trading) for identifying signals of price movements. Candlesticks have the property that when the price goes up (closing price is greater than the opening price) then the box has one colour (usually white in trading) and when the price goes down then the box has a different colour (usually black in trading). It is this property of the candlestick chart that we take advantage of.

So we are going to look at the candlestick chart method in this post.

Step 1: Create or Collect Your Data

First put together the raw data that you want to plot.  For example:

Raw data for chart

Step 2: Add Extra Columns for the Chart

The candlestick chart has four values: open, high, low and close.  We apply a trick to remove the upper and lower shadows of the candlestick – these shadows look like the whiskers in a box and whisker plot. Set the high equal to the maximum of the open and close.  Similarly, set the low equal to the minimum of the open and close.

Here are the formulas for the four new columns:

Insert four columns and formulas

Here is how to do it:

• Insert four new columns between your first and second column and label them  Open, High, Low and Close.
• Set the Open value for the first and last bar equal to zero so that these bars are full length.
• In the Close column, the closing figure is your open value for that row plus the value in the Sales column for that row.  Copy this formula down to the last row.
• Set the formula for the remaining values in the Open column as equal to the previous close.
• Set the High equal to the maximum of the open and close and copy this formula down.
• Set the Low equal to the minimum of the open and close and copy this formula down.

Step 3: Create the Chart

To create the chart:

• Select the first five columns, i.e. the Factor column in the example above plus the Open, High, Low and Close columns. Don’t select the Sales column.
  

  Select chart data

• Go to the Insert tab of the ribbon, select Other Charts and select the second Stock chart.
  

  Other Charts_Stock_Second chart

This produces the following chart:

Waterfall chart before tidying it up

Step 4: Tidy Up the Chart

Here are the adjustments that I made to the chart:

• I added a chart title.
• I removed the legend, i.e. set the legend to None.
• I adjusted the vertical axis so that the numbers started near where all the activity is.  Generally, we want to focus on the changes contributing to the final value.
  

  Specify a suitable minimum for the vertical axis

• I made the labels on each axis Bold font.
• I changed the colour of the up bars by clicking on the up bars, right mouse clicking and selecting Format Up Bars.
  

  Select Format Up Bars

  I then changed the Fill  to Solid Fill and selected a fill colour (green).

  

  Change to Solid Fill and select color

• Similarly for the Down Bars, I changed their colour to red.

The end result is the waterfall chart at the top of this page.

Try it out for yourself. If you are interested, find out more about candlestick charting.