Monday, 15 February 2010

How to make a Histogram on Excel

Had been struggling to create a histogram in Excel, and desperately wanted to be able to do one for some self assessing materials for my year 11s.

Finally cracked it, was fairly easy so i thought i'd share it here.

The example above shows a simple one.
The coordinates in red are the key ones.
simply put they show the start and end point of each bar (your x coordinate) and the frequency density (your y coordinate)
Key here is the extra coordinate in between bars - eg 3rd one down (10, 0) - this makes the bar return to the baseline like the kids would draw it (otherwise you just get a "skyline" type chart, which is pretty sweet in its own right...)

You then select these 2 columns of data and plot a scatter graph.
Select an individual point of data, right click on it.
Click on "Format data series", and in the patterns tab, turn on the LINE and turn off the MARKER.

Voila, histogram.

When I selected my histogram, i set the red coordinates to reference the values in the table, that way it makes it very easy to create in future.

Also, it allows the kids to really easily see how a histogram is built up, and self assess themselves at the same time (as well as being amazed at how the graph is drawn...)


No comments: