How to Use the FREQUENCY Function in Excel

Excel’s FREQUENCY function lets you count how many times values fall within specific ranges. For example, if you had the ages of a group of people in your spreadsheet, you could figure out how many people fall into different age ranges. Let’s take a look at how to calculate frequency distributions and, with a slight modification, frequency percentages.

What Does the FREQUENCY Function Do?

Excel’s FREQUENCY array function lets you calculate a dataset’s frequency distribution. You provide the numerical dataset (that’s the actual cells you use as your source), a list of bin thresholds (that’s the categories into which you’re sorting data), and then press Ctrl+Shift+Enter.

So, how might you use it? Well, here’s a quick example. Say you’re a teacher with a spreadsheet that shows all your student’s numerical test scores. You could use the FREQUENCY function to figure out how many students got an A, B, C, D, or F. The numerical test scores are the dataset and the letter grades form your bin thresholds.

You would apply the FREQUENCY function to a list of student’s test scores, and the function would count how many students got which letter grade by comparing each test score to the range of values that define the different letter grades.

If you round scores to the nearest tenth of a percent, these ranges would apply:

F <= 59.9 < D <= 69.9 < C <= 79.9 < B <= 89.9 < A

Excel would assign a score of 79.9 to the C range while a score of 98.2 would fall into the A range.  Excel would go through the list of test scores, categorize each score, count the total number of scores that fall into each range, and return an array with five cells showing the total number of scores in each range.

The FREQUENCY function requires two arrays as inputs: a “Data_array” and a “Bins_array.”  Arrays are simply lists of values. The “Data_array” needs to contain values—like the numerical grades for students—that Excel can compare to a series of thresholds defined in the “Bins_array”—like the letter grades in that same example.

Let’s Look at an Example

Read the remaining 42 paragraphs