Histograms in Excel

Histograms can be used as opposed to traditional bar charts when you are dealing with continuous variables. Particularly when there are a large number of values on your x-axis. With a histogram you can group together the values on your x-axis into “bins”, usually of equal sizes. The output will be very similar to a bar chart but with no gaps and is the best visual format for you to determine the distribution of your data.

 

In this example we have finishing positions for Leicester City each season since the Second World War.

 

Highlight your dataset, go to Insert – Charts – See All Charts. Then in the All Charts tab, Histogram is one of your options.

 

 

By default you will get some automatically set bins. These are the preset ranges for each of the bars. In this case they are not particularly useful, the first bar is looking for number of seasons Leicester finished between 1st and 9.8th but we only want to deal in whole numbers.

 

 

Therefore we need to right-click on the axis and go to Format Axis. Here you can set the Bin width to a whole number and either define the number of bins, or in this example we have just defined that 45 is the maximum number we’re looking for a 1 is the minimum.

 

 

This reformatted version is more informative. You can see a relatively normal distribution and that a standard finishing position for Leicester would be around the 17th to 25th mark.

 

 

Note: This Histogram chart feature is not available in earlier versions of Excel. If you’re using 2013 or earlier, you may have to create your own histogram by adding a bar chart and then changing the gap width to zero.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *