Frequency and Histograms in Excel

There are a number of ways to make histograms in MS Excel and other spread sheets. First I'll use the basic idea of a frequency table.

A frequency table is a summary of a data set based on small ranges of data. For example, I might group people ages by decade, 20s, 30s, and so on. Another way to state this is 20 to 29, 30 to 39, and so on. Excel just wants to know what the highest acceptable value is for each group. That would be 29, 39, and so on. These groups are known as categories, bins, or buckets.

bins
9
19
29
39
49
59
69
79

We can then use Excel's frequency function to count how many of our data values falls in each group.

excellFrequency

It's important that you highlight one more cell than bins you have. So here, I select cells C2 through C10, then start typing =fre. Excel will offer some options. Press the tab key to select the frequency function. Then use your mouse to select the data, type a comma, then select the bins and close the parentheses. Most Excel functions output a single thing, like a number. The frequency command is an array command which means it outputs a list of numbers. This means that just pressing the enter key is insufficient, you must press ctrl-shft-enter together.

data bins frequency
15 9 0
17 19 4
18 29 9
19 39 4
21 49 2
22 59 1
22 69 0
24 79 0
24 0
25
26
26
29
31
33
37
37
42
45
50

SUMMARY:

  • Given a column of data
  • create a column listing the maximum values for each bin
  • in the adjacent column to that select one more row than bins
  • type =frequency(
  • select the data
  • type a comma
  • select the bins
  • type the closing parenthese
  • press ctl-shift-enter

Leave a Reply