MrExcel's Learn Excel #821 - Dual Histogram

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 Jan 12, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,224,872
Messages
6,181,498
Members
453,047
Latest member
charlie_odd

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