How to Use the FREQUENCY Function to Calculate Number Distribution in Excel

When you are analyzing a massive Excel dataset containing survey results or test scores, understanding the exact distribution of your data is critical. If you have 5,000 student test scores ranging from 0 to 100, you need to know exactly how many students scored between 90-100 (A), 80-89 (B), and 70-79 (C). Manually sorting the list and counting the rows is impossible. Instead, you can use the mathematically complex FREQUENCY() function to instantly drop thousands of numbers into specific data buckets.

Understanding the Two Arrays

Unlike standard Excel formulas that look at a single cell, the FREQUENCY() function requires two massive arrays of data to work properly.

1. The Data Array: This is the massive list of raw numbers you want to analyze (e.g., the 5,000 test scores located in column A).
2. The Bins Array: This is a much smaller list of “threshold” numbers you must type out yourself. If you want to group scores by letter grade, your “bins” would be the maximum score for each tier: 69 (F), 79 (C), 89 (B), and 100 (A).

Step-by-Step Instructions

Assume your 5,000 raw test scores are in column A (A1:A5000). First, you must define your Bins.

1. In an empty column (like Column C), type your maximum thresholds vertically. In C1 type 69, in C2 type 79, in C3 type 89, and in C4 type 100.
2. Highlight the empty cells directly next to your bins (D1 through D4). This is where the results will appear.
3. With all four empty cells highlighted, click into the formula bar at the top of the screen and type the formula:

=FREQUENCY(A1:A5000, C1:C4)

Executing the Array Calculation

Because this is a complex array calculation (meaning it outputs multiple answers into multiple cells simultaneously), you cannot just press Enter.

4. Hold down Ctrl + Shift on your keyboard, and then press Enter. (If you are using the newest version of Excel 365, standard Enter will work, but the Ctrl+Shift+Enter method is safer for all versions).

Excel will instantly process the 5,000 numbers. Cell D1 will output exactly how many scores were 69 or below. Cell D2 will output how many were between 70 and 79. Cell D3 will output the 80 to 89 range, and cell D4 will output the 90 to 100 range. You have instantly mapped the mathematical distribution of your entire database.