In July 2015 Microsoft announced that the then-upcoming Office 2016 would introduce 6 new charts to their line of charts. The one that was most highly anticipated in the financial community was definitely the built-in Excel waterfall chart.
Waterfall Charts 101
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.
Some people like to connect the lines between the contributions to make the chart look like a bridge, while others leave the columns floating.
Waterfall charts are popular in the corporate and financial environment because they are very useful to visually show the positive and negative movements within, say, your Monthly Net Profit or Cash Flow.
Other examples of situations where you might want to use a waterfall chart are:
- 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
Excel Waterfall Chart
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 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 at one point dips below zero, at least seven additional series are needed!
Here are just some of the many tutorials on how to create waterfall charts in pre-2016 Excel:
... using templates
To get around having to follow this long process every time, people often resorted to using templates:
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 insert 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.
Here are 8 reasons why the default Excel waterfall chart (still) sucks:
1. Too much work to set 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.
Well, this shouldn't be too hard. Click inside the data table, go to "Insert" tab and click "Insert Waterfall or Stock Chart" and then click on "Waterfall". 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:
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.
Finally, we have our waterfall chart:
2. There's too much stuff around and on the chart
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 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 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.
(Tip: To remove the distracting chart elements, right-click on each of them and then click "Delete".)
Great, this is much better. But it required additional work that would not be required if Excel defaults were better. But they are what they are.
There are also some other limitations of Excel charts (all charts, not just waterfall charts) that advanced Excel users surely miss:
3. Too many clicks required to break the axis
This limitation is especially noticeable in waterfall charts, because we have essentially two different types of data in waterfall charts:
The common problem is that contributions are often very small compared to totals. This is also apparent in our example (see image above).
This chart correctly visualizes the situation: the contributions really ARE that small compared to totals. Our 2016 result is essentially the same as our 2015 result.
This visualization is also completely in line with IBCS Standards.
But users (and their bosses) are sometimes more interested in contributions and not so much in totals and the relationship between the two.
In this case the only viable option would be to break the vertical axis: have the totals start at, say, 35000. This enables individual contributions to be much more pronounced, but risks unaware readers reaching false conclusions about the data.
Anyway, to do this, you can again resort to using tutorials and templates:
Another, somewhat simpler option is to do the following:
- Click on the chart to select it
- Re-add vertical axis: Go to Design>>Add Chart Element>>Axes>>Primary Vertical
- "Break" vertical axis: right click on the vertical axis and click "Format Axis...", then under Axis Options write "35000" under Bounds>Minimum.
- Remove vertical axis: right click on the vertical axis and click "Delete"
This is the chart we end up with:
Now the contributions are much more prominent, but there's no obvious indication that the vertical axis does not start at zero which is really bad because the user does not draw the correct conclusion from the visualization.
4. Not able to display relative contributions in percentages
When analyzing contributions you're often more interested in relative contributions (in percentages of the total) than in absolute contributions.
Unfortunately if you want to do that in default Excel waterfall chart, you're out of luck - you're stuck with displaying absolute contributions only.
5. No difference highlights
Another thing that you're not able to do in an Excel waterfall chart is display the total difference from year 2015 to year 2016 in our example.
Sure, you can see in the chart that the 2016 column is higher than the 2015 column (especially now that we cut the vertical axis). But by how much? Unless you can do complex subtractions in your head, you don't know the exact number. Relative difference in percentage? Forget about it.
Since this difference between totals is rather important, it's definitely a major feature that's missing in Excel waterfall charts.
6. No vertical Excel waterfall chart
We know from the How to Choose the Right Business Chart article that horizontal charts (i.e. the charts that have a horizontal category axis) are used to display time-related data. For everything else we should use vertical charts instead.
Waterfall charts are no exception. Strangely, in Excel 2016 there is no way to insert a vertical waterfall chart. While this feature has been requested, there's no indication whether it will be implemented and when.
So if you wanted to visualize an income statement with a vertical Excel waterfall chart, you'd again have to resort to using templates and tutorials like this one ...
7. No subtotals
Since we're on the subject of visualizing income statements - in a typical income statement there are a some categories that are actually sums of several other categories.
For example: you can choose to calculate a sum of all Operating Expenses (OpEx). This better visualizes the relationship between "Revenue" and "Earnings before interest and taxes" (EBIT). EBIT = Revenue - OpEx.
In a table this is easy to do - just write a formula and you're done.
In an Excel waterfall chart? Not so much. It's apparently so hard to do it manually that there's not a single tutorial or template available on the internet.
8. Scaling multiple charts is time consuming
Finally we arrive at one major feature that's missing in Excel from the very beginning: scaling multiple charts.
While this problem is not limited to waterfall charts, it's too important not to mention it here.
Ensuring that all related charts in a report or dashboard are in the same scale is one of the most important concepts in data visualization.
I cannot stress this enough!
If you don't synchronize scales, don't even insert the charts
All too often you see two Excel charts side by side with completely different scales. I'm sure each of them is an adequate data visualization on its own. But once you put them side by side, you have to ensure they are scaled! Otherwise please don't even insert the charts, leave the data in a table.
So, how do you synchronize scales of Excel charts? While the procedure is not particularly hard, it is time consuming. It's a similar procedure that we used to break the axis.
Say we have these two default Excel waterfall charts and we need to scale them:
The first step is to re-add Vertical Axis on both charts.
- Click on the first chart to select it
- Re-add vertical axis: Go to Design>>Add Chart Element>>Axes>>Primary Vertical
- Repeat for the second chart
This is what we have so far:
Now we have to adjust the scale of the right chart to be the same as the left. Right click on the vertical axis and click "Format Axis...", then under Axis Options write "600" under Bounds>Minimum.
Remove vertical axis from both charts (right click on the vertical axis and click "Delete") and we have our correct visualization:
OK, that wasn't too bad. Now what if you have a monthly report with 6 waterfall charts on it? Would you do this procedure for 6 charts every month when the data changes? I guess not.
Of course you can automate this, but you have use VBA to do it. If you don't want to use VBA, maybe this article from 2012 by Jon Peltier will help you ...
There has to be a better way!
If you don't have time to tweak default Excel waterfall charts and would like to add those advanced features to your waterfall charts with a few clicks, take a look at the animation below:
I'm sure you'll agree that compared to other methods with following tutorials and using templates linked in this article this looks so easy it's scary!
Now let's try to do something that you cannot do with default Excel waterfall charts - relative contributions in % and difference highlights:
How about inserting two scaled vertical waterfall charts in an income statement? 2 clicks and you got it. 🙂 Pay attention to the subtotal visualization for OpEx!
We saved the best for last: here's 8 waterfall charts in 2 clicks:
Interested? Try it yourself!
If you'd like to insert waterfall charts like these or any other of the 20+ advanced business charts, give Zebra BI a try here: