Waterfall Chart in Excel

What Do You Mean By Waterfall Chart in Excel?

Waterfall chart in excel is also known as Bridge chart in excel, which is a special type of column chart which is used to show how the start position of a certain data series changes over time be it a growth or decrease, the first column in the waterfall chart is the first value while the last column in the waterfall chart is the final value and in total they represent the total value.

Explained

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 color-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.

Uses of Excel Waterfall Chart

Waterfall chart 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.

How to Create a Waterfall Chart in Excel 2016?

Now, for example, you want to visualize the data on a waterfall chart.

You can select the cells A3:B14 and click on chart type and select the Waterfall chart.

A waterfall chart 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 a 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 in excel.

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.

Create a Waterfall Chart in Excel Older Versions 2007, 2010, 2013

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.

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

• 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.

Recommended Articles

This has been a guide to Waterfall Chart in Excel. Here we discuss its uses and how to create Waterfall Chart in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• Gantt Chart Examples
• Create a Bubble Chart in Excel
• Area Chart in Excel
• Pie Chart in Excel