A histogram shows the frequency of data items within a certain range. You might, for example, survey 100 people, note their age and then produce a histogram of the age distribution at 10 year intervals:

1. In the range C2:C101 enter a list of numbers between 0 and 100
2. Select cell C2 and drag its copy marker to row 101

To define the category ranges for the grouping by age proceed as follows:

1. In cell A1 enter 'Age'
2. Enter 10 in A2 and 20 in A3, select these two cells and drag the copy marker to row 11

Excel calls the data ranges 'bins'. The histogram tool counts the number of data items between the current bin value and the next higher bin value. It also appears to do this for values less than the first bin. If a value is greater than or equal to the current bin and less than the next bin then it will be counted as belonging to the current bin. In the present case the first defined bin is 10 and Excel assigns values less than 10 to this one. Moving on to bin 20 Excel looks for values between 10 and 20 and enters the number found in the '20' bin. For values greater than 100 Excel creates an additional bin called 'More'.

The data ranges (in italics) were added manually after the data was created.

To make a histogram from the data created above proceed as follows:

1. Open the Tools menu and select Data Analysis
2. Choose Histogram from the list of analysis tools available
3. Click in the Input Range box and drag the mouse over the cells on the worksheet to be included in the histogram (C2:C101)
4. Click in the Bin Range box and drag the mouse over the cells where the bin values are defined (A1:A11)
5. We have included a label in the bin range ('Age') so click the Labels check box
6. Click in the Output Range box and select a suitable cell on the worksheet (e.g. E5)
7. Check the Chart Output box in the Tools section at the bottom of the dialogue box
8. Click OK and wait for the data and chart

Format the chart in the normal way, delete the legend (which you don't need), expand the chart area vertically and make the whole chart bigger if desired. You now have the distribution of the data items in the bins defined and a visual representation in the chart.

While the histogram tool is very useful it does seem to have a bug, at least in the examples shown here. The histogram chart shows the wrong values on the X axis for the data in the population. The scale should begin at 0 with the first column of data between 0 and 10, the second between 10 and 20, and so on. Instead, the first data value is 10 and the data column for items in the range 0-10 appears between 10 and 20. You could try including a bin labelled 0 but this will have with nothing in it because Excel looks for values less than zero. You will have to use Tippex and a pen to correct them!

There are other techniques for finding distributions within data including the worksheet functions MEDIAN(), MODE(), QUARTILE(), RANK(), PERCENTILE() and PERCENTRANK. These are illustrated below and are fairly self-explanatory. 

The RANK() function returns the rank position within a data set of a particular item. The PECENTRANK() function returns the percentage point (measured between 0 and 1) of a particular value in a distribution. These might be useful if you have a list of scores such as examination marks and you want to know the rank of a particular individual or the boundary of the top twenty per cent.

Use the Back Button to Return to the previous page