Erika from Arkansas asks how to create a dual histogram. These charts are great for comparing the skew of answers between two categories such as male vs female. Episode 821 shows you how to create this chart.
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:
Okay. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today a question from Erika. Erika's in Arkansas.
Want to know how to do a dual histogram where you compare one series against another series.
This is kind of interesting, a bit tricky, but we'll get through it.
We're going to take the original data set here.
I'm going to copy it to a new spot because I'm going to change all of one series to be negative.
So, basically, a couple of ways to do this.
I can enter a –1 in a cell, and then use EDIT, PASTE SPECIAL, and multiply to multiply all those by –1, and then we'll build the chart from the second series.
So, I’m going to choose this and choose a stacked bar chart.
So, choose BAR chart, and then a STACKED BAR chart, click FINISH, and you see that, basically, we have the items, the NORTH going to the left, the South going to the right.
Now, a couple things we have to do to fix this up.
I'm going to right-click on one of the bars, FORMAT DATA SERIES, and go to the OPTIONS tab where I’m going to change the GAP WIDTH to 0.
That's the way to make a histogram.
That gets rid of all the gaps between our data.
I’m going to have to make things a little bit taller so that way we can see all of the different categories, and then, you know, one problem that we have here is that the numbers along the left-hand side are reading as negative instead of reading as positive.
It's really positive in both directions.
So, I'm going to right-click and choose FORMAT AXIS, and, on the NUMBER tab, I'm going to create a CUSTOM number format.
Basically, it says, for the positive numbers, show it in 0, and, for the negative numbers, show it in 0 without a minus sign, and what that should do is, sure enough, the negative numbers now appear as positive.
So, we have positive numbers going in both directions.
You might want to move the legends.
So, we'll go to CHART, CHART OPTIONS, and, on the LEGEND, move that to the bottom.
Click ok.
So, now, we have NORTH and SOUTH.
Again, we need to make it a little bit bigger so that we see all of our labels all the way down.
There we go.
So, there's all the category labels.
We can compare NORTH versus SOUTH.
So, in this particular case, these particular categories are more skewed towards the NORTH, these categories are more skewed towards the SOUTH.
I just noticed here that my categories are going backwards.
So, let's right-click, FORMAT AXIS, and, on the SCALE tab, say CATEGORIES IN REVERSE ORDER.
That will force the A to be at the top of the chart.
So, very possible to make these dual histograms; although, just a few things that you need to clean up at the end.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Today a question from Erika. Erika's in Arkansas.
Want to know how to do a dual histogram where you compare one series against another series.
This is kind of interesting, a bit tricky, but we'll get through it.
We're going to take the original data set here.
I'm going to copy it to a new spot because I'm going to change all of one series to be negative.
So, basically, a couple of ways to do this.
I can enter a –1 in a cell, and then use EDIT, PASTE SPECIAL, and multiply to multiply all those by –1, and then we'll build the chart from the second series.
So, I’m going to choose this and choose a stacked bar chart.
So, choose BAR chart, and then a STACKED BAR chart, click FINISH, and you see that, basically, we have the items, the NORTH going to the left, the South going to the right.
Now, a couple things we have to do to fix this up.
I'm going to right-click on one of the bars, FORMAT DATA SERIES, and go to the OPTIONS tab where I’m going to change the GAP WIDTH to 0.
That's the way to make a histogram.
That gets rid of all the gaps between our data.
I’m going to have to make things a little bit taller so that way we can see all of the different categories, and then, you know, one problem that we have here is that the numbers along the left-hand side are reading as negative instead of reading as positive.
It's really positive in both directions.
So, I'm going to right-click and choose FORMAT AXIS, and, on the NUMBER tab, I'm going to create a CUSTOM number format.
Basically, it says, for the positive numbers, show it in 0, and, for the negative numbers, show it in 0 without a minus sign, and what that should do is, sure enough, the negative numbers now appear as positive.
So, we have positive numbers going in both directions.
You might want to move the legends.
So, we'll go to CHART, CHART OPTIONS, and, on the LEGEND, move that to the bottom.
Click ok.
So, now, we have NORTH and SOUTH.
Again, we need to make it a little bit bigger so that we see all of our labels all the way down.
There we go.
So, there's all the category labels.
We can compare NORTH versus SOUTH.
So, in this particular case, these particular categories are more skewed towards the NORTH, these categories are more skewed towards the SOUTH.
I just noticed here that my categories are going backwards.
So, let's right-click, FORMAT AXIS, and, on the SCALE tab, say CATEGORIES IN REVERSE ORDER.
That will force the A to be at the top of the chart.
So, very possible to make these dual histograms; although, just a few things that you need to clean up at the end.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
