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!
Very cool!
Two very hair-splitting questions, aren’t the labels wrong? Region C should be 90 (20 +70), Region D should be 100 (90+10)?
And then second, why are the arrows moving around in height? Is that an artifact of publishing to the site, or is that something slightly off in the coding in the spreadsheet? In two of the charts the arrow from region B to region c is a bit too low, and then in one chart the arrows from region C to D and from region D to the total are both a bit low.
Hi Devin, Yes, I think you’re correct (and my spreadsheet is incorrect).
As for the arrows, I do see the B-> C arrow being a little low in two of the graphs. That’s probably just me putting the point too low. The other ones may be an artifact of converting from the Excel spreadsheet to PNG image format.
Thanks for asking!
The elements in charts are always positioned +/- a pixel, so a slight mismatch is not unexpected, and converting to an image may make minor unexpected changes. But Jon used Y values 0.5 lower than he probably should have for the XY series that anchors the arrows.
This is a great chart! I like the way the arrows help to illustrate the flow of data.