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.

Histogram Excel

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.
Excel FrequencyOn 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:

Excel Frequency Distribution

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.

Click here to download the file and have a go yourself.

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.

Histogram in Excel

  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. Histogram Excel 2007

  7. Remove the gap between the bars. Select the Frequency bars on the chart and right click and select Format Data Series.
  8. In the Series Options tab reduce the Gap Width to 0%.
  9. 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.
  10. 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.
  11. Remove gridlines. Click on the horizontal gridlines and press the delete key to remove them.
  12. 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.

Histogram Excel

  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:

Excel Histogram

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.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 18 comments… read them below or add one }

Vikas February 2, 2012 at 10:13 pm

Thank you for the post.

Reply

Mynda Treacy February 3, 2012 at 11:57 am

My pleasure, Vikas. Glad you liked it.

Kind regards,

Mynda.

Reply

lynn April 5, 2012 at 10:38 pm

I’ve looked for lots of resources on making a histogram in Excel and they are all pretty similar to this one. This is helpful if you are manually entering in all your data but what happens when you have, say… 40,000 data points that you’d like to make into a histogram?

Reply

Mynda Treacy April 6, 2012 at 5:03 am

Hi Lynn,

It doesn’t matter how many data points you have (as long as you don’t exceed the number of rows/columns available in Excel) since you use the FREQUENCY function to group your data into bins. You would never have 40,000 bins, you’d group those data points into bins just as I have done in the example.

I hope that makes sense, and your 40,000 data points are no longer insurmountable.

Kind regards,

Mynda.

Reply

rashed May 5, 2012 at 8:27 pm

Very nice initiative

Reply

rashed May 5, 2012 at 8:29 pm

very useful

Reply

Tom May 18, 2012 at 12:27 am

Can you do the same with time stamps like 16/05/2012 00:00:00 or it it just limited to number ranges ..?

Reply

Mynda Treacy May 18, 2012 at 8:56 am

Hi Tom,

Yes, just change the bins to use a time/date format and set your bin sizes to suit your data. As long as your bins are not text values it will work.

Kind regards,

Mynda.

Reply

Peter September 6, 2012 at 6:34 am

Thanks for the tip. I’ve been trying to do this using Excel for Mac (Office 2011). One thing I’ve noticed is that once I enter the equation:

{=FREQUENCY(F12:F10011,Z11:Z22)} and then press control-shift-enter, it seems to work for the first cell, but the subsequent cells increment row numbers (e.g., {=FREQUENCY(F13:F10012,Z12:Z23)}).

This gives me different results and does not seem consistent with what your worksheet shows (where the rows do NOT increment). Have I done something differently?

Reply

Mynda Treacy September 6, 2012 at 7:17 am

Hi Peter,

You need to select all the cells that will contain your FREQUENCY formula, 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.

You don’t actually copy it down. Is that what you are doing?

Kind regards,

Mynd.

Reply

Raghu September 30, 2012 at 6:47 pm

Hi, WOW!! – was my expression- once i actually could complete it myself with a different example. Additionally, in this tutorial i learnt how to use frequency function in arrays.
As usual – Simply Awesome!!!

Reply

Mynda Treacy October 1, 2012 at 10:22 am

Cheers, Raghu :)

Reply

Natalie January 15, 2013 at 4:30 am

Is there anyway to move the cumulative frequency line to the right of the graph?

Reply

MikeBanawa January 15, 2013 at 11:39 pm

Hello Natalie,

Nope, Excel won’t allow it. Also, moving the line graph to the right would defeat the purpose of presenting the cumulative frequency in the graph. This kind of presentation is actually called “Pareto Chart” used for quality control in companies and businesses around the world. You might want to create two separate charts though if you want the two chart types to be beside each other.

Thanks!
Mike

Reply

Jonathan April 6, 2013 at 11:18 pm

THERE’s an EASIER way. See http://youtu.be/ebiC71Wh5sY. NO FORMULAS. It uses PivotTables (far more flexibility) that have a drag and drop interface. The trick is to put the field with the numbers (like grades) in both the row labels and the values labels. Then you can group (aka bin) the data by any number you want. Also works well with dates. If you want custom groupings, you can manually do that too.

Reply

Carlo Estopia April 6, 2013 at 11:30 pm

Hi Jonathan,

Nice Info right there.

Thanks for sharing.

Cheers,

CarloE

Reply

Faisal Alghati October 5, 2013 at 11:06 am

Thank you so much!!
You just saved my life.

Reply

Mynda Treacy October 5, 2013 at 5:13 pm

:-) you’re welcome, Faisal. All in a day’s work.

Reply

Previous post:

Next post: