## The New Waterfall Chart in Excel 2016

This article about Microsoft’s new Waterfall Chart in Excel 2016 was written by Kasper Langmann, co-founder with Mikkel Sciegienny of Spreadsheeto, a relative newcomer to the Excel blogosphere. Kasper and Mikkel are very enthusiastic about Excel, they have written a number of tutorials on the Spreadsheeto Blog, and they offer a comprehensive seven-part free training course on Excel. Mikkel approached me to write about the new charts that Microsoft introduced in Excel 2016, and he agreed that we should write some posts together.

In the first half of this post, Spreadsheeto will specifically talk about Microsoft’s new Waterfall Chart. I will follow up with a discussion of the extensive and flexible waterfall charts in Peltier Tech’s software.

## The Waterfall Chart in Excel 2016

In 2015, Microsoft released six new charts in an update for Excel 2016. This release was a direct response to user feedback and one of those new charts was the waterfall chart. The waterfall chart is a bar chart in which the bars are placed along the vertical axis at different levels according to whether they are an increase or decrease.

Then totals are shown as bars of height from zero as they are affected by the increases and decreases.

This is often useful for visualizing things like financial data where revenue can be shown as bars that shift upward vertically and expenses can be shown as bars that shift downward by contrast.

As we dive into the details of how to create a waterfall chart, note that we will work with an example scenario in Excel 2016 for Windows.

### Getting Started with a Waterfall Chart – Get the Data Right

The first and foremost objective when setting out to create a waterfall chart is to make sure our data is in the correct format. Let’s take a look at a simple example of income statement data.

It is important that our data is in this form where increases (income) and decreases (expenses) will be shown in the waterfall chart from left to right according to the data points from top to bottom in our table. Also, any totals (Total Revenue, Operating Income, and Net Income) will be shown in the chart as they appear in sequence in our data table.

Note that Total Revenue, Operating Income, and Net Income are subtotals along the way that will be shown as cumulative totals in our waterfall chart. This is set up to be a very intuitive progression as the chart presents revenue and expenses in the same logical fashion as our data table.

This is an instrumental point for setting up our data. You get out of the waterfall chart what you put into it and this means knowing how to set up our data before creating the chart.

### Inserting the Waterfall Chart

Once our data table fits this layout, simply highlight the entire table (or one cell within the table), click on the ‘Waterfall and Stock Charts’ dropdown button…

Then select ‘Waterfall’ in the menu.

Alternatively, click on the ‘See All Charts’ button at the bottom right of the Insert > Charts group…

Then find ‘Waterfall’ in the list of available charts and click OK.

### Identifying the Totals

Initially, our new waterfall chart will be a bit unorganized and will need some specific tweaks from before it will begin to take shape and be a better visual representation of our data. Note in the legend that there are three classifications of the data bars: ‘Increase’, ‘Decrease’, and ‘Total’.

Notice how ‘Increase’ bars rise in cascading fashion and ‘Decrease’ bars fall in the same way along the vertical axis according to the cumulative effect each has on the running total. Right now our Total Revenue, Operating Income, and Net Income totals are each shown as an ‘Increase’. We need to change them to each to a ‘Total’.

To do this, let’s first click on the Total Revenue bar and then right click.

Select ‘Set as Total’ and two things will happen. First, the color of the bar will change to match the legend as a ‘Total’ and second, the bottom of the bar will be anchored at zero. That way, the bar will be a visualization of the cumulative total income after increases due to Sales and Service income. Notice now how the top of the ‘Total Revenue’ bar is the exact same height as both the ‘Sales’ and ‘Service’ bars put together.

Now we just need to repeat this step for ‘Operating Income’ and ‘Net Income’. This results in the chart we ultimately want. Increases (income) are show as rising bars in blue while decreases (expenses) are shown as falling orange bars.  Note the cascading visual effect of the rising and falling bars that give the waterfall chart its name.

Now we can make any formatting changes we want just as we would with any other chart in Excel. Then it’s ready to disseminate across your organization, to your stakeholders, or for a presentation.

### Summary

The waterfall chart is a really nice addition to the family of charts offered in Excel 2016. As we have seen here, it provides a cascading visualization of data that includes increases and decreases while allowing us to see the cumulative effects on the running total in a very intuitive fashion. For the right kind of data, the waterfall chart can really bring things to life by providing an at-a-glance representation that anyone can appreciate.

The new charts in Excel 2016 can be used in a variety of scenarios. To learn more about all of them check out our guide here.

