Excel Histogram Charts and FREQUENCY Function

A Histogram, also known as a frequency distribution, is a chart that illustrates the distribution of values that fall into groups.

Since my 5 year old is big into his football (soccer) we’ll take goals scored as an example…even though in 5 year old's football matches you’re not supposed to count the goals scored!

Below is the end result. I've added a cumulative percentage to this chart to aid with further interpretation of the data, but the histogram itself is the column chart.

From the histogram we can see that we've scored 2-3 goals in 11 matches. And from the cumulative percentage we can see that in 80% of matches between zero and three goals are scored.

We could also use this type of chart to plot:

• Distribution of student grades
• Performance of salespeople e.g. groups might be units sold
• Distribution of salaries across headcount
• Plus various scores, ratings and other data you want to see the frequency distribution of.

How to create a Histogram Chart

The first thing we need to do is compile our data into a table that can feed our chart.
On the left we’ve got a list of the goals for the last 25 matches. The first thing we need to do is specify our groups, or bins as they are often referred to.

Since our data range is only small (between zero and 6 goals) our bins will be small: 0 – 1, 2 – 3, 4 – 5 and 6 – 7. In fact we could just as easily have a bin for each number from 0 to 6, but I want to show you how to use the FREQUENCY function so we’ll have small groups.

Next we need to set up a table that will feed our histogram chart like this:

Frequency Table Explained

Group Column

These are simply the groups that will appear on the horizontal axis of the chart.

Bin Column

The bins are the upper limits of the groups and are used by the FREQUENCY formula. You can see that the bin value for the group '2-3' is 3.

Frequency Column

The Frequency Column contains the FREQUENCY formula.  The FREQUENCY function is actually an array formula which means it needs to be entered using CTRL+SHIFT+ENTER.

The syntax for the FREQUENCY function is:

=FREQUENCY(data_array, bins_array)

and our formula is:

{=FREQUENCY(K5:K29,N5:N8)}

Note: the curly brackets are entered by Excel when you press CTRL+SHIFT+ENTER to enter the formula.

Tip: select all the cells that will contain your FREQUENCY formula (in our case O5:O8), then enter the formula, then press CTRL+SHIFT+ENTER and your formula will automatically be entered in all the required cells with the curly brackets.

Note: since this range containing the FREQUENCY formula (O5:O8) is an array you can only edit the whole array. Excel will give you an error if you try to modify a part of the array on its own.

Cum % Column

This is simply a cumulative percentage of the frequency. To calculate this, the formula in the first cell is:

=SUM($O$5:O5)/SUM($O$5:$O$8)

Tip: The first cell reference in the above formula, $O$5, is what is called an absolute reference and the second, O5, is a relative reference. This is so that when you copy the formula down the remaining cells it will automatically update to include the next cell in the formula, and thus calculates a cumulative percentage.

Now that we have our frequency table ready we can plot our Histogram chart.

How to create a Histogram Chart

1. Highlight the frequency data table (cells M4:P8)
2. From the Insert tab of the ribbon select Column > 2-D column. You should end up with something like this.

1. Remove the Bin data from the chart. Select the blue bin data columns (just click on one of the columns) and press the delete key to delete them.
2. Change the Cum % to a line chart. Select the Cum % column, right click and select Change Series Chart Type.
3. Select a line chart.
4. Move the Cum % to a Secondary Axis. Select the Cum % line on the chart, right click and select Format Data Series.
5. In the Series Options tab select Secondary Axis. It should look like this now:



6. Remove the gap between the bars. Select the Frequency bars on the chart and right click and select Format Data Series.
7. In the Series Options tab reduce the Gap Width to 0%.
8. Add labels and format. Select the chart and from the Layout Tab on the ribbon add a chart title and axis labels. Click inside the labels to add your text.
9. Reposition your legend to the top right of the chart. Click on the legend and drag the outer edge of the box to the top. Resize the box using the pull handles so that the data is side by side rather than stacked. This will make it fit better at the top of your chart.
10. Remove gridlines. Click on the horizontal gridlines and press the delete key to remove them.
11. Resize chart. Click inside the chart area so that the pull handles are visible (see squares and circles on the outline of the chart in the image below) and resize it by grabbing the mid pull handle on the right hand side, and dragging it to the right to resize it and fill the gap left from the legend.

1. Add data labels to line chart. Select the line in your chart and from the Layout tab on the ribbon select Data Labels > Above.
2. Change bar colour. Select the Frequency bars and from the Format tab on the ribbon and select a new colour from the Chart Styles section.

Your chart should now look like this:

While this may seem like a lot of steps, once you get to grips with formatting charts you can do all this in about a minute.

Our premium training has comprehensive video tutorials on Excel Charts. Click here to sign up for our Excel Training.