Creating a simple histogram in Excel 2007

Keywords: histogram, normal distribution, Excel 2007, column chart

Version: Should work with any version. Tested with Excel 2007.

This article contain a quick tutorial on how to create a simple histogram in Excel 2007, to visualize for example student test score, like in the example.

To make this, start by creating a new worksheet and enter some structure, like in the image below.

Starting bin and bin width defines the resolution and span of the histogram categories. Data is the column where you will enter your data(!), in this caste test score results, Bin no is just a counter, Bins are the histogram categories, and Frequency is the number occurence of a test score result (Data) in Bins.

So how to achieve this? The key here is the frequency formula and the array formula function in Excel.

To enter an array formula this follow these steps.

1. Select the range that will contain your frequency distribution (in this case D6:D18)

2. Enter the frequency fomula in the toolbar text field:

=FREKVENS(B6:B65,D6:D18)

where B6:B65 should be the actual range containing your data.

3. After entering the complete formula press shift+CTRL+Enter to tell Excel you want this to be an array formula.

4. Done!

Now select the range containing your Bins and Frequency and create a clustered column chart. The default Excel column chart have a gap between the columns while traditionally in histograms there should be no gap between columns. Remove the gaps by right clicking the data series –> Format data series –> select  Alternatives for data series –> spacing –> set to zero.

Format the chart to your liking. A sample histogram can be seen below:

Another way to visualize distribution is by graphing the cumulative relative frequency. To do this, just create a new column where you sum up the relative frequencies for all bins. The sum will of course be 100%. Then create a new graph similar to the histogram above. The result can look something like this:

Popularity: 36% [?]