Overlap-Separate columns in clustered and stacked bar graph

[pecosvil]

I am using Excel 2007.
I wanted to create a bar graph in which series A and B are stacked, and next to it there is a series C (imagine that A are women from the US, B are men from the US and C are HIV positive cases in the US). So what I am trying to do is some kind of combination of stacked bar graph with clustered bar graph.

I have an horizontal bar graph in which I created two series (A and B) that are stacked and a third serie (C) that is not stacked. In order to do that, I followed the following approach.

1) I created a bar stacked graph with the three series.
2) Then I selected serie C and I set that it's shown on the secondary axis.
3) I set the secondary axis scale to match the principal axis.

The problem is that the series are overlapped, and even if I try to separate them (going to series C format settings and changing the overlap), it doesn't do it (tip: to visualize things better, I recommend to go to series C and set the Gap With to 300%).

If I try with series A and B settings, overlap does work, but it just separates every series (A from B, B from C, C from A) and I don't want to do that!

Any help?

 

[Gerald Higgins]

Hi.

The trick to this is in how you layout your source data.

You need something like this

Row.........Women......Men......HIV+
1..............3..............4..............
2........................................5...
3.............................................(blank row to space out the bars)
4..............3.5............4.2...........
5........................................6...
6.............................................(blank row)

There's more detailed explanations of this approach on Jon Peltier's great site.

 

[pecosvil]

Thanks, it worked fine, althought it was kind of annoying to create those empty spaces. I guess there was no other way...
Thanks again!