Michael from Kansas City asks how to build a floating quartile chart. Episode 1143 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!
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.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
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 these cool stacked floating column charts over here.
And now what this is saying, like this section on here in the red is saying, the first quartile range from this low of 11.46, up to the third quartile 14.5.
Alright, so that's the data that he has, 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, and actually, already put the formulas in, just change the color.
And the way I think about this is there's an invisible series that I'm going to go ahead and chart, and then how tall the pink, yellow, 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 pink series, the first series, we're going to see, it's the Delta between the low and the third quartile, and similar formulas throughout, have to build these one at a time, and then copy them over.
OK now, let's build a chart from that whole series, so we'll go to Insert, Column, choose the 3D Column chart.
Now we need to change this, so that way we have the different colors over here, and the legend and the years going across the bottom.
Alright, now that we've done that, we want to choose that invisible series, so I come here to the Layout tab, and choose Series “Invisible”.
Now that that's selected, I can press Ctrl+1 or Format Selection here, will change 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, right?
From here, we could go through and choose the additional series, so Series “pink” and format that, change the fill color to a Solid fill, and choose a nice pink color, click close.
And then Series “green” or Series “yellow”.
Alright now, you know, rather than go into the Format dialog box, we can just use the Paint bucket here and choose yellow, don't like that yellow, guess we're going to be stuck with that yellow.
And then choose Series “green” and “blue” and so on.
Alright, so now that we have the chart kind of doing what we wanted to do, we have to fix up the legend over here.
So I take these words, and you just put bottom quartile, third quartile, second quartile, and top quartile, alright.
So everything's good, except for where we have the word invisible.
Now if I put a space there that will solve the problem, because the column goes away.
If I did not want to put a space there, I could also click once to select the legend, click a second time to select that one individual item, and then just click Delete and get rid of it.
So, a couple of different ways to go.
Now this all works out, provided your numbers are positive.
Once the numbers become negative, then it becomes a whole different ballgame.
So depending on their performance, like if its investment results are having some 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!” Your call, whether you want to use those or not, but today, talking about how to create the invisible series down at the bottom to have your column slope.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
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 these cool stacked floating column charts over here.
And now what this is saying, like this section on here in the red is saying, the first quartile range from this low of 11.46, up to the third quartile 14.5.
Alright, so that's the data that he has, 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, and actually, already put the formulas in, just change the color.
And the way I think about this is there's an invisible series that I'm going to go ahead and chart, and then how tall the pink, yellow, 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 pink series, the first series, we're going to see, it's the Delta between the low and the third quartile, and similar formulas throughout, have to build these one at a time, and then copy them over.
OK now, let's build a chart from that whole series, so we'll go to Insert, Column, choose the 3D Column chart.
Now we need to change this, so that way we have the different colors over here, and the legend and the years going across the bottom.
Alright, now that we've done that, we want to choose that invisible series, so I come here to the Layout tab, and choose Series “Invisible”.
Now that that's selected, I can press Ctrl+1 or Format Selection here, will change 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, right?
From here, we could go through and choose the additional series, so Series “pink” and format that, change the fill color to a Solid fill, and choose a nice pink color, click close.
And then Series “green” or Series “yellow”.
Alright now, you know, rather than go into the Format dialog box, we can just use the Paint bucket here and choose yellow, don't like that yellow, guess we're going to be stuck with that yellow.
And then choose Series “green” and “blue” and so on.
Alright, so now that we have the chart kind of doing what we wanted to do, we have to fix up the legend over here.
So I take these words, and you just put bottom quartile, third quartile, second quartile, and top quartile, alright.
So everything's good, except for where we have the word invisible.
Now if I put a space there that will solve the problem, because the column goes away.
If I did not want to put a space there, I could also click once to select the legend, click a second time to select that one individual item, and then just click Delete and get rid of it.
So, a couple of different ways to go.
Now this all works out, provided your numbers are positive.
Once the numbers become negative, then it becomes a whole different ballgame.
So depending on their performance, like if its investment results are having some 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!” Your call, whether you want to use those or not, but today, talking about how to create the invisible series down at the bottom to have your column slope.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
