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 downloadable 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: 39% [?]
Can we create these backlinks?
Thank you very much for this article! I was wondering if I could use this article on my website? Of course source and a backlink will be given to you. Do let me know!
it sucks,, you did not explain clearly,, you divert from i phase to another,,
Im trying to take a bunch of different age groups and put them into age classes; like 15-19, 20-25…and idk how to do that:/ im so lost with this whole histogram process! help!
this is the most horrible article i’ve ever seen, how can you not include the column and row headings and base your entire instructional input on those? retard….
you just saved my life, thank you so much
what is a clustered column chart? The column charts do not have name labels.
When I follow the procedure, shift+Ctrl+enter, I got either 0 or 1. I did not get the full array or the bar chart. The older version seems much better and user friendly.
Right click on the column, i think
I can’t find Format Data Series!!
AHH!
You are correct of course. I will see if I can fix that.
Regards
Jesper
It would be extremely helpful to show the column and row headings! The article references cells specific cells, but no reference!