Stacked column chart with optional trendline

posted Oct 4, 2013, 8:01 AM by Krisztina Szabó   [ updated Oct 25, 2013, 2:27 PM ]
[as she holds a candle holder with 3 unlit candles]
Frau Blücher: Stay close to the candles. The stairway can be... treacherous.

by The FrankensTeam

A recent post presented the Floating bar chart where the bars “sit” on a trendline. Joel asked us to create an improved version making possible to put bars below and above the line.
The first version where stacked bars were combined with xy scatter line is easily adaptable for this challenge. We simply added more data series to the stacked bars and re-calculated the y coordinates of the line. Now you can choose where do you want to draw the line: it could be placed above either of the series, so you can highlight the trend of any summation level of the stacked data.
Download the file.

Naturally you can color the “invisible” series, so you will see a normal stacked chart. (You will have to delete and add the legend again to capture the invisible serie which was deleted from the original legend.)

The data table is dynamic: if you add new rows to the bottom, it will appear on the chart. If you need more columns, you have to add it manually to the chart as new stacked bar series. In this case, take into consideration the order of the series - it must be the same as in the data table to be able to position the base line correctly.