Stacked Column Chart with independent time series

**Lloyd Blankfein** · 07-03-2018, 02:01 PM

Hi,

Please see the attached Excel. I like to build a Stacked Column Chart from the range A1:H8.
Cells A1; C1; E1; G1 should be the labels.

The result should look like the draft in range J2:P23.

Is it possible to realize this in Excel (cell formula / no VBA)?

Thanks a lot!

Llyod

**dflak** · 07-04-2018, 12:50 PM

Kludgy at best. I made 4 separate charts, gave them invisible backgrounds. Saved the vertical axis on the first chart added at horizontal axis label to each chart. Gave it a 500 gap. dragged the charts on top of one another. Put a white rectangle behind all. I didn't quite match up the lines. I had the thought of adding data labels only on the last chart.

There is still some work to be done for cleanup. Yes, it can be done but poorly.

**Lloyd Blankfein** · 07-04-2018, 05:15 PM

Dear dflak,

Thank you so much for that effort! That helps for the moment. As you said, this must be cleaned up a bit, but the template is super useful for me.
Finally I'll use VBA to add the data labels (names, values) automatically, as doing this manually is pain for the overlapped charts.

THANKS A LOT!

Best,
Lloyd

**dflak** · 07-05-2018, 08:43 AM

If you are going to use VBA, then maybe there is a way to "munge" the data so that it plots automatically. Let me give that a thought. Also I'll check my archives, I might have a data labeling macro there.

**dflak** · 07-05-2018, 04:21 PM

Well, this has been a fun project.

First of all, if you arrange the data "horizontally" as in rows 7:10 on Sheet2, then you can get the groups as stacked columns all on one chart.

Then after a bunch of macro recording I came up with a way to assign the data labels.

Is this what you want? The only thing to do from here is a short VBA to read the data in the format that you have it in sheet 1 and put it into the format in sheet 2.

Can I assume that you will always have 4 groups and a maximum of 7 items in a group?