If you’re currently using Excel 2016, you know that the Waterfall chart is now a default chart type. You can select your data and insert the chart as any other line, bar, or area chart. One problem I’ve found, however, is that the formatting options are somewhat limited. You can’t change the color of the horizontal axis, you have limited formatting options of the linking lines, and you can’t combine it with other chart types.

My 2010 Excel ebook includes a tutorial on how to create a Waterfall chart using a combination of stacked column charts and line charts (it also works in 2016). With that approach, you have control over all of these chart elements that you can’t control in the new default version.

In this post, I want to expand the basic Waterfall chart to convert the basic connecting lines to arrows. It’s a slightly different approach than my approach in the ebook because I want to do something slightly different. A workshop attendee asked if this was possible, so I took it as a (fun) challenge.

As in the ebook, I’ll start with a stacked column chart and apply a *No Fill *color to the bottom series.

Now, instead of adding lines, I’ll add a series of scatterplot points that will be positioned at the top-right corner of each bar. Horizontal error bars will be added to each marker and extend to the next column.

The x-values are set to the position of each column (1, 2, 3,…) plus 0.2. Adding 0.2 puts the point along that right edge.

The y-values are set to the height of each column, but I also subtract 0.2; otherwise, the point is positioned just slightly higher than the top of the column.

With the points positioned on the top-right corner of each column, horizontal error bars are added to extend to the next column. You can see what happens when the (X, Y) coordinates are changed from, for example, (1, 40) to (1.2, 39.5).

The length of each error bar is set to 0.6 and are formatted to have an arrow head at the end position (I couldn’t tell you exactly why 0.6 works, but, well, it does).

That’s pretty much it. A Waterfall chart with arrows that help guide the reader from one column to the next. You can download my Excel file here.

Here is where 0.6 error bar length comes from.

Your gap width is by default 150%. The horizontal distance from the middle of the one category to the next (e.g., Region A to Region B) is 0.5 bar + 150% or 1.5 of a bar for the gap + 0.5 of the next bar. The arrow has to span the gap, so it has to be 1.5 bars wide, and the whole distance is 2.5 bars.

1.5 / 2.5 = 0.6.

You are a man above men. Thanks.

When I use your template and add more columns I can’t seem to get the error bars to populate on new “Regions”

Hi Dan,

Do you mean add new rows? Are you adding more “Regions”?

Thanks,

Jon

Dan: For each “Regions” bar, you need a “Connectors” point.

Hi “Jons”,

I’ve added new connector points but the graph doesn’t seem to be referencing them. How can I see what is referencing the connectors range?

Thanks

Dan

Select the series. In the formula bar you will see the series formula, which looks like this:

=SERIES(Sheet1!$J$3,Sheet1!$G$4:$H$19,Sheet1!$J$4:$J$19,2)

The series name is in Sheet1!$J$3, the X values in Sheet1!$G$4:$H$19, and the Y values in Sheet1!$J$4:$J$19. You can also see these ranges highlighted in the worksheet: name in red, X values or category labels in purple, and values in blue (Excel for the Mac uses different colors, naturally). The attached picture is from a random file I had open just now.

You can edit the ranges in the formula bar, or drag the corners of the colored highlights, to change the data in the chart.

Attached image:

Ah ha! Thank you, very helpful!