JB asks, "How can I create a Comparative Histogram?" Today, in Episode #1522, Bill shows us how to set up our Data, create Data Lables, and Formatting for creating a Comparative Histogram Chart.
Today's Episode is brought to you by "Easy-XL". Easy-XL From MrExcel adds over 50 time saving data analysis and manipulation features to Excel. Check it out here http://www.easy-xl.com/
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Today's Episode is brought to you by "Easy-XL". Easy-XL From MrExcel adds over 50 time saving data analysis and manipulation features to Excel. Check it out here http://www.easy-xl.com/
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1522.
Comparative Histogram.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by JB.
JB's trying to create a comparative histogram like this one.
So, here's the trick, here's the data.
We have the categories, the male % and female % and you want to copy this data, it make a copy of it over here because we need to make the male side negative.
So, I'm just use minus B2, copy that down then we're going to create a stacked bar chart from this data.
So, insert, bar, choose the stacked bar, alright!
You see we start to get pretty close there, a couple things layout, legend, show legend at the top.
Categories are going backwards, so let's choose the axis.
[ Control 1 ], to format categories in reverse order, that will get the categories going the other way.
It also gets the axis at the top.
Let's see Major Tick Mark.
Type, None.
We want labels, alright!
So, to get labels and this is a little bit tricky.
Data labels a little bit of more data label options that adds the data labels, actually and you see that the male side happens to be selected.
We're going to go into the number format.
I'm going to create a custom format and right here, so this is positive.
This is for negative.
For negative, we don't want to show the minus sign, click [ add ] and use that close there we go, so the negative signs are gone.
In the original example here, the columns are a heck of a lot wider.
So, we will choose one of these, [ control 1 ] and the gap with, it's your call whether you want to make it no gap like that, or just a tiny little gap like that one.
But I think that's pretty good, most of the time as these comparative histograms have one side sorted.
So, I did that here, but you know fairly high, it's not straight forward, certainly.
But you can get pretty good labels.
Yeah, I'm torn on the labels here.
They put them in the center and that is part of the axis.
So, that's the vertical category axis, [ control 1 ].
We can move the labels, low down here on the left-hand side.
Let's take a look and see how that looks or high on the right hand side, your call.
Where you want the labels to go?
But you can kind of get this effect.
Hey! I want to thank you for stopping by, see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1522.
Comparative Histogram.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by JB.
JB's trying to create a comparative histogram like this one.
So, here's the trick, here's the data.
We have the categories, the male % and female % and you want to copy this data, it make a copy of it over here because we need to make the male side negative.
So, I'm just use minus B2, copy that down then we're going to create a stacked bar chart from this data.
So, insert, bar, choose the stacked bar, alright!
You see we start to get pretty close there, a couple things layout, legend, show legend at the top.
Categories are going backwards, so let's choose the axis.
[ Control 1 ], to format categories in reverse order, that will get the categories going the other way.
It also gets the axis at the top.
Let's see Major Tick Mark.
Type, None.
We want labels, alright!
So, to get labels and this is a little bit tricky.
Data labels a little bit of more data label options that adds the data labels, actually and you see that the male side happens to be selected.
We're going to go into the number format.
I'm going to create a custom format and right here, so this is positive.
This is for negative.
For negative, we don't want to show the minus sign, click [ add ] and use that close there we go, so the negative signs are gone.
In the original example here, the columns are a heck of a lot wider.
So, we will choose one of these, [ control 1 ] and the gap with, it's your call whether you want to make it no gap like that, or just a tiny little gap like that one.
But I think that's pretty good, most of the time as these comparative histograms have one side sorted.
So, I did that here, but you know fairly high, it's not straight forward, certainly.
But you can get pretty good labels.
Yeah, I'm torn on the labels here.
They put them in the center and that is part of the axis.
So, that's the vertical category axis, [ control 1 ].
We can move the labels, low down here on the left-hand side.
Let's take a look and see how that looks or high on the right hand side, your call.
Where you want the labels to go?
But you can kind of get this effect.
Hey! I want to thank you for stopping by, see you next time for another netcast from MrExcel.
