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

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Waterfall Chart in Excel (wallstreetmojo.com)

Uses of Excel Waterfall Chart

A 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 20th century when McKinsey and Company first presented them in a presentation to their clients.

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

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.

click on chart type

A waterfall chart for this data will look like—

waterfall chart

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 3rd row and End in the 16th row, as shown below.

Waterfall chart - illustration 1-1

For the Decrease column, use the following syntax –

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

Waterfall chart - illustration 1-2

For the Increase column, use the following syntax –

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

Waterfall chart - illustration 1-3

For the Base column, use the following syntax –

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

Waterfall chart - illustration 1-4

The data will look like –

Waterfall chart - illustration 1-5

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

Waterfall chart - illustration 1-6

Click on Column and then plot a Stacked Column chart in excelStacked Column Chart In ExcelA stacked column chart in Excel is a column chart in which multiple series of data representations of various categories are stacked over each other. The stacked series are vertical, and comparing multiple data series is easy, but as the number of data series increases, so does the complexity of representation.read more.

Click on Column

The chart will look like this.

waterfall chart 2

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.

waterfall chart 3

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.

waterfall chart example 1

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

waterfall chart 4

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.

waterfall chart example 1-1

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

waterfall chart example 1-2

For Jan, the increase will be given as –

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

waterfall chart example 1-3

For Jan, the decrease will be given as –

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

waterfall chart example 1-4

For Jan, the base will be given as –

= C22 + E22 – D23

waterfall chart example 1-5

Now, simply drag them to the rest of the cells.

waterfall chart example 1-6

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

2D Stacked Column

The chart will look like this.

waterfall chart 5

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

format data series

Now, Finally, your chart will look like this.

waterfall chart 6

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 a 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *