07 March 2012

How to do a proper histogram in Excel

A few months ago I posted an article on the right way to do a histogram. The example I showed was done with JavaScript, because there was no way apparent to do it in Excel.

I found this annoying, so I've been picking away at it. The problem is that the labels should be between the bars, not underneath them, and Excel insists on putting them underneath.

The solution is to alternate null bars with the data bars and label every second bar. So, lets say your histogram values are (.01, .03,..., .01) against category labels (10, 20,..., 100). You'd set up the axis values as (0, .01, 0, .03,..., 0, .01, 0) and (10, 15, 20,..., 95, 100). Needless to say, you'll be coding some VBA to set up the chart.

Making it work takes some tweaking, the main thing is to set Xaxis.MajorUnit = 2, so that it only marks the null bars – the data bars will be between the marks the way they should be.

I set the series ChartType=xlColumnClustered. All I can say is it works. The result could look something like this:

Pretty Histogram

If you're happy with someone else doing the coding, this is one of the charts in my probability management kit for Excel. Go to smpro.ca/SDXL to get it.