Hey welcome back to another MrExcel netcast I'm Bill Jelen for today’s question, great question send by Jeff from Akron. Jeff wants to create a chart like this where he has three series and the first two series are stacked but the third series is next to the first two series. So this is a weird kind of combination chart and said oh Jeff, I don’t know I think there might be way to do it.
And my first thing inclination was to take the data and use this trick. I was going to create a stacked column chart and that’s stacks all the three of them. And then select the item that I wanted to appear next to the others and I want to change the chart type of just this item now. Promise I can't change the chart type when it’s on the same axis, first thing I have to do is move it to another axis like I press control 1, what came out here is a layout tab and choose format selection. Move that to the secondary axis and now it’s still selected and I can come back here and change the chart type of that one series to a column chart.
The problem though is that we then have the series two plotted directly on top of series one A and series B. So I'm go back to a trick that someone sent me awhile ago and while we actually insert a couple of blanks series that was called blank one, blank two. And we’ll make all of these be zero and now let me try that again where I create a chart from this data. So insert column again we’ll make it be stacked initially and we got have to change the row and column. So it quarters across the bottom alright now, same initial trick.
Some here to the series one and we’ll change that to the secondary axis. But now I have to do that for the blank series as well, so we go to layout series blank, format selection, secondary axis and then series blank two, format selection, secondary axis- excellent. Alright now select the series two, change the chart type back to a cluster type. Okay now what we do? Because we have these blanks series here, what it is doing and actually let put in some data so you can see what’s it’s doing it’s a par of 20 there straight across the board.
It’s putting blank little zeroes there and those are taking up space which is moving our series over. So I will go back to zeroes, now to fix this what we have to do is choose the series one format. And we are going to increase the gap width now, let’s try about 207, what are we trying to do is as we increase the gap the column gets narrower. And I am trying to get back all the about the same width as the other column, looks where about right there. So now we’ re almost there but not quite there because look this 450 should be larger than the sum of those two number. But that is not working out and the reason is the scales are of different magnitude on the left and right.
So at this point, I am go in and right click say format axis and instead of allowing to be automatic. And let’s say that it is fixed I am going from zero and also fix going up to lets say 750 and then we also want to format the left axis. So that will have to fixed going from zero and it has to be going up to the same number. Now the hassle with this is, if we ever get larger numbers we have to come back in and change the axis to make that work.
Alright now a couple of things here we have the blank one and the blank two over the legend how do we get rid of that. First click, selects the whole legend, second click goes for just like that one and press delete and the other one like two press delete and there you have it, a chart with a stacked first two series and then a clustered third series with all the answers.
Also notice here I went 1 B and 1A to get that legend to appear in the right order 1A 1B not your wide goes in that particular method. When we used two axes but nit does, for the last thing, okay now I see that these two axes are here. Let’s just format this one like that- I can actually come in and change the font color back to white and so we seize this there. And good to go, hey thanks to Jeff in sending that question and thanks to you for stopping by, we’ll see you next time for another netcast from Mr Excel.
Transcription by:
Scribe4you Transcription Services