# 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;

- Invisible
- Increase
- Decrease
- Variance

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;

**=IF(E3>0,E3,0)**

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;

**=IF(E3<=0,ABS(E3),0)**

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.

Posted on April 23, 2015, in Charts and tagged bridge charts, charting, charts, floating brick charts, Mario charts, msexcel, waterfall charts. Bookmark the permalink. 2 Comments.

There is one more add-in for creation waterfall charts. It is Waterfall Chart Studio http://fincontrollex.com/?page=products&lang=en

How it works you can see here https://youtu.be/Z1pavJW8_e8

Thanks another alternative for people to check out