How to Use the FREQUENCY Function in Microsoft Excel to Calculate Data Distributions

When analysing numerical datasets in Microsoft Excel—such as customer ages, examination marks, product delivery times, or transaction values—reviewing raw rows of figures rarely reveals meaningful patterns. To understand the underlying distribution, analysts group continuous numbers into distinct categories or “bins” to build frequency distributions and histograms.

While you can calculate counts for individual intervals using multiple COUNTIFS formulas, this manual approach is repetitive, prone to reference mistakes, and slow on large datasets. Microsoft Excel provides a dedicated statistical array function specifically engineered for this task: the FREQUENCY function. Here is how to use the FREQUENCY function in Microsoft Excel to calculate grouped data distributions.

What Is the FREQUENCY Function and How Does It Work?

The FREQUENCY function calculates how often values fall within specified numeric ranges (bins) and outputs a vertical array containing the count for each interval. The syntax requires two parameters:

=FREQUENCY(data_array, bins_array)

Where:

• data_array: The reference range containing the raw numeric values you want to count (e.g. A2:A50). Blank cells and text are automatically ignored.
• bins_array: The reference range containing the sorted upper boundaries of the intervals into which you want to group your data (e.g. C2:C5).

Understanding Excel’s Bin Boundary Logic

A crucial aspect of the FREQUENCY function is how Excel evaluates bin thresholds:

• Each bin counts values that are greater than the preceding bin limit and less than or equal to the current bin limit.
• The returned result is always a vertical array with one more element than the number of entries in your bins_array. This final supplementary row captures all outliers that exceed your highest bin limit.

Step-by-Step Example: Calculating Student Examination Grade Distributions

Suppose column A contains test scores ranging from 0 to 100 for 30 students (range A2:A31). You want to group these scores into standard grade bands:

• 0 to 59: Fail
• 60 to 69: Pass
• 70 to 79: Merit
• 80 to 89: Distinction
• 90 to 100: High Distinction

Step 1: Set Up the Bin Thresholds

In a separate helper column (e.g. column C), enter only the upper boundaries of your lower intervals in ascending numerical order:

Cell	Value (Bin Limit)	Grade Representation
C2	59	Scores ≤ 59
C3	69	Scores 60 to 69
C4	79	Scores 70 to 79
C5	89	Scores 80 to 89

(Note: You do not need to enter 100 in your bins array; any scores above 89 will automatically spill into the final extra output cell).

Step 2: Enter the FREQUENCY Formula

In modern Microsoft 365, Excel 2021, and Excel for the Web, dynamic array calculation is native. Select a single empty cell adjacent to your bins (for example, D2) and enter:

=FREQUENCY(A2:A31, C2:C5)

Press Enter. Excel instantly calculates the frequencies and spills the results downward across cells D2:D6.

Note for Legacy Excel (2019 and earlier): If you are using an older version of Excel that does not support dynamic arrays, select the entire destination range (D2:D6) first, type the formula in the formula bar, and press Ctrl + Shift + Enter to commit it as a classic multi-cell array formula.

Creating Descriptive Label Columns for Charts

While the FREQUENCY output displays raw counts, building a presentable dashboard requires clear interval labels. In column B, create descriptive text labels such as:

• "0 - 59" in B2
• "60 - 69" in B3
• "70 - 79" in B4
• "80 - 89" in B5
• "90+" in B6

You can now highlight columns B2:D6, navigate to Insert > Charts > Clustered Column, and generate a clean, accurate histogram distribution of your data.

FREQUENCY vs COUNTIFS: Key Differences

Feature	FREQUENCY Function	COUNTIFS Function
Formula Complexity	A single dynamic array formula spills all intervals at once.	Requires writing a separate formula with > and <= criteria for every bin.
Calculation Speed	Highly optimised single-pass engine across large datasets.	Performs multiple passes over the range for every row.
Data Handling	Strictly numeric; automatically ignores text and blanks.	Evaluates text and criteria based on formula syntax.
Outlier Capture	Automatically includes an extra bucket for values above highest bin.	Requires a manual formula for boundary overflow.

Practical Tips for Best Results

• Sort Your Bins: Always sort your bins_array in ascending order. If bins are entered randomly, Excel will not group numbers correctly.
• Spill Clearance: In Microsoft 365, ensure the cells directly beneath your formula are empty. If existing text or numbers block the spill path, Excel returns a #SPILL! error.
• Dynamic Binning: If your dataset expands dynamically, convert your source data to an official Excel Table (Ctrl + T) and pass structured references (e.g. =FREQUENCY(ScoresTable[Score], C2:C5)) so counts update automatically whenever new rows arrive.