Categorized | excel chart

One Axis Frequency Distribution Chart in Excel (Download & Template)

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.


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:


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Fabrice wrote:

  2. NL wrote:


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


  3. Dj wrote:

    This is exactly what I have been looking for!!! Thank you for sharing this! Will be a huge part of a major product.


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel