Here is how the FREQUENCY function works in Excel:
You can download the file here and follow along.
Here we have a list of 100 numbers ranging between 1 & 5,000. I want to know how many fall into each of the 1,000 sized bins or buckets:
The first step in using the FREQUENCY function is to list the upper limits of each range:
Next, we need to highlight the area next to the limits, adding to it one additional cell. This is necessary to “catch” any that are outside of the ranges specified. While the range is highlighted, we can enter into the active cell the FREQUENCY function:
We’ve defined the data array (A2:A101) along with the bins array (D2:D5):
Now, instead of hitting ENTER we need to hit Ctl + Shift + Enter, since this is an array function. Note the curly brackets around the formula:
Excel has now entered into each cell of the FREQUENCY function the quantity for each of the bins that we’ve established. The FREQUENCY function will update automatically whenever any values are changed in the data array.
This can be accomplished via a PivotTable also, but FREQUENCY is nice if your data dynamically changes.