Before you begin your charting adventure, you should definitely read this comprehensive piece on what to do and what not to do when creating charts.

## Peltier Tech Charts for Excel Waterfall Charts

Many years ago, Peltier Tech introduced a tutorial that showed you how to make your own waterfall charts in Excel, the slow hard way. You can even read the latest edition of the Excel Waterfall Charts (Bridge Charts), which is only a few years old.

Peltier Tech came out with a demo Waterfall Chart utility a decade ago, because even though it’s possible to build your own waterfall chart by hand, it’s much easier to select your data and click a button than to follow a long protocol with many easy-to-omit steps.

Peltier Tech has had a commercial Waterfall Chart utility on the market since 2008. Over the years it has been upgraded to run in newer versions of Excel, and to run in Excel for Mac. It has been continually enhanced, several varieties of waterfall charts have been introduced beside it, and thousands of users now depend on it. The newest edition, released to coincide with Excel 2016 for Windows and Mac, is called Peltier Tech Charts for Excel 3.0.

When Microsoft finally came out with their own Waterfall Chart, Peltier Tech said, “Nice going, that’s a pretty good start.”

### Regular Waterfall

The data for the Peltier Tech Waterfall Chart is like that for Microsoft’s version, except the totals are left blank. When the program encounters a blank in the data column, it knows to calculate a total for that category. (If the totals in your input data are wrong, the chart Microsoft creates is also wrong.)

Select the data, then click the Waterfall dropdown button on the Peltier Tech tab > Custom Charts group of the ribbon, and click Waterfall.

You get a dialog that lets you select from a number of options.

When you click OK, the program inserts a worksheet with links to the data as well as columns of formulas that make sure the plot comes out right, plus a chart. Alternatively, you could have the program construct these formulas in the original worksheet. There is a checkbox and list box next to the output range, so that you can modify some of the options selected in the dialog.

The waterfall chart looks like on of the charts below. The waterfall treats the first value in the input data as a subtotal, for example, as a starting value that you are tracing through a set of intermediate operations to get a final value.

The second waterfall chart treats the first value in the input data as a change in value, in this case, as the first increase in revenue, coming from sales.

These charts are regular Excel charts, so you can reformat them, resize them, copy and paste them wherever you want. You need to be careful, however, because if you format away some of the hidden magic that makes them work as waterfall charts, you may break them and have to rebuild them.

The Microsoft waterfall charts are still not 100% user editable, depending on which build of Excel 2016 you use. On my main laptop I have the non-Office-365 version of Excel 2016, so it isn’t updated monthly with new features. I can format the colors of the Microsoft waterfall chart, and I can change the maximum and minimum of the vertical axis, but I am unable to modify the major tick spacing of the vertical axis. I also cannot modify the orientation of the horizontal axis labels. I’m sure that before long Microsoft will enable formatting of all of the elements of its waterfall charts, but for now, as I said, it’s still just a “good start”.

### Rotated Waterfall

So what are some of Peltier Tech’s other waterfall charts? One of them is a Rotated Waterfall Chart. It uses the same data as the “Regular” Waterfall Chart, but it uses horizontal bars instead of vertical ones. Most people prefer the regular orientation, but some customers asked for a rotated version, so here it is. Here is the “First Item is a Starting Total” version…

And here is the “First Item is a Change in Value” version.

### Stacked Waterfall

There is also a Stacked Waterfall chart. which breaks down each bar into smaller components. For example, you may want to see how several divisions contribute to your company’s performance.

The data is the same as for the “Regular” Waterfall Chart, except that there are two or more columns of values, one for each division.

Each division appears in the chart with its own color, so you can track it across the chart.

Note that it’s impossible to stack negative and positive values in a stacked bar chart. The Stacked Waterfall handles this situation by replacing a multicolored stacked bar with a single bar that merely shows the net for that category. There is a Split Bar Waterfall Chart in Peltier Tech Charts for Excel (not shown here) that takes this into account, splitting each bar vertically, to stack positive values in the left half of the bar and negative values in the right.

### Dual Waterfall

Another type of waterfall is the Dual Waterfall Chart, which allows you to compare two sets of data. The data set below shows the same data for two years.

The Dual Waterfall shows one set of data as filled bars, the other as transparent bars with a thick outline, so you can compare performance item by item across two sets of data.

### Paired Waterfall Chart

I wrote about the Paired Waterfall Chart recently in the Peltier Tech Blog. This is yet another way to track the cumulative effects of intermediate factors on a value or set of values.

I’m sure there are numerous additional Waterfall Charts that people could show you, but this extensive set is all that I’ve got.

### Peltier Tech Charts for Excel 3.0

All of these Waterfall Charts, and several other handy chart types, are included in the Advanced Edition of Peltier Tech Charts for Excel 3.0. The “Regular” and Stacked Waterfall Charts are included in the Standard Edition.

## Excel Waterfall Charts (Bridge Charts)

Waterfall charts are commonly used in business to show how a value changes from one state to another through a series of intermediate changes. For example, you can project next year’s profit or cash flow starting with this year’s value, and showing the up and down effects of changing costs, revenues, and other inputs. Waterfall charts are often called bridge charts, because a waterfall chart shows a bridge connecting its endpoints. A simple waterfall chart is shown below:

There is more than one way to create a waterfall chart in Excel. The first approach described below is to create a stacked column chart with up and down columns showing changes and a transparent columns that help the visible columns to float at the appropriate level. Under some circumstances this simple approach breaks down, and another approach is described.

## Floating Column Chart Data and Calculations

Here is some sample data showing how to construct a stacked-column waterfall chart. The left table has a column of labels, then a column with just the initial and final values, then columns with increases and decreases in value. This is the almost arrangement needed for making the chart, but I prefer to put these values into a single column as shown at right, and let the formulas sort it all out.

The first approach most people try is to use a floating column chart, that is, a stacked column chart with the bottom column in the stack hidden to make the others float. This range contains the calculations needed to make a floating column waterfall chart. After the two columns of labels and values, as in the top right table, there are calculated columns for the chart endpoints, the blank series that supports the floaters, and up and down values. Here are the formulas; the formulas in D3:F3 are filled down to row 7:

Cell C2: `=B2`

Cell C8: `=SUM(B2:B7)`

Cell D3: `=MIN(SUM(B\$2:B2),SUM(B\$2:B3))`

Cell E3: `=MAX(B3,0)`

Cell F3: `=-MIN(B3,0)`

The chart is pretty easy to make. Select A1:A8 (yes, include the blank top cell), hold Ctrl and select C1:F8 so both selected areas are highlighted, and create a stacked column chart.

Finish up with a little formatting. Set the gap width of the columns to 75%: format the series and on the Series Options or Options tab, change the value for gap width. Hide the Blank series by giving it no border and no fill, use colors that invoke positive and negative for the audience (usually green and red, which makes it tough for those with color vision deficiencies), remove the legend.

## Data Crossing into Negative Territory: Breakdown with Stacked Columns

That seems just too simple to be true. And in fact, for data like the following, which has negative as well as positive values, the simple floating column chart approach fails.

The green and red bars are the correct length, and as long as they are located completely above the horizontal axis, the chart is cool. But the formula computing the blank values is too simplistic, and Excel prohibits the floating bars from floating across the axis.

You can still use stacked columns, but you need to compute two sets of up bars and two sets of down bars, one set of each that lies above the axis, and one that lies below the axis. You also need to fix the formula for the blank series so it floats each column above or below the axis as necessary, or provides no float if the column spans the axis. Wow, so complicated.

But wait!

## Approach Using Up-Down Bars

There is another approach which takes a bit longer to chart, but the formulas are easier, and the columns in this case are able to float anywhere, even across the axis. This approach is based on line charts and a line chart feature called up-down bars. Up-down bars connect the first line chart value at a category to the last, like the open-close bars in a stock chart. In fact, Excel uses up-down bars as open-close bars in its stock charts. The up bars and down bars can be formatted individually.

The range below contains the calculations needed to make an up-down bar waterfall chart. After the two columns of labels and values, as above, there are calculated columns for the chart endpoints, and the values before and after adding an item to the previous total. Here are the formulas; the formulas in D3:E3 are filled down to row 7:

Cell C2: `=B2`

Cell C8: `=SUM(B2:B7)`

Cell D3: `=SUM(B\$2:B2)`

Cell E3: `=SUM(B\$2:B3)`

The chart-making process is a bit longer than for the floating column chart approach. Select A1:A8, hold Ctrl while selecting C1:E8, and create a line chart.

Select the Ends series and convert it to a column chart.

Select one of the line series, and add Up-Down Bars. In Excel 2007 and 2010, go to the Chart Tools > Layout tab, click the Up-Down Bars button, and select Up-Down Bars from the menu. In Excel 2003 and earlier, format the series, and check Up-Down Bars on the Options tab.

Hide the line chart series by formatting them to show no line and no markers, and format the up-down bar colors.

Remove the legend, and change the gap width of the column and the up-down bars to 0.75. This is easy for the column: simply format the series and on the Series Options or Options tab, change the gap width value. For the up-down bars in Excel 2003 and 2010, format one of the line chart series, and on the Options or Series Options tab, change the gap width value.

In Excel 2007 there is no way to change the up-down bar gap width from within the user interface, but you can do it with VBA. Press Alt+F11 to open the Visual Basic Editor. Press Ctrl+G (or go to View menu > Immediate Window) to open the Immediate Window. Type the following line of code into the Immediate Window (capitalization does not matter), then press Enter:

`ActiveChart.ChartGroups(2).GapWidth = 75`

Indistinguishable from the floating stacked column approach.

## Data Crossing into Negative Territory: No Problem with Up-Down Bars

This is the negative trending data set that messed up the floating columns. The up-down formulas work just fine.

Follow the same process. Create a line chart.

Change the Ends series to columns.

Add up-down bars to the lines.

Hide the lines and markers and format your colors.

Change the necessary gap widths, and delete the legend.

Perfect, no problem with spanning the axis with our floating columns.

## Stacked Columns for Positive and Negative Data

Earlier I said that it’s possible to use stacked columns for mixed values, and for completeness I’m going to describe the protocol here. If you don’t care to read about it, feel free to skip ahead, or to visit some of the other tutorials on this web site.

Here’s the start of the calculations for the stacked-column-across-the-axis approach. Here are the formulas for blanks above and below zero in D3 and E3:

Cell D3: `=MAX(0,MIN(SUM(B\$2:B2),SUM(B\$2:B3)))`

Cell E3: `=MIN(0,MAX(SUM(B\$2:B2),SUM(B\$2:B3)))`

Since at most only one of these has a non-zero value, we can replace the two formulas by a single formula which adds them together:

Cell F3: `=MAX(0,MIN(SUM(B\$2:B2),SUM(B\$2:B3)))+MIN(0,MAX(SUM(B\$2:B2),SUM(B\$2:B3)))`

Let’s consolidate the Blank column, and compute the other values. Here are the formulas; those in D3:H3 are filled down to row 7:

Cell C2: `=B2`

Cell C8: `=SUM(B2:B7)`

Cell D3: `=MAX(0,MIN(SUM(B\$2:B2),SUM(B\$2:B3)))+MIN(0,MAX(SUM(B\$2:B2),SUM(B\$2:B3)))`

Cell E3: `=MAX(0,MIN(SUM(B\$2:B3),B3))`

Cell F3: `=-MAX(0,B3-E3)`

Cell G3: `=MAX(0,H3-B3)`

Cell H3: `=MIN(0,MAX(SUM(B\$2:B3),B3))`

Select A1:A8, hold Ctrl while selecting C1:H8 so both areas are highlighted, and create a stacked column chart. Format the Blank series to hide it, format both Up series the same and both Down series the same. The result is almost identical to the up-down bar version, except here the horizontal axis is not hidden by the bars that cross the axis.

## Waterfall Chart with Intermediate Cumulative Totals

It’s easy to accommodate intermediate totals in a waterfall chart. Adjust your formulas so the Ends series has a cumulative total and no red or green bars at the appropriate category. Construction of the charts is the same as without the intermediate totals.

Here is how the data and charts appear for a stacked column waterfall chart with intermediate totals:

Here is how the data and charts appear for an up-down bar waterfall chart with intermediate totals:

## Compound Floating Columns

Sometimes a waterfall chart may have two or more items stacked within a floating column. In the data table below, we see that Items A and B both contribute to the accumulating value. This kind of illustration only makes sense if the Item A and B values are all positive or all negative. Otherwise the chart will be confusing.

This table contains calculated blank data for a stacked (floating) column waterfall:

Select the data in column A, then hold Ctrl while selecting the data in columns C through E, and insert a stacked column chart as before.

Hide the blank series and the chart is complete.

A compound waterfall chart with up to two elements can be made using the up-down bar approach. There can be only one set of up-down bars per axis, so one set is on the primary axis and the other on the secondary axis.

Here is how the data is arranged for up-down bar waterfall. Here are the formulas that generate the table, which are filled down as far as shown:

Cell E3: `=SUM(B\$2:D2)`

Cell F3: `=E3+C3`

Cell G3: `=F3`

Cell H3: `=G3+D3`

Select A1:B8, then hold Ctrl while selecting E1:H8 so both regions are highlighted, and insert a line chart.

Move the Before B and After B series to the secondary axis.

