Stat 11/31

January 26, 2006

Making a Histogram in Excel using the FREQUENCY Function

It is possible in Excel 2003 to create construct a histogram that updates automatically when you change the data or the bin boundaries. Here are the steps:

(1) Put your data into any column, row, or rectangle on the spreadsheet. For example, you might put the data values in cells A5 to A15 (that is, in the range A5:A15).

(2) Decide which bins to use for your histogram. Type the upper limits of the bins in one column. For example, if you choose bins

up to 30, 30 to 40, 40 to 50, 50 to 60, over 60 (five bins)

then the upper limits are

30, 40, 50, 60.

(The last bin doesn’t have an upper limit.) Let’s say you type these bin upper limits in the range D1:D4.

(3) Highlight cells for the frequency table. You should highlight one cell for each bin. In the example, that means highlighting five cells.

(4) Type this formula (while all the cells are still highlighted):

=FREQUENCY(A5:A15, D1:D4) (Don’t press ENTER yet!)

The first range is the location of your data, and the second range is the list of bin upper limits.

(5) Now press CONTROL-SHIFT-ENTER. That is, hold both CONTROL and SHIFT down while you press ENTER. That makes the formula into an “array formula,” so that it can control all of the cells you highlighted.

(6) Now you have the frequencies you need. Highlight them again (if they’re not still highlighted). Click on the CHART WIZARD (a small chart-like button on one of the toolbars). Accept all the defaults. When you finally click on “finish” the chart will appear in your workbook.

(7) Correct the labels on the horizontal axis, as follows: Right-click on the chart and select “Data Series.” In the dialog box, find the box marked “x axis labels” and enter a range that contains labels. In the example, you might type

=D1:D5

to use your bin boundaries as labels. That isn’t a great solution, so you might want to type labels into a separate column somewhere so that you can get the labels you want.

(8) Fix other parts of the chart by right-clicking on any feature that could stand improvement, and experimenting with the choices.

NOTES:

a – Excel is going to make the bars all have the same width (whether you like it or not) so you should make the bin sizes equal as well.

b – Blanks and text cells in the data won’t be counted.

c – Whenever you change the original data or the bin boundaries, the histogram will change automatically! If you want to change the number of bins, you’ll have to start over.

EXAMPLES from Stat 11 can be found from the Stat 11 or Stat 31 webpage.