Frequency Tables and Histograms

Last October, I wrote about the FREQUENCY Function in Excel. This function allows you to investigate how frequent values within particular ranges occur. This unique tool works as an Array function that counts the number of values that occur in each specified interval (or “Bins”, as they are often referred).

The FREQUENCY Function syntax is as follows:

FREQUENCY(data_array, bins_array)

The function is entered as an Array formula after you select a range of adjacent cells (B2:B7 in our example) into which you want the distribution to appear. After you select the data and bins arrays, press CONTROL+SHIFT+ENTER.

The following example shows the “Bins” under the heading Claims, and the Frequency of claims within each range under Instances:

Now comes the fun part where we make the Histogram.

Select the table A1:B7 and create a simple Column Chart. Right-Click one of the columns in the chart and choose Format Data Series. On the Series Options tab change the Gap Width to 0% (best practice for Histograms). Finally, on the Fill tab, put a check mark in the box next Vary colors by point.

There you have it!  A finely done Frequency Table and accompanying Histogram.  Far Out!