How To Calculate Frequency In Excel: Step-by-Step Guide

You’re staring at a column of numbers. And you need it in Excel. And you need to know: how many values fall into each range? But where do you even start? A thousand rows of raw data. Test scores. Worth adding: the function names sound cryptic. But the steps feel finicky. So how many sales were between $100 and $500? And how many people scored between 70-80? In practice, sales figures. Website visits. You need a frequency distribution. You just want the answer, not a PhD in spreadsheet mechanics.

Let’s fix that.

What Is a Frequency Distribution in Excel?

At its heart, it’s a simple question: “How many times does this thing happen?” But in Excel, we’re usually asking it about ranges of numbers. A frequency distribution takes a list of numbers and groups them into buckets—or bins—you define. Then it counts how many numbers land in each bucket.

Think of it like sorting marbles. You have a big mixed bag of red, blue, and green marbles (your raw data). You pour the bag in, and each marble goes into its cup. Also, the number of marbles in each cup at the end? You set out three cups labeled “Red,” “Blue,” and “Green” (your bins). That’s the frequency.

In Excel, the “marbles” are your data points. The “cups” are your bin upper limits. We’ll do both. But there’s also a much simpler, more intuitive way using COUNTIFS. And the counting is done by a function. The classic tool is the FREQUENCY function. Because understanding the classic method explains the concept deeply. But knowing the modern method saves you time and headaches.

The Two Main Paths

There are essentially two ways to skin this cat:

1. It’s what most people should use today. Plus, it’s what you’ll find in old textbooks. The COUNTIFS function: This is the straightforward, formula-per-bin approach. Day to day, 2. Day to day, The FREQUENCY function: This is the legacy, array-formula powerhouse. Now, it’s powerful but has quirks. It’s transparent and easy to debug.

We’ll walk through both. You’ll see why one often feels like a black box and the other feels like you’re just counting out loud.

Why Bother With Frequency Distributions?

“Can’t I just sort the data and count?” Sure. Day to day, for ten numbers, absolutely. That said, for ten thousand? No way.

• It reveals patterns hidden in the noise. A list of 1,000 test scores is meaningless. A table showing 5 scores below 60, 120 between 60-70, 450 between 70-80… that tells a story. You see the shape of your data immediately—is it skewed left, skewed right, or bell-curved?
• It’s the foundation for charts. That table you just made? One click and you have a histogram. Histograms are arguably the most important chart for understanding the distribution of a single variable. Without the frequency table, you can’t build it properly in Excel.
• It enables analysis. You can’t calculate percentiles, quartiles, or do meaningful statistical sampling on raw, unsorted data. Frequency distributions are a core step in exploratory data analysis. In business, you’re looking at customer spend tiers, product defect rates, or website session durations. In science, it’s measurement data. In HR, it’s salary bands or tenure ranges. The use case is everywhere.

People get stuck because they treat it like a one-off calculation. It’s not. It’s a view of your data. Once you know how to build that view dynamically, you can slice and dice any dataset in seconds.

If you found this helpful, you might also enjoy which transformation will always map a parallelogram onto itself or who is responsible for avoiding a collision between two boats.

How to Calculate Frequency in Excel: The Step-by-Step

Let’s get our hands dirty. We’ll use this sample data: a list of 20 exam scores in cells A2:A21.

Method 1: The Classic FREQUENCY Function (The “Array Formula” Way)

This method feels old-school because it is. Also, you select a range of cells for the output, type one formula, and press Ctrl+Shift+Enter. If you just hit Enter, it breaks. This is the #1 reason people hate this function.

Step 1: Define Your Bins. Decide on your bucket boundaries. Let’s say we want scores in 10-point increments: 0-59, 60-69, 70-79, 80-89, 90-100. Our bin array will be the upper limits: 60, 70, 80, 90, 100. Put these in cells D2:D6.

Step 2: Select the Output Range. You need to select a vertical range of cells that has one more cell than your bin array. Why? Because FREQUENCY also counts values above the highest bin. So if you have 5 bins, you select 6 cells. Let’s select E2:E7.

Step 3: Enter the Formula. In the formula bar, type: =FREQUENCY(A2:A21, D2:D6) DO NOT HIT ENTER YET.

Step 4: The Magic Keystroke. Press Ctrl+Shift+Enter. If you did it right, Excel will wrap your formula in curly braces {} in the formula bar. You’ll see the counts spill down into your selected cells E2:E7.

The last cell (E7) will show how many scores are greater than 100. In our case, probably zero.

Why it feels weird: You’re not typing a formula in one cell. You’re telling Excel