Waterfall Chart | How to Create Waterfall Chart in Excel (2016, 2013 & 2010)

Excel Functions Tutorials

Excel Waterfall Chart (Table of Contents)

Waterfall Chart in Excel
Waterfall Chart in Excel 2016
How to Create Waterfall Chart in Excel (for 2013, 2010, 2007 and older versions)?

Waterfall Chart in Excel (2016, 2013, 2010, 2007 and others)

A waterfall, also known as a bridge or cascade, chart is a form of data visualization that helps to understand how the sequentially introduced positive or negative values affect the final value, showing a cumulative effect. It is also known as a flying bricks chart and Mario chart.

The Waterfall excel chart was introduced in 2016. The chart demonstrates how value increases or decreases through a series of changes. By default, the positive and negative values are colour coded. In 2013 or earlier versions of Excel, the Waterfall chart type is not present. However, in this article, you will also learn how to create a waterfall chart if you are using an older version of Excel.

Where to Use a Waterfall excel chart?

Waterfall graph in excel is widely used for visualizing financial statements, comparing earnings and analyzing sales or product value over a period of time. It is also used for analyzing profit and loss and visualizing inventory. They became popular in the late 20^th century when McKinsey and Company first presented them in a presentation to their clients.

#1 – Waterfall Chart in Excel 2016

Suppose you have the revenue data for each month. The data is given in A3:B14 as shown below:

Now, you want to visualize the data on a waterfall chart in excel. You can select the cells A3:B14 and click on chart type and select Waterfall excel chart.

A waterfall chart in excel for this data will look like—

The chart will show the revenue generated in each month of the year.

You can also build a similar chart using older versions of Excel. Let us see how.

First, create five columns A:E – (1) Months (2) Decrease (3) Increase (4) Revenue.

The Start value will be in 3^rd row and End in 16^th row as shown below.

For Decrease column, use the following syntax –

= IF( E4 <=0, -E4, 0) for cell C4 and so on.

For Increase column, use the following syntax –

= IF( E4 >= 0, E4, 0) for cell D4 and so on.

For Base column, use the following syntax –

= B3 + D3 – C4 for cell B4 and so on.

The data will look like –

Now, select the cells A2:E16 and click on charts.

Click on Column and then plot a Stacked Column chart.

The chart will look like this.

Now, you can change the colour of the Base columns to transparent or no fill, and the chart will turn to a waterfall chart as shown below.

#2 -Waterfall Chart in Excel (older versions 2007, 2010, 2013)

Let us now see some examples of the waterfall graph.

Waterfall Graph – Example #1

Suppose you have the net cash flow for your company for each month of the year. Now, you want to see this cash flow over a Waterfall graph for better visualization of cash flow throughout the year and see which period faced the most crisis. The net cash flow data is shown below.

To plot this chart, simply select the cells B3: C16 and click on the waterfall chart to the plot.

If you have an earlier version of Excel, you can use an alternative method to plot.

Firstly, make five columns, Time, Base, Decrease, Increase and Net Cash Flow as shown below.

Now, fill in the details for the Start. The Base value will be similar to Net Cash Flow, Decrease and Increase will be 0.

For Jan, the increase will be given as –

= IF ( F23 >= 0, F23, 0) for cell E23.

For Jan, the decrease will be given as –

= IF ( F23 <= 0, -F23, 0) for cell D23.

For Jan, the base will be given as –

= C22 + E22 – D23

Now, simply drag them to rest of the cells.

Now, you can select the cells B21: E35, and click in Charts -> Columns -> 2D Stacked Column.

The chart will look like this.

Once the chart is prepared, you can set the Base fill to no fill, and you get this chart as shown below. Also, you may change the colour of Start and End bars.

You can adjust the thickness of the bars and Gaps in between to better visualization using the following steps –

Right click on the chart -> Select Format Data Series -> Decrease Gap Width.

Now, Finally, your chart will look like this.

In the chart, you can observe that there was an increase in the Net Cash Flow during the year and the Net Cash Flow was maximum during July. However, during Aug – Sep, the decrease was huge.

Waterfall Chart – Example #2

Suppose there are two items, Item 1 and Item 2, manufactured at your company. You have the details of the number of items manufactured in four different quarters and want to visualize them on a waterfall graph.

The data of the two items is given as:

Now, insert three columns in between- Base, Item 1, Item 2. Also, make a column of Total.

The Total column will contain the sum of items produced during that time period. For cell G13, it will be given as –

= E13 + F13

and the result will be:

Drag and drop for the remaining cells.

For the Initial time period, the base value will be the sum of the items, i.e, 220 in this case. C13 and C14 cells may be marked as 0.

For the base value, the syntax will be given as –

= B13 + C13 + D13 for cell B14.

Column C and D will be similar to E and F for all the four quarters.

So, simply drag it for the four quarters and you will observe the data as –

For End, the Base value will be –

=B17 + C17 + D17

Result:

and Item 1 and 2 will be zero for End.

Now, select the cells A12 : D18 and select Chart and make 2D Stacked Column.

Then, select No Fill to Base Column and you will get an Waterfall chart as shown below.

Alternatively, you may want to colour code the Initial values also. To do this, simply change the Base value for Initial to zero and keep the original Items values (100 and 120). You will get the plot as –

Pros

The Waterfall excel chart is simple to create in Excel 2016+.
Good visualization of change in values over time.
Good visualization of negative and positive values.

Cons

The waterfall graph type has limited options in Excel.
Making this chart becomes cumbersome when using 2013 or lower version of Excel.

Things to Remember About Waterfall chart in Excel

It shows a cumulative effect of how the sequentially introduced affect the final value.
It is easy to visualize positive and negative values.
This is a built-in chart type in Excel 2016.

You can download this Waterfall Chart in Excel template here – Waterfall Chart Excel Template

Waterfall Chart in Excel