I am trying to create a combo chart using a Column Chart as Primary and a Line Chart with a separate axis for scale. This is easy.
The trick that I'm trying to accommodate is that I need to have the rows of data on the Column section be displayed as stacked columns instead of side-by-side.
Is there a solution built-in, or do I need to do two separate charts and have one be transparent and then overlap?
How about this, using the 2-axis chart to make the labels on both sides:
Enabling the Y1 axis makes the two charts align properly.
The only two-axis option combines a bar chart with a line chart. There's no option (at least that I'm aware of) to combine a stacked bar with a line chart.
Therefore I think your option is the transparent/overlay solution you've already thought of.
If you need a right-side Y axis, I'm afraid it might not be all that good with the charts overlaid, either. To align the points of the line with the center of the bars, the 2-axis chart and stacked bar chart aren't in the exact same place on the sheet. This causes some problems with the gridlines. If you use gridlines for the stacked bar chart, they overlap with the Y axis labels on the right (as shown below). If you instead use gridlines for the 2-axis chart, they don't reach all the way to the Y axis on the left. If you chart an extra row for the 2-axis chart (and use the stacked bar grid lines) to make some extra room, the Y axis labels on the right become too far from the chart (plus you'll have to realign the charts whenever you include more data). I have not yet seen a way to make it look right.
If you use normal line chart for the line so the Y axis is on the left and both charts have the same Ymin and Ymax, there is no problem:
Thanks,
This is, unfortunately, what I have found as well and isn't going to be just right. On the two axis solution, one further workaround, is to take the "background" graph that you've used above with the gridlines that are too long, and, before you overlay the top graph, add a box with a background color and no outline to block the right hand edges of the gridlines and then apply the top overlay... but this is clearly NOT a clean - or frequently reproducible -solution.
Combo chart: Stacked columns & line with secondary axis
