Let's say we want to have this data table visualized with a waterfall chart...

EBITDA of our fictional company for the years 2015, and 2016..

And the individual contributions of 7 small business units to the change...

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

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

Or, you can skip all this and use Zebra BI for Office to do the hard work for you...

Finally, we have our waterfall chart:

Data visualization best practice is to remove ALL elements from the visualization that is not absolutely necessary.

The default Excel waterfall chart often contains elements that do not contribute to data comprehension, instead acting as distractions...

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.

Great, this is much better. 

But it required additional work that would not be required if Excel defaults were better. 

We’ll show you how to do it better with Zebra BI for Office.

This limitation is especially noticeable in waterfall charts..

Because waterfall charts have essentially two different types of data:

1. Totals: usually the first and last column in a series.
2. Contributions: the floating bricks make up the “bridge” between the two totals.

A common problem is that contributions are often very small compared to totals. This is also apparent in our example (image above).

First, a point of order: this chart correctly visualizes the situation as 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.

However, users (and their bosses) are sometimes more interested in contributions than in totals and the relationship between the two.

In this case, the only viable option would be to break the vertical axis and have the totals start at some value larger than 0. Let’s say 35,000. This highlights individual contributions, but risks guiding unaware readers to false conclusions about the data.

Simpler option:

1. Click on the chart to select it
2. Re-add vertical axis: Go to Design >> Add Chart Element >> Axes >> Primary Vertical
3. ”Break" vertical axis: right click on the vertical axis and click "Format Axis...", then under Axis Options write "35000" under Bounds >> Minimum.
4. Remove the 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.

When analyzing contributions you're sometimes more interested in relative contributions (in percentages of the total) than in absolute contributions.

Unfortunately, if you want to do that in a default Excel waterfall chart, you're out of luck - you're stuck with displaying absolute contributions only.

See how easy this is to do in Zebra BI for Office.

Another thing that you're not able to do in an Excel waterfall chart is to display the total difference between 2015 and 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.

There’s also no way to display the relative difference in percentage.

Since this difference between totals is rather important...

It's definitely a major feature that's missing in Excel waterfall charts.

Of course, it's much easier to highlight the differences using Zebra BI for Office.

We know 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 of whether it will be implemented and when.

We prepared a demonstration in Zebra BI for Office, so you can see how to create an income statement with vertical waterfall charts.

Since we're on the subject of visualizing income statements - in a typical income statement, there are 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.

When you create a waterfall chart in Excel? 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.

You can, however, enter subtotals and designate them as such in your waterfall chart.

However, you need to calculate them yourself to make sure they are correct.

You can see how Zebra BI for Office automatically creates subtotals in this handy animated gif at the end of this article.

The default color scheme in Excel could be better.

Visit the Chart Design tab and open the Change Colors gallery.

Here, you can select a color palette. You can also choose a different theme on the Page Layout tab. 

To adjust how the colors are used, click the Colors button and select Customize Colors at the bottom of the list.

You can set it up to display positive values in green and negative values in red, which is a common approach in financial reporting.

Connector lines connect columns to show the movements in values in the chart. You can turn them on or off by right-clicking a data series to open the Format Data Series pane and checking/unchecking the Show connector lines box.

Making sure that all related charts in a report or dashboard are on the same scale is one of the most important concepts in data visualization.

All too often you see two Excel charts side by side with completely different scales.

While each of them is an adequate data visualization on its own, you must make sure they are scaled once you put them side by side!

Otherwise don't even insert the charts and just leave the data in a table — or risk misleading/incorrect interpretation.

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.

1. Click on the first chart to select it
2. Re-add vertical axis: Go to Design >> Add Chart Element >> Axes >> Primary Vertical
3. 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 the 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 to use VBA to do it.

If you don't want to use VBA, maybe this article from 2012 by Jon Peltier will help you.