One axis charts combined with the histogram are a great way to summarize unidimensional data. Excel does not provide provide in-built support for one axis frequency distribution charts but they can be created without breaking into a sweat.
This one’s based on a set of 250 data points with 10 bins. While its easy to get a general idea of distribution across the distribution range, the distribution within each of the bins – that’s not very clear. One can obviously make an educated guess – but that’s about it.
Let’s look at another one with greater detail. This one is based on the same data set but has 50 bins.
As you can see, when broken down further, the data distribution within individual bars is far from being even. Even the highest bars have portions that have a sparser distribution than portions within bars with a lesser number of observations.
Here’s a very useful tip on how to make histograms in excel using pivot table from Mike Alexander.
Making the One Axis Distribution Chart in Excel
The first step is to simply create a dot plot using the XY Chart in excel.
There are two ways in one can take from here. The first one is to simply add the y-error bars.
One disadvantage of this chart is that if two data points overlap, one gets hidden beneath the other which makes visualization of the data distribution somewhat inaccurate. Wait. We can do better.
Instead of using the y error bars, we can create custom shapes and add them to the chart. To get this one going, simply create vertical bars using excel auto shapes. Once this is done simply copy the shapes (ctrl + c), select the chart series and paste (ctrl + v) the shape over it. When you do this, you’ll soon realize the pro and cons of using lighter as opposed to darker shades and thicker vs. thinner bars.
This one has the advantage that when two data points overlap, they combine to produce a darker shade. The greater the number of points which overlap, the darker the region. When used in combination with the histogram, both the summary and details can be quickly grasped.
And why stop a good thing. We can add colors and make this into a gradient color chart. And from there on, its just a short hop to a cool looking heatmap chart. The colors can serve as an additional dimension of information. We’ll further explore this concept sometime later.
You can download a sample worksheet containing the one axis data distribution chart in excel here or click on the button below: