Making Histogram chart in excel

Histogram looks similar to a column charts, but rather than showing all data points individually, they group data points into ranges. They work well when we want to see the distribution of numbers in a range.

To explain the concept of Histogram, let’s consider a class of 30 students and their marks in a subject.

If we simply plot these marks using a column chart, it will make little sense as shown in the left side image below:

Histogram chart explained

If we want to summarize the marks of all these students in a simple graph, histogram is a good choice, a possible histogram chart is shown in the right side of the image above. As you can see, histogram is a frequency chart (we count numbers of data points in a range and that is plotted here).

Various positives & shortcoming of histogram are also tabulated above.

So, how do we make a histogram in excel?

There is no histogram chart type in excel by default, so we need to arrange the data properly, create a column chart on it and then format the column chart to make it look like a histogram.

Let’s see these steps one by one:

1.       Arranging the data: Histogram is a frequency chart, we need to create reasonable buckets (which brings out the best meaning from the data without being too exhaustive). Keeping the numbers of buckets less than 5 can be taken as a rule of thumb.

For this particular example, we take 4 buckets(less than 40 marks, between 40 & 60, between 60 & 80, and >80).

Then we count the numbers in each of these buckets and arrange the data as shown below:

Creating the frequency table for histogram

There are ways of generating this frequency table in excel, we cover all those options at the end of this tutorial. As of now, let’s focus on the histogram part.

2.       Creating the Column Chart: Now select the data-> Insert -> Clustered Chart to get the following chart:

Inserting column chart for histogram

If you are getting 2 series in the chart and are not sure of how to get the above column chart, we recommend you to read this tutorial on column chart. As a quick step, keep the top left cell blank (delete the cell that contains “Range”) and then insert this chart.

3.Formatting the chart:

The key visual differentiator of a histogram is that there is no gap between the columns (as we usually plot continuous distribution on horizontal axis) If you look at the default chart from previous step, the first column needs to reflect the number of students who have scored less than 40 marks (& not exactly 40 marks as this chart is currently hinting at). Also there should not be any gap in between these columns.

To do this, we need to make the difference between the columns as zero as shown below:

Column Gap adjustment

This looks much better now.

The last thing if you notice in the image above is the central alignment of axis labels. The proper alignment should be “right aligned”. To do an alignment of axis data label, simply click on the axis data label area to select the same and then right align from the Home->Alignment section as shown below:

Alignment of axis data labels

This is it! We can further do some formatting to enhance the Histogram. 

Finished Histogram

PS: Now, coming to various ways of creating the frequency table, we can use formulas to do this.

As you can see we can use COUNTI() to calculate number of students who got marks less than a particular number, and we can calculate frequency for all these ranges in a step by step way.

Number of students who got 40 to 60 marks = All students who got less than 60 marks – students who got less than 40 marks

Alternatively, You may also use Analysis Tool Pack to quickly do this and that will also allow you to add a histogram in one click. Steps to do this through ToolPack way can be seen here

Section: