Hey, welcome back to the MrExcel netcast, I’m Bill Jelen. Today’s question sent in by Michael from Kansas City.
Michael wants to build this cool stocked floating column chart over here. What is he’s saying like this section in here in the red is saying that the first quartile range from this level of 11.46 up to the third quartile 14.5 all right, so that’s the data that he has. He wants to try and create this chart. Let’s figure out if we can pull that off.
I’m going to build some formulas down here in Excel and I’ve already put the formulas in just change the column, and the way I think about this is there’s an invisible series that I’m going to go in the chart, and then it had all the pink, yellow and green and blue series need to be, so the invisible series well, that’s pretty easy that’s just the low value.
And then for the first series, the pink series, the first series we’re going to see if the Delta between the low in the third quartile and similar formulas throughout. I have to build this one at a one, and then copy them over okay, now let’s build the chart from that whole series. Let’s go to insert, column choose the 3D column chart.
Now, we need to change this so that when we have the different colors over here in the legend and the years going across the bottom. All right, now that we’ve done that we want to choose the invisible series, so I come here to the layout tab and choose series invisible. Now, that that’s selected I can press control one or format selection here. We’ll change the shape, the fill to no fill and the border color to no line click close and what you’ll see now is that we’ve managed to make those columns float very cool all right.
From here we can go through and choose the additional series, so series pink and format that change the fill color to a solid fill and choose the nice pink color, click close and then series green or series yellow.
All right, now rather than go into the Format toolbar, we’re going to choose the pink bucket here and choose yellow. I don’t like that yellow well, we’re going to be stuck with that yellow, and then choose series green and blue and so on.
All right, so now we have the chart kind of doing what we wanted to do we have to fix up the legend over here, so I’ll take these words and you know just put the bottom quartile, third quartile, second quartile and top quartile all right, so everything is good except for where we have the word invisible.
Now, if I put a space there that will solve the problem because the color of the column is white. If I did not want to put the space I could also click once to select a legend, click a second time to select that one individual and just click delete and get rid of it, so a couple of different ways to go and those all work out provided your numbers are positive. Once the numbers become negative then it becomes a whole different ballgame sort of thing under performance. I like it if it’s an investment results we’re going to have problems there, but kind of a cool way to go, interesting looking chart.
You know, I understand some people say 3D charts are a lot of chart junk. You’re co-worker don’t want to use those or not, but say, I’m talking about how to create the invisible series down at the bottom and have your column slope.
Thanks for stopping by well, see you next time for another netcast from MrExcel.
Transcription by:
Scribe4you Transcription Services