Update: Jon Peltier sells an add-in that replaces all of these steps: Peltier Tech Charts for Excel 3.0
Today's episode is a continuation from Episode #1091 [ Stacked & Clustered – 1091 – Learn Excel from MrExcel Podcast ] that shows how to create a chart with Two (2) Series Stacked and a Third as a Cluster. But in Episode #1577, Bill is going to show us how to stack Two (2) Series of 2, side by side. Follow along to see how it's done.
Today's episode is a continuation from Episode #1091 [ Stacked & Clustered – 1091 – Learn Excel from MrExcel Podcast ] that shows how to create a chart with Two (2) Series Stacked and a Third as a Cluster. But in Episode #1577, Bill is going to show us how to stack Two (2) Series of 2, side by side. Follow along to see how it's done.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast.
Cluster Stack Chart II.
Hey, today's question sent in by Rota.
Rota, actually saw the first cluster stack podcast where we had 1, 1 bar next to 2 that were stacked on top of each other and Rota's data set is a little bit different.
Rota needs to stack A1 and A2 in one stack and then right next to B1 and B2.
So, a little bit different approach here the first thing I did was I took the data and I separated it out.
for each month I wanted to have three columns, but I only wanted to label the first one.
So, here's January and then two blanks and then February and two blacks margin two blanks April and two blanks and so on and I used little hor-- I've never used HLOOKUP always laugh, I never use HLOOKUP, but okay fine I use an HLOOKUP to pull the 29 and the 26 and then I actually pulled the 20 to 25 right here and then cut and paste it and then once I had this whole block because I carefully use the dollar signs throughout I was able to copy and then paste and then paste and then paste.
All right! So now, we update the data here and we get the data in the weird format down here.
All right! So now, that we have all that let's select the data and see if we can make this chart, will do Insert, Column, it's going to have to be a Stacked Column.
All right and we start out with this look and now let's see if we can make it look a little bit better.
First thing I'm going to do is, I'm going to take this item and I'm going to change the color of it back here, Shape Fill, will go with a light green and then a dark green on top to kind of tell people that those are associated then here we have a dark purple and we'll go with a light purple on the bottom.
So, again just giving the the visual clue that those are associated with each other.
Now, I want to try and make it more clear that these two are for January and so, one thing that's annoying me down there is those tick marks.
I'm going to select their Control+1 to format and Major tick mark type, I'm going to say, None, click Close.
All right! So now, we don't have any tick marks down there.
I'm going to right click on any one series format data series and we're going to change the gap with to No Gap that's going to get all of the columns right next to each other.
So, here you know, we have the first January the second January and then for the third January it's all blank.
So, it appears as no column at all, no column at all I think that one other thing I would do here is, I would take the legend and move it to the top. Layout, Legend, Show Legend at Top.
All right, so, this is what Rota was looking for it's probably, pretty close, is it an absolute cheap because we're putting the blank columns in yeah, yes it is, but it is a way to achieve I think what she was looking for.
Oh! Hey, I want to think Rota for sending that question in and I wanna thank you stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel form MrExcel podcast.
Cluster Stack Chart II.
Hey, today's question sent in by Rota.
Rota, actually saw the first cluster stack podcast where we had 1, 1 bar next to 2 that were stacked on top of each other and Rota's data set is a little bit different.
Rota needs to stack A1 and A2 in one stack and then right next to B1 and B2.
So, a little bit different approach here the first thing I did was I took the data and I separated it out.
for each month I wanted to have three columns, but I only wanted to label the first one.
So, here's January and then two blanks and then February and two blacks margin two blanks April and two blanks and so on and I used little hor-- I've never used HLOOKUP always laugh, I never use HLOOKUP, but okay fine I use an HLOOKUP to pull the 29 and the 26 and then I actually pulled the 20 to 25 right here and then cut and paste it and then once I had this whole block because I carefully use the dollar signs throughout I was able to copy and then paste and then paste and then paste.
All right! So now, we update the data here and we get the data in the weird format down here.
All right! So now, that we have all that let's select the data and see if we can make this chart, will do Insert, Column, it's going to have to be a Stacked Column.
All right and we start out with this look and now let's see if we can make it look a little bit better.
First thing I'm going to do is, I'm going to take this item and I'm going to change the color of it back here, Shape Fill, will go with a light green and then a dark green on top to kind of tell people that those are associated then here we have a dark purple and we'll go with a light purple on the bottom.
So, again just giving the the visual clue that those are associated with each other.
Now, I want to try and make it more clear that these two are for January and so, one thing that's annoying me down there is those tick marks.
I'm going to select their Control+1 to format and Major tick mark type, I'm going to say, None, click Close.
All right! So now, we don't have any tick marks down there.
I'm going to right click on any one series format data series and we're going to change the gap with to No Gap that's going to get all of the columns right next to each other.
So, here you know, we have the first January the second January and then for the third January it's all blank.
So, it appears as no column at all, no column at all I think that one other thing I would do here is, I would take the legend and move it to the top. Layout, Legend, Show Legend at Top.
All right, so, this is what Rota was looking for it's probably, pretty close, is it an absolute cheap because we're putting the blank columns in yeah, yes it is, but it is a way to achieve I think what she was looking for.
Oh! Hey, I want to think Rota for sending that question in and I wanna thank you stopping by.
We'll see you next time for another netcast from MrExcel.
