In Episode #1289, Bill shows you how to compare two populations by mirroring their Bar Charts back to back. You will also learn how to turn a column negative using 'Paste Special, Multiply', display a negative number as a positive and more!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel charts, Chapter 5, The Paired Bar Chart.
Oh! Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
In the charting book, chapter 5, talks about doing comparisons and this paired bar chart is pretty cool, though 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 going to do is we're first sorted by one of, one of the factors.
In this case, adults will put it in adult sequence and then I need to take the numbers on the left and make them negative.
So, here's how I'm going to do that I'm going to take a minus 1, copy the minus 1 and then I'm going to use Paste Special, Multiply, Paste Special, Multiply.
So, Edit, Paste Special, I'll chose Multiply, click OK.
All right! Now, you know, Paste Special, is a little bit harder now you, you can always find it down here at the bottom of the Paste drop down, choosing Paste Special, and so, that gets once at numbers to be negative, but I don't want them to show up as negative.
This is where the trickery comes in.
So, I'm going to go to the number category here at home and hit the dialog launcher, you can also do Control+1 and I'm going to set up a Custom format of 0 semicolon 0, when you have two different zones here separated by a semicolon the first ones for positive the second ones were negative and most people of course would do minus zero to get that second number show up is negative, but i wanted to show up as positive so this just affects how it displays.
So, it's actually storing minus 162, but showing me 162.
All right! So, you made that negative.
Now, let's create our bar chart here this would be a stacked bar chart.
So, Insert, Charts, Bar and the Stacked Bar.
All right, change the zoom a little bit here, a couple things the most popular flavor appears at the bottom, legend appears along the rights.
I want to change the legend will go to Legend, Show Legend at Top.
The beautiful thing there now is the legend now match's 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, and we're choose something called Categories in reverse order.
So now, we have the largest items at the top.
The chart currently is a little bit not quite symmetrical going from 200 - 150.
So, we'll choose that double click and say that the Minimum is going to Fixed at minus 200, the Maximum is also going to be fixed at 200.
I'm sure that they are 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 should have worked, didn't work.
Axis labels, Low, Close, there we go.
All right, don't know why that didn't work for the first time, moves those over to the left.
So now, we could see you know, in descending order, adults prefer Vanilla, Butter Pecan, Chocolate, Chocolate Chip.
Kids yep on the Vanilla definitely on the Chocolate, but much more preference for Strawberry and Blue Moon and Butter Pecan.
So, interested in creating a Paired Bar Chart.
Hey, Want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Excel charts, Chapter 5, The Paired Bar Chart.
Oh! Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
In the charting book, chapter 5, talks about doing comparisons and this paired bar chart is pretty cool, though 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 going to do is we're first sorted by one of, one of the factors.
In this case, adults will put it in adult sequence and then I need to take the numbers on the left and make them negative.
So, here's how I'm going to do that I'm going to take a minus 1, copy the minus 1 and then I'm going to use Paste Special, Multiply, Paste Special, Multiply.
So, Edit, Paste Special, I'll chose Multiply, click OK.
All right! Now, you know, Paste Special, is a little bit harder now you, you can always find it down here at the bottom of the Paste drop down, choosing Paste Special, and so, that gets once at numbers to be negative, but I don't want them to show up as negative.
This is where the trickery comes in.
So, I'm going to go to the number category here at home and hit the dialog launcher, you can also do Control+1 and I'm going to set up a Custom format of 0 semicolon 0, when you have two different zones here separated by a semicolon the first ones for positive the second ones were negative and most people of course would do minus zero to get that second number show up is negative, but i wanted to show up as positive so this just affects how it displays.
So, it's actually storing minus 162, but showing me 162.
All right! So, you made that negative.
Now, let's create our bar chart here this would be a stacked bar chart.
So, Insert, Charts, Bar and the Stacked Bar.
All right, change the zoom a little bit here, a couple things the most popular flavor appears at the bottom, legend appears along the rights.
I want to change the legend will go to Legend, Show Legend at Top.
The beautiful thing there now is the legend now match's 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, and we're choose something called Categories in reverse order.
So now, we have the largest items at the top.
The chart currently is a little bit not quite symmetrical going from 200 - 150.
So, we'll choose that double click and say that the Minimum is going to Fixed at minus 200, the Maximum is also going to be fixed at 200.
I'm sure that they are 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 should have worked, didn't work.
Axis labels, Low, Close, there we go.
All right, don't know why that didn't work for the first time, moves those over to the left.
So now, we could see you know, in descending order, adults prefer Vanilla, Butter Pecan, Chocolate, Chocolate Chip.
Kids yep on the Vanilla definitely on the Chocolate, but much more preference for Strawberry and Blue Moon and Butter Pecan.
So, interested in creating a Paired Bar Chart.
Hey, Want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
