Excel FREQUENCY Function
last modified April 4, 2025
The FREQUENCY function is a statistical function in Excel that
calculates how often values occur within specified ranges (bins). This tutorial
provides a comprehensive guide to using the FREQUENCY function.
You'll learn its syntax, practical applications, and advanced techniques to
master this powerful data analysis tool.
FREQUENCY Function Basics
The FREQUENCY function counts how many values fall within specified
ranges (bins) in a dataset. It returns a vertical array of counts and must be
entered as an array formula (Ctrl+Shift+Enter in older Excel versions).
| Component | Description |
|---|---|
| Function Name | FREQUENCY |
| Syntax | =FREQUENCY(data_array, bins_array) |
| Arguments | data_array: Values to analyze bins_array: Upper limits of ranges |
| Return Value | Vertical array of counts |
This table breaks down the essential components of the FREQUENCY
function. It shows the function name, syntax format, argument requirements, and
return value characteristics.
Basic FREQUENCY Example
This example demonstrates the simplest use of FREQUENCY with test
scores and grade boundaries.
| A (Scores) | B (Grade Boundaries) | C (Frequency) |
|---|---|---|
| 78 | 60 | |
| 85 | 70 | |
| 92 | 80 | |
| 65 | 90 | |
| 72 |
Select cells C1:C4, enter the formula, and press Ctrl+Shift+Enter (for array formula in older Excel versions). The function will count scores in each grade range.
=FREQUENCY(A1:A5, B1:B4)
This formula counts how many scores fall into each grade range: ≤60, 61-70, 71-80, 81-90, and >90. The result will be a vertical array with counts for each bin.
FREQUENCY with Equal Width Bins
This example shows how to create equal-width bins for analyzing age distribution data.
| A (Ages) | B (Bin Upper Limits) | C (Frequency) |
|---|---|---|
| 23 | 20 | |
| 35 | 30 | |
| 42 | 40 | |
| 28 | 50 | |
| 19 |
=FREQUENCY(A1:A5, B1:B4)
This formula creates age groups: ≤20, 21-30, 31-40, 41-50, and >50. The result shows counts for each age range. Equal-width bins are useful for consistent data analysis.
FREQUENCY with Unequal Bins
This example demonstrates using custom, unequal bin sizes for salary data analysis.
| A (Salaries) | B (Salary Brackets) | C (Frequency) |
|---|---|---|
| 45000 | 30000 | |
| 62000 | 50000 | |
| 38000 | 70000 | |
| 55000 | 100000 | |
| 82000 |
=FREQUENCY(A1:A5, B1:B4)
This formula creates salary brackets: ≤30k, 30k-50k, 50k-70k, 70k-100k, and >100k. Unequal bins allow for meaningful categorization based on business needs.
FREQUENCY with Dynamic Arrays
In modern Excel, FREQUENCY automatically spills results with
dynamic arrays. This example analyzes product prices.
| A (Prices) | B (Price Ranges) | C (Frequency) |
|---|---|---|
| 15.99 | 10 | |
| 8.50 | 20 | |
| 25.00 | 30 | |
| 12.75 |
=FREQUENCY(A1:A4, B1:B3)
This formula counts products in price ranges: ≤$10, $10-$20, $20-$30, and >$30. In Excel 365/2019, results automatically spill to adjacent cells.
FREQUENCY with Empty Cells
This example shows how FREQUENCY handles empty cells in the data
array.
| A (Values) | B (Bins) | C (Frequency) |
|---|---|---|
| 5 | 5 | |
| 10 | ||
| 12 | ||
| 8 |
=FREQUENCY(A1:A4, B1:B2)
Empty cells in the data array are ignored. The formula counts values ≤5, 6-10, and >10. The empty cell A2 doesn't affect the counts.
FREQUENCY with Text Values
This example demonstrates FREQUENCY's behavior when the data array contains text values.
| A (Mixed Data) | B (Bins) | C (Frequency) |
|---|---|---|
| 15 | 10 | |
| "Text" | 20 | |
| 25 |
=FREQUENCY(A1:A3, B1:B2)
Text values in the data array are treated as zero. The formula counts values ≤10, 11-20, and >20. "Text" in A2 is counted in the ≤10 bin as zero.
The FREQUENCY function is a powerful tool for statistical analysis
in Excel. It provides valuable insights into data distribution patterns.
Remember that FREQUENCY returns an array and requires proper range
selection. With practice, you can use it to analyze test scores, salaries, ages,
and other numerical datasets effectively.
Author
List all Excel Formulas.