How to Use FREQUENCY in Excel

Key Insights

• FREQUENCY returns an array counting how many values fall into each bin range, always producing one more result than the number of bins to capture overflow values
• In Excel 365, FREQUENCY uses dynamic arrays and spills automatically, but older versions require Ctrl+Shift+Enter and pre-selecting the output range
• While COUNTIFS can achieve similar results, FREQUENCY is significantly faster for distribution analysis and requires less formula maintenance when bins change

Understanding the FREQUENCY Function

The FREQUENCY function counts how many values from a dataset fall within specified ranges, called bins. This makes it invaluable for distribution analysis, creating histograms, and understanding data patterns without pivot tables or complex formulas.

The syntax is straightforward: =FREQUENCY(data_array, bins_array). The function takes your data range and a set of bin boundaries, then returns an array showing the count of values in each bin. The critical detail: FREQUENCY always returns one more value than the number of bins you specify. This extra element captures any values above your highest bin.

Use FREQUENCY when you need to categorize continuous numerical data into discrete groups—test scores into letter grades, sales figures into performance tiers, or customer ages into demographic segments.

Basic Syntax and Data Structure

FREQUENCY requires two arguments:

• data_array: The range containing values you want to count
• bins_array: The upper boundaries for each bin

The bins array defines the top of each range. If your bins are 60, 70, 80, 90, FREQUENCY creates these ranges:

• Values ≤ 60
• Values 61-70
• Values 71-80
• Values 81-90
• Values > 90 (the overflow bin)

Here’s a basic example with test scores:

Data (A2:A21):
85, 92, 78, 65, 88, 73, 95, 82, 69, 91, 
77, 84, 90, 72, 86, 79, 94, 81, 75, 88

Bins (C2:C6):
59
69
79
89
100

Formula in D2:
=FREQUENCY(A2:A21, C2:C6)

In Excel 365, this formula spills automatically into D2:D7, showing:

• 0 (scores ≤ 59)
• 2 (scores 60-69)
• 6 (scores 70-79)
• 9 (scores 80-89)
• 3 (scores 90-100)
• 0 (scores > 100)

In older Excel versions, select D2:D7 first, type the formula, then press Ctrl+Shift+Enter. You’ll see curly braces {=FREQUENCY(A2:A21, C2:C6)} indicating an array formula.

Step-by-Step Implementation with Sales Data

Let’s work through a practical example analyzing monthly sales figures across different performance tiers.

Sales Data (A2:A25):
$850, $3,200, $12,500, $750, $4,800, $2,100, $9,200, $1,500, 
$6,300, $450, $8,700, $3,900, $11,200, $2,800, $5,500, $1,200, 
$7,400, $950, $4,200, $10,800, $3,500, $6,900, $2,300, $8,100

Bins (C2:C4):
1000
5000
10000

Labels (B2:B5):
Low ($0-$1,000)
Medium ($1,001-$5,000)
High ($5,001-$10,000)
Premium ($10,001+)

Formula in D2:
=FREQUENCY(A2:A25, C2:C4)

Results in D2:D5:

• 4 (Low tier)
• 9 (Medium tier)
• 7 (High tier)
• 4 (Premium tier)

This immediately shows that most sales fall in the Medium and High categories, helping you understand your sales distribution without manual counting.

To make this more useful, add percentage calculations:

Formula in E2:
=D2/SUM($D$2:$D$5)

Copy this down to show that 37.5% of sales are Medium tier, 29.2% are High tier, and so on.

Practical Use Case: Employee Age Distribution

HR departments frequently need age demographics. Here’s how FREQUENCY simplifies this analysis:

Employee Ages (A2:A51):
28, 45, 33, 52, 29, 38, 41, 25, 56, 34, 42, 31, 48, 27, 39, 
44, 36, 50, 32, 47, 35, 43, 30, 54, 26, 40, 37, 49, 33, 46,
29, 51, 38, 44, 31, 53, 35, 42, 28, 48, 36, 41, 34, 50, 32,
45, 39, 55, 37, 43

