Stacked column chart with more trendlines

posted Oct 14, 2013, 7:00 AM by Krisztina Szabó   [ updated Oct 14, 2013, 10:48 AM ]
Elizabeth: [to The Monster] Honey, did you see I put another hamper in the bathroom? This one's for your shirts, the other's just for socks and poo-poo undies.

by The FrankensTeam
Seems this column+xy-line combination chart is a popular topic. After sharing the last one about optional trendlines, we got a challenge from Hessel to create the chart with more lines. Well, Excel has a built-in feature for it, the marker line, but it always appears for all the series. So, the idea is to create a chart where you can set above which series you would like to draw lines.
 
The base of the technique is similar to what we used for slopegraphs: we draw one xy scatter line only. The parts lying above the columns are connected with vertical lines on the side. And here is the trick: they are connected only on the left-hand side, so at value 0.25. This value will not appear on the column chart. (Why? Because the mid-point of the column is always positioned to the integer numbers starting from 1. Consequently the minimum of the axis of a column chart is 0.5)

The chart is a combination of stacked column and xy scatter line chart types. There are some named formulas in the background to calculate the coordinate points for the line. Making it work is very easy: you simply write 1 above the table if you want to draw a line above the columns, or write 0 if you do not want line there - as you can see on the below screenshot:


You can add new data to the chart similarly as you add new data to a normal stacked column chart. Simply write the header and the numbers to the next empty column. You have to add a new data series to the chart referring to the new data. You may need to change the chart type of this new series to stacked column, but you have nothing to do with the line - if you write 1 above the new column, the line will automatically appear at the top of the series.

If you like this trick, you can download the example file.

Related stuff: