Excel FREQUENCY function

Summary

The Excel FREQUENCY function calculates the distribution of values within a range into specified intervals, known as bins. This function is essential for statistical analysis, especially when you need to understand the spread of numerical data, such as test scores, employee ages, or product prices.
Syntax
				
					=FREQUENCY(data_array, bins_array)
				
			
  • data_array: The array or range of data values you want to analyze
  • bins_array: The array or range that defines the intervals (bins) for grouping the data values
Return value
A vertical array where each number represents the count of data values falling into each bin.

How to use

FREQUENCY is often used with array formulas in earlier versions of Excel or as a dynamic array function in Excel 365 and later, which automatically spills the results into multiple cells. It’s perfect for creating histograms or detailed frequency distributions.

Examples

Simple FREQUENCY
To Group Scores into Ranges: If you want to see how many students scored within set score ranges on a test.
				
					=FREQUENCY(A2:A100, B2:B5)
				
			
Assuming A2:A100 contains test scores and B2:B5 lists the maximum score for each bin (e.g., 59, 69, 79, 89), FREQUENCY calculates how many scores fall into each range (≤59, 60-69, 70-79, 80-89), plus any scores above the highest bin (>89).
FREQUENCY with Explicit Bins
Categorizing Data Points with Directly Specified Bins: To directly categorize data points into bins without using a separate bin range reference.
				
					=FREQUENCY(A2:A100, {0, 10, 20, 30, 40})
				
			
This formula evaluates the data in A2:A100 and categorizes it into bins defined directly within the formula: 0-10, 11-20, 21-30, and 31-40. It counts how many data points fall into each of these intervals. For instance, if there are data points spread across these ranges, FREQUENCY returns an array with counts corresponding to each bin, plus an additional count for any values greater than 40.
FREQUENCY with TRANSPOSE
Converting Vertical Output to Horizontal: For displaying the frequency distribution horizontally.
				
					=TRANSPOSE(FREQUENCY(A2:A100, B2:B5))
				
			
If you want to display the frequency distribution generated by FREQUENCY horizontally, perhaps for a report or chart, use the TRANSPOSE function to change the vertical array output of FREQUENCY into a horizontal array. This method is especially useful when space or formatting requires a horizontal presentation of data.

Additional Notes

  • The bins_array should be in ascending order for FREQUENCY to group the data_array values correctly.
  • FREQUENCY treats each bin as greater than the previous bin and up to and including the bin value.
  • An extra bin is automatically added by FREQUENCY to count any values greater than the highest bin specified.
  • Use FREQUENCY to create histograms in Excel by defining bin ranges that suit your data analysis needs.

Related Functions

Excel MODE function

The Excel MODE function determines the most frequently occurring number in a data set, useful for identifying common values.

Content Navigation