Waterfall Charts for Excel

Waterfall Charts for Excel

The Waterfall Charts macro allows you to easily create waterfall charts in excel with one click. Waterfall charts are not supported by Excel, thus making its construction time consuming and inefficient. The customized macro allows to create professional looking charts within seconds. Version 2 creates charts with one click, partial sum arrows, and convert to image function to allow easy embed in a presentation. you can use an Excel Waterfall chart to show the cumulative effect of positive and negative amounts, based on a starting value. For example, show the monthly net cash flow amounts in a waterfall chart, and quickly see which months had positive and negative results.

A waterfall chart shows a running total as values are added or subtracted. It’s useful for understanding how an initial value (for example, net income) is affected by a series of positive and negative values.

The columns are color coded so you can quickly tell positive from negative numbers. The initial and the final value columns often start on the horizontal axis, while the intermediate values are floating columns. Because of this “look”, waterfall charts are also called bridge charts.

A waterfall chart (also known as a cascade chart or a bridge chart) is a special kind of chart that illustrates how positive or negative values in a data series contribute to the total. In other words, it’s an ideal way to visualize a starting value, the positive and negative changes made to that value, and the resulting end value. In a waterfall chart, the first column is the starting value and the last column is the end value. The floating columns between them are the contributing positive or negative values.

Note: Other fun names for waterfall charts include Mario chart and flying bricks chart, because individual chart elements resemble an old arcade game.

Some people like to connect the lines between the contributions to make the chart look like a bridge (giving us the bridge chart name), while others leave the columns floating.

Uses of waterfall charts

Waterfall charts are popular in the corporate and financial environment because they are very useful for a visualization of the positive and negative movements within a measured quantity or KPI, such as your Monthly Net Profit or Cash Flow.

Other examples of quantitative analyses, where waterfall charts are used, include:

• Visualizing profit and loss statements
• Comparing product earnings
• Highlighting budget changes on a project
• Analyzing inventory or sales over a period of time
• Showing product value over a period of time
• Creating executive dashboards

In a nutshell, use a waterfall chart whenever you want to show how a starting value increases or decreases through a series of positive or negative changes.

Tip: While the most typical waterfall chart is the one with a starting and ending value, you can also create subtotals as visual milestones in the series. These show up as full columns. For example, you might want to use Net revenue and Gross Income as two checkpoints between Gross Revenue and Net income starting and ending values.

How to create a waterfall chart in Excel

Before Office 2016 creating waterfall charts in Excel was a notoriously difficult process.

Note that I used the word “creating” and not “inserting”. That’s right – you did not insert a waterfall chart, you created it.

… using tutorials

To create a waterfall chart in Excel 2013 and earlier, you had to define additional data series (with complicated formulas) in the data table and then make them invisible in the chart.

And we’re not talking about 1 invisible series. If the waterfall chart dipped below zero at one point, you needed at least seven additional series!

Here are just some of the many tutorials on creating a waterfall chart in pre-2016 Excel:

To get around having to follow this long process every time, people often resorted to using  a waterfall chart template:

Of course, using templates is not ideal. If your data has a different number of categories, you have to modify the template, which again requires additional work.

Ideally, you would create a waterfall chart the same way as any other Excel chart: (1) click inside the data table, (2) click in the ribbon on the chart you want to insert.

… in Excel 2016

Microsoft decided to listen to user feedback and introduced 6 highly requested charts in Excel 2016, including a built-in Excel waterfall chart.

No more templates, additional series, formulas or tinkering with the charts. 2 clicks and your awesome waterfall chart is inserted.

Or is it?

While the addition of waterfall charts in Excel 2016 is a great step forward, the current functionality still leaves much to be desired.

10 steps to a perfect excel waterfall chart

Here are some ways that can help you create better Excel waterfall charts and some things that are still missing.

1. Remember to set the totals

Let’s say we want to have this data table visualized with a waterfall chart: EBITDA of our fictional company for the years 2015, 2016 and the individual contributions of 7 small business units to the change from 2015 to 2016.

This shouldn’t be too hard. Click inside the data table, go to the “Insert” tab and click “Insert Waterfall Chart” and then click on the chart. Voila:

OK, technically this is a waterfall chart, but it’s not exactly what we hoped for. In the legend we see Excel 2016 has 3 types of columns in a waterfall chart:

1. Increase
2. Decrease
3. Total

This is correct, but in the chart there are no Total columns, only Increase and Decrease. The first and last columns should be Total (start on the horizontal axis) and to set them as such, we have to double-click on each of them to open the Format Data Point task pane, and check the Set as total box.

You can also right click the data point and select Set as Total from the list of menu options.

Finally, we have our waterfall chart:

2. Ditch the clutter on your visualization

Data visualization best practice is to remove ALL elements from the visualization that are not absolutely necessary (if you’re interested, you can learn more about this in our webinar: Data visualization in depth).

Similar to other Excel charts, the default Excel waterfall chart also suffers from having too much clutter. The legend, the vertical axis and labels, the horizontal grid lines – none of them contribute to the reader’s better understanding of the data. If anything, they are a distraction.

So, let’s remove all unnecessary elements and write our key message to the title. It’s a shame that the chart title cannot be inserted automatically from a cell.