Combined Stacked and clustered bar chart on the same graph

[Div]

I wonder if someone has a trick to do this...

I'm trying to show market saturation on a graph. For each category I want two bars:
1. one (stacked bar) showing revenue from my company and competitors
2. one (clustered/standard bar) showing the total potential of the market

so it looks like this:

uuuuuuuucccccccccccccccccc
pppppppppppppppppppppppppppppppppppppppppppppppppppppppp

where u=us, c=competitor, p=potential

I have 30 categories I want to plot like this on one graph.

I've had a good look through the different chart types, and can't see any way to combine the two types on one chart.

Any suggestions?

Thanks,

Dave.

 

[Jon von der Heyden]

Hi Div

I've just had a little play. At first I thought I could merely change the chart of type of the 'p' series but it seems to 'un' stack all the series.

So as another option, perhaps 1st create the stacked chart with the 'uc' series'. Then create another for the 'p' series. You can overlay this chart over the other. Change the plot area and chart area background to transparent (none). You also need to make sure scale etc. are identical.

Bit messy but it's the only way I construct what you were after.

Regards
Jon

 

[Div]

Hi Jon,

Yeah - this is what I came up with too - running the graphs through PhotoShop, but it's not very repeatable. The only other thing I can think to do is minus the Us and the Cs from the Ps and just have one stack:

uuuuuuuuuuuuuccccccccccccccccpppppppppppppppppppppppppp

and title the Ps as "remaining potential". if that makes any sense...

Dave.

 

[provincial]

Hi All,

I achieved the result as follows:
Graph type = Stacked Bar
Series 1
cell N5 empty
cell N6 containing value to plot (YOUR 'p' VALUE)
Graph Series Values:='JobCostReport($)'!$N$5:$N$6
Series 2
cell O6 containing value to plot (YOUR 'u' VALUE)
Graph Series Values:='JobCostReport($)'!$O$6
Series 3
cell B6 containing value to plot (YOUR 'c' VALUE)
Graph Series Values:='JobCostReport($)'!$B$6
Series 4
cell D6 containing value to plot
Graph Series Values='JobCostReport($)'!$D$6
The u and c will stack adjacent to the p bar.

Good luck
Paul

 

[Jon von der Heyden]

Yup, I tried as per your instructions and it worked a treat. Good job Paul.

 

[Jon Peltier]

For more general situations, the examples linked from this page may be helpful:

http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html