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.