Floating bar chart

posted Aug 9, 2013, 7:02 AM by r   [ updated Oct 8, 2013, 2:53 PM by Krisztina Szabó ]
Dr. Frederick Frankenstein: Sit down, won't you?
[Igor sits on the floor]
Dr. Frederick Frankenstein: No, no, up here.
[Igor gets up onto a stool]

by The FrankensTeam

The question was raised in the Excel Gurus community on Google+ by Rahim. He wanted a chart where the bars start from different values, and a trend-line connects the starting points.

We created two different combined charts. Both solutions are dynamic, you can add new data to the data tables.

First solution: stacked column chart with xy scatter


Download the file

The trick is how to position the line correctly. You need to know the coordinates of the bottom edge of the columns. It is easy to calculate based on the gap you set in the series option of the column chart. We choose 100% gap - it means the width of the gap is equal to the width of the column.
The mid-point of the column is always positioned to the integer numbers starting from 1, so with 100% gap the left-end of the first column is 0.75, the right-end is 1.25.
Now we can build a series for the x and y coordinates. We use named formulas for doing it, so no help columns needed on the sheet.

Second solution: stacked column chart with area


This version is a little bit more tricky. The first trick is only a cheating with the number of data points and the gap between the columns. The second trick is how to assign data labels using 2D named formula. If you are interested in the details, you can check it in the file.