Excel Charts 5 - Paired Bar Charts

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jul 16, 2010.
Compare two populations by mirroring their bar charts back to back
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top