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.

## The Histogram

The histogram trades off detail for ease of comprehension. Let’s look at the chart below.

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.

Just add a fixed error value to the dot chart above and you get something like this:

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.

## Heatmaps

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:

That cool !! reminds me of something !

http://sparklines-excel.blogspot.com/2008/11/release-325-for-excel-2000.html

Cheers.

January 1st, 2010 at 7:30 amHi,

How do you do the different colors in the last graph?

Thanks.

July 30th, 2012 at 1:48 amThis is exactly what I have been looking for!!! Thank you for sharing this! Will be a huge part of a major product.

December 25th, 2016 at 2:15 am