Excel FREQUENCY Function

The FREQUENCY function counts how often numeric values occur in a set of data and returns a frequency distribution – a summary table that shows the frequency (count) of each value in a range.  FREQUENCY returns the distribution as a vertical array of numbers that represent a "count per bin". The FREQUENCY function always returns an array with one more item than bins in the bins_array. This is by design, to catch any values greater than the largest value in the bins_array.

To create a frequency distribution using FREQUENCY:

1. Enter numbers that represent the bins you want to group values into
2. Make a selection the same size as the range that contains bins, or one greater if want to include the extra item
3. Enter the FREQUENCY function as an array formula using control+shift+enter.

In Excel 365, it is not necessary to select multiple cells and enter as an array formula, see below.

Example

With data in A1:A100, and bins in B1:B5, you would select C1:C5 and enter a formula like this:

{=FREQUENCY(A1:A100,B1:B5)}

If you want to see the count in the extra "overflow" bin returned by FREQUENCY, select C1:C6 before entering.

Note: the curly braces added by Excel automatically when formula is array-entered.

Excel 365

In Excel 365, which supports dynamic arrays natively, it is not necessary to select multiple cells before entering the FREQUENCY function. With data in A1:A100, and bins in B1:B5, select cell B1 and enter the formula below normally:

=FREQUENCY(A1:A100,B1:B5)

FREQUENCY will return an array of six counts, and these counts will spill automatically into the range B1:B6.

Notes

1. FREQUENCY returns multiple values and must be entered as an array formula, except in Excel 365.
2. FREQUENCY always returns an array with one more item than bins. This is by design, to catch any values greater than the largest interval in the bins_array.
3. Each bin shows a count of values up to and including bin value, excluding values already accounted for.