How To Use the FREQUENCY Function in Excel in 4 Simple Steps

By Indeed Editorial Team

Published March 25, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

When you have large amounts of data in nonnumerical order, you may wonder how to organize and interpret it. One useful way to categorize your data is by establishing intervals and determining the number of values that appear in each interval. You can use the FREQUENCY function in Excel to efficiently sort your spreadsheets in this manner. In this article, we teach you how to use the FREQUENCY function in Excel and provide expert tips.

Related: Frequency: What It Is and How To Calculate It

What is the FREQUENCY function in Excel?

The FREQUENCY function in Excel is a statistical formula that computers how many values in a data set appear within specified intervals. Users input a data range and designate the specified intervals by inputting the upper limits of each interval. When using the FREQUENCY function, the first argument is "data_array" and requires you to designate the data range. The second argument, "bins_array," is for indicating the upper limits of each interval. The function requires users to input both arguments.

Related: Frequency Distribution: What It Is and When To Use It

When to use the FREQUENCY function in Excel

Many professionals find the FREQUENCY function useful for analyzing their data. For instance, a teacher might want to know the number of test scores that appear in specific intervals to evaluate classroom performance. If the results show that students score in the lower rangers, the teacher can adjust their lesson plans accordingly. Financial professionals also use this function to create frequency distribution tables that note price action and identify economic trends.

Related: How To Create a Cumulative Frequency Distribution Table in 3 Steps

How to use the FREQUENCY function in Excel

Here's how to use the FREQUENCY function in Excel:

1. Enter raw data

Enter your raw data into a row, column or combination or rows and columns. You may enter this data manually or copy and paste it from an external source. The values can appear in any order.

For these steps, consider someone organizing a 5K for a nonprofit. They want to determine how many participants are within different age ranges to order the appropriate number of trophies for each age group. They might begin by extracting a list of participant ages from sign-up forms and pasting it into column A, which the chart below shows. Later steps discuss the additional information in this example spreadsheet.

ABCD1AgeAge GroupsUpper Limit of Age GroupFrequency270-1818431419-3030142531-5959251460+
1631

731

866

915

2. Specify intervals

In a separate column, specify the intervals you want the FREQUENCY function to consider. Designate the lower limit of the first interval as 0 and include an upper limit. In the example above, the first interval is for ages 0 to 18.

The next interval begins with the number above the upper limit of the previous interval. Note that the size of the intervals can vary. For the final interval, you can include a number with a "+" symbol next to it to indicate the total number of values above that number. In the example above, the final interval notes all participants who are 60 years old or older. Alternatively, you can establish an upper limit for the final interval. For instance, the 5K organizer could've made the final interval 60-75 if they knew that the oldest participant was 75 years old.

3. Create an "Upper Limit" column

Next to the intervals column, create an "Upper Limit" column that notes the upper limit of each interval. In the example above, 18 is the upper limit for 0-18, 30 for 19-30 and 59 for 31-59. Note that the final interval doesn't have a corresponding value in the "Upper Limit" column. This is because the FREQUENCY function automatically calculates all values above the upper limit of the second-to-last interval.

4. Create a "Frequency" column

Create a "Frequency" column to the right of the "Upper Limit" column. Select the first cell in this column and drag your selection down to the "Frequency" cell that corresponds with the final interval. Type "=FREQUENCY(" and select the raw date you entered in step one. Add a comma and select the upper limit values. Add a closing parenthesis and press "Enter." In the example above, the formula would appear as "=FREQUENCY(A2:A9, C2:4)." The frequencies would appear as 4, 1, 2 and 1.

Related: Basic Excel Formulas and How To Use Them

Tips for using the FREQUENCY function in Excel

Here are some tips for using the FREQUENCY function in Excel:

Select blank cells when making your selection

When selecting raw data to create the first argument of the FREQUENCY function, you may notice blank cells. These cells may occur because you skipped a cell when inputting data or because a user left the cell blank. Regardless, many people try not to select blank cells because they think they interfere with the formula.

Understand what happens when arguments contain no values

Even though the FREQUENCY function requires both arguments, it's possible to enter arguments that contain no values. For instance, you might enter C2:C10 as the first argument, even if this range of cells contains no values. When the "data_array" argument contains no values, the function returns an array of zeros. When the "bins_array" argument contains no values, the function returns the number of elements within the data selection.

Understand the "#N/A error"

When using the FREQUENCY function, you may encounter the "#N/A error." Knowing what causes this error can help you adjust your formula to make it more accurate. One common reason for this error is that you left an argument blank. You might also experience the "#N/A error" if you enter the array formula into a range of cells that goes beyond the number of specified intervals.

Related: How To Include Excel Skills on Your Resume

Know when to use the COUNTIF function instead

Some people think that the FREQUENCY function is for counting the number of times a specific value appears in a data set. If you want to perform this task, consider using the COUNTIF function instead. This formula analyzes a data set and returns the number of cells that meet a specified condition.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles