Update: PeltierTech sells an add-in to easily create this chart and many variations: Peltier Tech Charts for Excel 3.0
Jeff from Akron asks how to create a column chart where two series are stacked, but a third series is clustered. Episode 1091 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Jeff from Akron asks how to create a column chart where two series are stacked, but a third series is clustered. Episode 1091 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast.
I’m Bill Jelen.
Amazing.
We start out with massive amounts data. We say, “How’re we going to analyse this?” Well, let’s fire up a Pivot Table and see if we can solve this problem.
Hey.
Welcome back to another MrExcel netcast.
I’m Bill Jelen.
Boy, today’s question.
Great question sent in by Jeff.
Jeff’s 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 I said, “Oh, Jeff, I don’t know.
I think there might be a way to do it.
And my first inclination was to take the data and use this trick.
I was going to create a stacked column chart.
And so that stacks all 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 for just this item.
Now, the problem is I can’t change the chart type when it’s on the same axis.
So first thing I have to do is move it to another axis.
So I can press Control 1.
I’ll come here to the Layout tab and choose Format Selection.
Move that to the secondary axis.
Alright.
And now it’s still selected.
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 2 plotted directly on top of Series 1a and Series 1b.
So I’m going to go back to a trick that someone sent me a while ago, where we actually insert a couple of blank series.
So we’ll just call this ‘Blank 1’, ‘Blank 2’.
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, okay.
We have to change the row and column so I have quarters across the bottom.
Alright, now.
Same initial trick.
Come here to the Series 1 and we’ll change that to the secondary axis.
Alright, 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 2.
Format Selection, Secondary Axis.
Excellent.
Alright.
Now, select the Series 2, change the chart type back to a cluster type.
Ah, okay.
Now, what did we do?
Because we have these blank series here, what it’s doing and I’ll actually… Let’s put in some data so you can see what it’s doing.
Just put 120 there straight across the board.
Ah! It’s putting blank little zeros there and those are taking up space, which is moving our series over.
So I’ll go back to zeros.
Now, to fix this, what we have to do is choose the Series 1, Format, and we’re going to increase the gap width.
Now let’s try about 270.
What we’re trying to do is, as we increase the gap, the column gets narrower.
And I’m trying to get that column to be about the same width as the other column.
Looks like we’re about right there.
Okay.
So, now.
We’re almost there, but not quite there.
Because look, this 450 should be larger than the sum of those two numbers, but that’s not working out.
And the reason is the scales are of different magnitude on the left and right.
So at this point, I’m going to go in and right-click, say “Format Axis”.
And, instead of allowing it to be automatic, I’m going to say that it’s fixed going from zero.
And also fixed going up to, let’s say, 750.
Good.
And then we also want to format the left axis.
So that I want to be 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’re going to have to come back in and change the axes to make that work.
Alright, now.
Couple of things.
Here we have the Blank 1 and Blank 2 over on the legend.
How do we get rid of that?
First click selects the whole legend.
Second click, though, is going to select just that one.
And press Delete.
And the other one, Blank 2.
Press Delete.
And there you have it.
A chart with a stacked first two series and then a clustered third series with all of the answers.
Also notice here I went 1b and 1a to get that legend to appear in the right order, 1a, 1b.
Not sure why it goes in that particular method when we use two axes, but it does.
Oh boy, the last thing.
Okay now I see that these two axes are here.
Let’s just format this one.
I can actually just come in and change the font color back to white so no-one even sees that it’s there and good to go.
Hey, thanks to Jeff for sending in that question and thanks to you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Thanks for stopping by and we’ll see you next time for another netcast from MrExcel.
I’m Bill Jelen.
Amazing.
We start out with massive amounts data. We say, “How’re we going to analyse this?” Well, let’s fire up a Pivot Table and see if we can solve this problem.
Hey.
Welcome back to another MrExcel netcast.
I’m Bill Jelen.
Boy, today’s question.
Great question sent in by Jeff.
Jeff’s 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 I said, “Oh, Jeff, I don’t know.
I think there might be a way to do it.
And my first inclination was to take the data and use this trick.
I was going to create a stacked column chart.
And so that stacks all 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 for just this item.
Now, the problem is I can’t change the chart type when it’s on the same axis.
So first thing I have to do is move it to another axis.
So I can press Control 1.
I’ll come here to the Layout tab and choose Format Selection.
Move that to the secondary axis.
Alright.
And now it’s still selected.
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 2 plotted directly on top of Series 1a and Series 1b.
So I’m going to go back to a trick that someone sent me a while ago, where we actually insert a couple of blank series.
So we’ll just call this ‘Blank 1’, ‘Blank 2’.
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, okay.
We have to change the row and column so I have quarters across the bottom.
Alright, now.
Same initial trick.
Come here to the Series 1 and we’ll change that to the secondary axis.
Alright, 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 2.
Format Selection, Secondary Axis.
Excellent.
Alright.
Now, select the Series 2, change the chart type back to a cluster type.
Ah, okay.
Now, what did we do?
Because we have these blank series here, what it’s doing and I’ll actually… Let’s put in some data so you can see what it’s doing.
Just put 120 there straight across the board.
Ah! It’s putting blank little zeros there and those are taking up space, which is moving our series over.
So I’ll go back to zeros.
Now, to fix this, what we have to do is choose the Series 1, Format, and we’re going to increase the gap width.
Now let’s try about 270.
What we’re trying to do is, as we increase the gap, the column gets narrower.
And I’m trying to get that column to be about the same width as the other column.
Looks like we’re about right there.
Okay.
So, now.
We’re almost there, but not quite there.
Because look, this 450 should be larger than the sum of those two numbers, but that’s not working out.
And the reason is the scales are of different magnitude on the left and right.
So at this point, I’m going to go in and right-click, say “Format Axis”.
And, instead of allowing it to be automatic, I’m going to say that it’s fixed going from zero.
And also fixed going up to, let’s say, 750.
Good.
And then we also want to format the left axis.
So that I want to be 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’re going to have to come back in and change the axes to make that work.
Alright, now.
Couple of things.
Here we have the Blank 1 and Blank 2 over on the legend.
How do we get rid of that?
First click selects the whole legend.
Second click, though, is going to select just that one.
And press Delete.
And the other one, Blank 2.
Press Delete.
And there you have it.
A chart with a stacked first two series and then a clustered third series with all of the answers.
Also notice here I went 1b and 1a to get that legend to appear in the right order, 1a, 1b.
Not sure why it goes in that particular method when we use two axes, but it does.
Oh boy, the last thing.
Okay now I see that these two axes are here.
Let’s just format this one.
I can actually just come in and change the font color back to white so no-one even sees that it’s there and good to go.
Hey, thanks to Jeff for sending in that question and thanks to you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Thanks for stopping by and we’ll see you next time for another netcast from MrExcel.