Select the secondary vertical axis (right of chart) and press Delete.

Convert the Ends series to a column.

Select either Before A or After A and add Up-Down Bars.

Select either Before B or After B and add Up-Down Bars.

Format the up-down bars and adjust gap widths.

If the endpoints are also split by item, the data looks something like this.

Here the blanks have been calculated for floating columns:

Construction of the chart is the same as before, starting with a stacked column chart, except there is no Ends series.

Here is the finished chart with Blanks made transparent.

Here the Before and After A and B values have been calculated for up-down bars:

Construction of the chart is the same as before, starting with a line chart, except there is no Ends series which must be converted to columns. Before and After A stay on the primary axis, while Before and After B move to the secndary axis.

Add two sets of up-down bars.

Hide the legend, hide the lines and markers, and format the up down bars. The chart looks just like that using stacked columns.

Because there are only primary and secondary axes in an Excel chart, the up-down bar approach can only support a two-item per stack waterfall chart. The stacked column approach can support many more items: the limitation is imposed by the legibility of the resulting chart.

Here is data for a waterfall chart with three items per stack (you could add enough items to make your chart illegible).

The data has a calculated Blank column to float the three columns.

Select the data in column A, and hold Ctrl while selecting the data in columns C through F, and insert a stacked column chart.

Hide the Blank series, and hide the unwanted legend entries (click once to select the legend, and click a second time on the legend entry, and press Delete).

## Waterfall Charts in Peltier Tech Charts for Excel 3.0

This tutorial shows how to create Waterfall Charts, including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, is prone to error, and becomes tedious.

I have created Peltier Tech Charts for Excel 3.0 to create Waterfall Charts (and many other custom charts) automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. This is a commercial product, tested on thousands of machines in a wide variety of configurations, Windows and Mac, which saves time and aggravation.

The program makes regular waterfalls …

… waterfalls with values that end up spanning the horizontal axis …

… rotated waterfalls …

… stacked waterfalls …

… and a couple obscure waterfall varieties as well.

## Add Percentages on the Secondary Axis

I received an email from a user of my Waterfall Chart Utility, who wanted to add a secondary axis showing percentages corresponding to the values on the primary axis. This is really just a matter of applying straightforward algebra to compute the secondary axis scale parameters from the primary axis scale parameters and the two values one wants to line up. I described a very similar problem in Align X Axis to Y=0 on Two Y Axes, which solved the special case where zero on the primary axis was to coincide with zero on the secondary axis.

The user’s data looks like this (I’ve changed the labels and values).

The waterfall chart looks like this, after minor modifications.

The user wanted the secondary axis to scale from 0% at \$0, to 100% at the top of the revenue bar, or \$840,000. The simplest way to do this is to set up a table like the one below, which contains the primary axis maximum and minimum, the primary and secondary axis values which have to line up, and spaces for the secondary axis maximum and minimum.

I changed the view to show formulas in the cells, by holding Ctrl and clicking the button with the tilde (~). The formulas in C12 and C14 are really straightforward once you’ve set up a table like this.

I’ve switched back to normal view, by again holding Ctrl and clicking the ~ button. The calculated secondary axis scale parameters are now shown.

In order to have a secondary axis in an Excel chart, you need a series to put onto the secondary axis, in addition to any series that belong on the primary axis. The waterfall chart has a bunch of series on the primary axis, but we’ll add a new series for the secondary axis. Right click the chart, choose Source Data, and click on the Series tab. Click the Add button, enter a name (“Dummy”), and for values, enter ={0}, which is an array consisting of the single element zero. This results in a series which should remain hidden.

This new series is added to the primary axis. Select the new series, by using the Chart Menu’s Select Chart element dropdown, or simply by selecting any chart series (actually, any chart element) and cycling through all chart elements until the Dummy series is selected.

Once the Dummy series is selected, press Ctrl+1 (numeral one) to open the Format Series dialog. On the Axis tab, click Secondary. The chart now has a secondary axis.

Format the secondary axis: right click on the axis and choose Format Axis. On the Scale tab, enter the numbers calculated above. If necessary, adjust the number format of the axis labels on the Number tab.

You should also lock in the primary axis scale parameters, because any change Excel makes under Auto mode will break the calculations made above. Right click the axis, choose Format Axis, and on the scale tab, uncheck the Auto boxes in front of Minimum, Maximum, and Major Unit.

The result is a chart with percentages that line up as desired.

An alternative might be to scale the secondary axis from 0% to 100%, and the primary axis from \$0 to \$840,000.