Age Bins (C2:C6):
25
35
45
55
65

Age Groups (B2:B7):
18-25
26-35
36-45
46-55
56-65
66+

Formula in D2:
=FREQUENCY(A2:A51, C2:C6)

This returns the distribution across age brackets, instantly revealing whether your workforce skews young, experienced, or balanced. You can then use this data to inform succession planning, benefits packages, or recruitment strategies.

Creating Visual Distributions

FREQUENCY output feeds perfectly into charts. After generating your frequency distribution:

1. Select your bin labels and frequency results (B2:B7 and D2:D7 from the age example)
2. Insert a Column Chart
3. Format as needed

For a more integrated approach:

Setup:
Column A: Original data
Column C: Bin values
Column D: =FREQUENCY(A:A, C:C)
Column B: Bin labels

Chart data range: B2:B7, D2:D7
Chart type: Clustered Column

This creates a histogram showing your data distribution. While Excel 365 has a built-in Histogram chart type, using FREQUENCY gives you more control over bin boundaries and makes it easier to update your analysis when data changes.

The FREQUENCY approach also lets you use the same distribution data in multiple places—charts, summary tables, conditional formatting rules—without recalculating.

Common Pitfalls and Solutions

Array formula errors: In pre-365 Excel, forgetting Ctrl+Shift+Enter causes #VALUE! errors. Always select the entire output range first, then enter the formula with Ctrl+Shift+Enter.

Bins not in ascending order: FREQUENCY requires bins sorted from smallest to largest. If bins are 100, 50, 75, results will be incorrect. Always sort your bins array.

Unexpected overflow bin values: The last element in FREQUENCY output counts values above your highest bin. If you see unexpected numbers here, check if your data contains values exceeding your maximum bin. Either add a higher bin or verify your data for outliers.

Spill errors in Excel 365: If cells below your formula contain data, you’ll get a #SPILL! error. Clear the range or move your formula to an area with empty cells below it.

Empty bins showing nothing: Unlike COUNTIFS, FREQUENCY always returns a value for each bin, even if it’s zero. If you’re seeing blanks, your formula isn’t entered as an array formula (older Excel) or isn’t spilling correctly (365).

Advanced Techniques and Alternatives

For dynamic analysis, combine FREQUENCY with named ranges:

Name Manager:
SalesData = Sheet1!$A$2:$A$100
SalesBins = Sheet1!$C$2:$C$5

Formula:
=FREQUENCY(SalesData, SalesBins)

This makes your analysis update automatically as you add data or adjust bins.

You can replicate FREQUENCY with COUNTIFS, but it’s more cumbersome:

FREQUENCY approach:
=FREQUENCY(A2:A25, C2:C4)

Equivalent COUNTIFS (requires separate formulas):
=COUNTIFS(A:A,"<="&C2)
=COUNTIFS(A:A,">"&C2,A:A,"<="&C3)
=COUNTIFS(A:A,">"&C3,A:A,"<="&C4)
=COUNTIFS(A:A,">"&C4)

FREQUENCY is cleaner, faster on large datasets, and easier to maintain. If you change bin boundaries, FREQUENCY updates automatically. With COUNTIFS, you’d need to edit multiple formulas.

When to use Excel’s Histogram chart instead: If you need quick visualization and don’t care about using the frequency counts elsewhere, the built-in histogram is faster to set up. Use FREQUENCY when you need the actual distribution numbers for calculations, reporting, or multiple visualizations.

For truly large datasets (100,000+ rows), consider pivot tables with grouping instead. They offer better performance and more flexibility for slicing data multiple ways.

The FREQUENCY function excels at transforming raw numerical data into meaningful distributions. Master it, and you’ll handle data analysis tasks that would otherwise require pivot tables, complex formulas, or external tools. Whether you’re analyzing sales performance, student grades, or customer demographics, FREQUENCY provides fast, reliable distribution analysis with minimal setup.