Compare two populations by mirroring their bar charts back to back
Transcript of the video:
MrExcel podcast is sponsored by Excel-XL.
Cell Charts: Chapter 5; The Paired Bar Chart.
Welcome back to the MrExcel netcast. I'm Bill Jelen.
In the Churning book chapter 5, talks about doing comparisons and this Paired Bar Chart is pretty cool, although it requires a bit of trickery, in order to get to work.
So it works, when you're comparing two different populations.
For example, here preference for ice cream flavors; Kids and Adults and so what we're gonna do is we're gonna, first sort it by one of the one of the...
factors in this case, Adults, we'll put it in Adults sequence and then I need to take the numbers on the left and make them negative, so, here's how I'm gonna do that, I'm going to take a minus one... copy the minus one and then I'm gonna use Paste Special multiply...
Paste Special multiply, so edit Paste Special, I am gonna choose multiply, click OK, and now, you know, Paste Special, it's a little bit harder now.
You can always find it down here it at the bottom of the paste drop-down, choosing Paste Special and so that gets 1 to 7 numbers to be negative, but I don't want them to show up as negative I, this is where the trickery comes in, so, I'm going to go to the Number category here in Home and hit the Dialogue Launcher, you can also do Ctrl+1 and I'm going to set up a custom format of 0;0, when you have two different zones here separated by semicolon, the first one's for positive, the second ones for negative and most people of course, would do -0 to get that 2nd number show up as negative, but I want it to show up as positive, so, this just affects how it displays, so, it's actually storing -162 but showing me 162.
All right, so made that negative.
Now let's create our Bar Chart, here this would be a Stacked Bar Chart, so insert Charts, Bar and well, the Stacked Bar.
All right, now just zoom a little bit here Couple of things, the most Popular Flavor appears at the bottom.
Legend appears along the rights.
I want to change the Legend.
We'll go to a Legend, Show Legend at Top.
The beautiful thing there now is the Legend, now matches Kids on the left, Adults on the right.
I want to get the... I want to get the largest most popular flavor at the top.
Okay so, that's going to be the Vertical (Category) Axis, Format Selection, in here we're choosing something called Categories in reverse order.
Now we have the largest items at the top.
The chart currently is a little bit, not quite symmetrical, going from 200 to 150, so we'll choose that, double click and say that the Minimum is going to fix that minus 200, the maximum is also going to be fixed at 200, ensures that or a little bit symmetrical and then you have to make a call here, this, these labels that are appearing near the Center Axis is that intruding on the chart or not.
One thing that you could do, is double click the horizontal axis again, and there's something called Axis Labels. Right now is Next to Axis, if I say Low, click close that, I am not even sure it worked. It didn't work.
Axis Labels Low, close, there we go, all right, I don't know why that didn't work for the first time...
moves those over to the left, so now we get to see, you know, in descending order, Adults prefer Vanilla, Butter Pecan and Chocolate, Chocolate chip.
Kids, yep, on the vanilla, definitely, on the chocolate.
But much more preference for Strawberry and Blue Moon and I'm going to recall, so I'm just creating a Paired Bar Chart.
Well hey, I want to thank you for stopping by, I'll see you next time for another...
...hey, I wanna thank you for stopping by. I'll see you next time for another netcast from MrExcel.
Cell Charts: Chapter 5; The Paired Bar Chart.
Welcome back to the MrExcel netcast. I'm Bill Jelen.
In the Churning book chapter 5, talks about doing comparisons and this Paired Bar Chart is pretty cool, although it requires a bit of trickery, in order to get to work.
So it works, when you're comparing two different populations.
For example, here preference for ice cream flavors; Kids and Adults and so what we're gonna do is we're gonna, first sort it by one of the one of the...
factors in this case, Adults, we'll put it in Adults sequence and then I need to take the numbers on the left and make them negative, so, here's how I'm gonna do that, I'm going to take a minus one... copy the minus one and then I'm gonna use Paste Special multiply...
Paste Special multiply, so edit Paste Special, I am gonna choose multiply, click OK, and now, you know, Paste Special, it's a little bit harder now.
You can always find it down here it at the bottom of the paste drop-down, choosing Paste Special and so that gets 1 to 7 numbers to be negative, but I don't want them to show up as negative I, this is where the trickery comes in, so, I'm going to go to the Number category here in Home and hit the Dialogue Launcher, you can also do Ctrl+1 and I'm going to set up a custom format of 0;0, when you have two different zones here separated by semicolon, the first one's for positive, the second ones for negative and most people of course, would do -0 to get that 2nd number show up as negative, but I want it to show up as positive, so, this just affects how it displays, so, it's actually storing -162 but showing me 162.
All right, so made that negative.
Now let's create our Bar Chart, here this would be a Stacked Bar Chart, so insert Charts, Bar and well, the Stacked Bar.
All right, now just zoom a little bit here Couple of things, the most Popular Flavor appears at the bottom.
Legend appears along the rights.
I want to change the Legend.
We'll go to a Legend, Show Legend at Top.
The beautiful thing there now is the Legend, now matches Kids on the left, Adults on the right.
I want to get the... I want to get the largest most popular flavor at the top.
Okay so, that's going to be the Vertical (Category) Axis, Format Selection, in here we're choosing something called Categories in reverse order.
Now we have the largest items at the top.
The chart currently is a little bit, not quite symmetrical, going from 200 to 150, so we'll choose that, double click and say that the Minimum is going to fix that minus 200, the maximum is also going to be fixed at 200, ensures that or a little bit symmetrical and then you have to make a call here, this, these labels that are appearing near the Center Axis is that intruding on the chart or not.
One thing that you could do, is double click the horizontal axis again, and there's something called Axis Labels. Right now is Next to Axis, if I say Low, click close that, I am not even sure it worked. It didn't work.
Axis Labels Low, close, there we go, all right, I don't know why that didn't work for the first time...
moves those over to the left, so now we get to see, you know, in descending order, Adults prefer Vanilla, Butter Pecan and Chocolate, Chocolate chip.
Kids, yep, on the vanilla, definitely, on the chocolate.
But much more preference for Strawberry and Blue Moon and I'm going to recall, so I'm just creating a Paired Bar Chart.
Well hey, I want to thank you for stopping by, I'll see you next time for another...
...hey, I wanna thank you for stopping by. I'll see you next time for another netcast from MrExcel.
