Excel – Creating a Basic Waterfall Chart
Waterfall charts are used to show how you got from one figure, let’s say at the start of the year, to how you ended up at the figure at the end of the year, but instead of showing a total value for each period, it will show you the change in value from one period to another.
Let’s take a simple example…
Above we can see the cash amount left over at the end of each month. If we chart this with a basic column chart it shows those values as solid columns. We can see that there is a change from month to month but we can’t see by how much unless we work it out in our heads.
The waterfall chart (sometimes called a flying bricks, bridge or Mario chart) will display the change in value whether positive or negative as a floating bar. This type of chart is used in a number of ways but examples might include showing cash flow over time, or changes to inventory or staff levels.
Instead of a standard column chart we want to create this;
There are add-ins you can buy to create a variety of waterfall charts and I will mention this again later. If all you want though is a basic waterfall chart then it can be achieved relatively simply using a handful of formulas and a bit of formatting.
First we need to modify our data table.
Insert four new columns. You can call them anything you like. In this example I have named them;
We also need to add a couple of new rows to the table. We need an opening balance (i.e. how much had in the bank at the end of December 2014) and a closing balance at the end of the year.
This will now give us the following layout;
Next, calculate the change from one period to another.
Simple bit of maths, one cell minus another
=F3 – F2
…and copy down.
Now we need to create what will become the floating bars in our waterfall chart. We need one calculation to identify positive values and one for negative values.
In D3 I need the following formula;
This tests the value in the VARIANCE column. As we are looking for positive variances we test for values greater than 0. If the value is negative display a 0 (zero).
Now for the negative values. In cell E3 enter the following formula;
This time show which values are negative, but as we need these values to be displayed as floating bars, the values need to be converted to positive ones, hence the use of the ABS (absolute) function.
As a quick check, in each pair of cells representing an INCREASE and a DECREASE, you should find one cell with the value 0 and the other with a positive value in it.
The final calculation we have to do now is work out the INVISIBLE value. We need this to create a value to calculate the start value of our DECREASE/INCREASE bars.. The close up below of the bars in the final waterfall chart shows what we are trying to calculate.
To work these out we need to add the INVISBLE value to the INCREASE minus the DECREASE. Copy across the OPEN and CLOSE values into your INCREASE column and enter the formula as shown below.
Looking at the numbers, the sum of the INVISBLE and the DECREASE values in one line should add up to the INVISBLE value in the line above.
Copy your formula down and we now have a full set of data from which to build the chart.
Select cells A1 to the bottom of your INCREASE column.
Go to the INSERT tab and select the 2-D STACKED COLUMN chart.
All we need now is a bit of formatting to get the final effect.
Select the INVISIBLE series and format the bars to have NO FILL. Then remove INVISBLE from the LEGEND as it is no longer required.
If you want, colour code the INCREASE/DECREASE bars. In this example I will make INCREASE bars green and DECREASE bars red.
To differentiate your OPEN and CLOSE balances from the variance bars, colour these differently. You may have to click twice on the end bars to select them separately from the rest of the series. Pick any colour you like.
As a finishing touch, you can add the variance values to the floating bars using Rob Bovey’s free add-in – X Y CHART LABELER from www.appspro.com
And there we have our final chart.
If you don’t fancy doing all those calculations then you might want to consider buying Jon Peltier’s PELTIER TECH CHART UTILITY add-in. The full advanced edition sells currently (April 2015) for $99 (http://peltiertech.com/Utility20/). At that price it may not be for everyone, but if you have to build complex waterfall charts (as well as some other types included in the add-in) on a regular basis then it’s worth investing in.