How to Make a Frequency Distribution Table in Excel (6 Ways)

While working in Excel, we often need to make a distribution table. You can make a frequency distribution table in Excel in a good number of ways. Here, we have summarized a total of 7 methods in this article.

Except for these 6 methods, if you know of any other techniques, let me know in the comment section.

Download Excel Workbook

Terminologies of a Frequency Distribution Table

Before going into the discussion of how to make a frequency distribution table in Excel, let’s introduce you to the terminology of a frequency distribution table.

Look at the following numbers. These are the Math scores of 20 students on an exam.

40, 43, 54, 62, 88, 31, 94, 83, 81, 75, 62, 53, 62, 83, 90, 67, 58, 100, 74, 59.

Just think of yourself as the teacher of these students.

Your task is to categorize the above scores to find out –

• How many students got A
• How many students got A-
• How many students got B
• How many students got C
• How many students got D
• And how many students failed (grade F) on the exam.

As the number of students is only 20, you can make a frequency distribution table manually without using any formula or sophisticated tool (for example, Pivot Table) in Excel. But if you are a statistician or work with big data, you might have to deal with thousands of numbers, if not millions of numbers. And one thing is sure: you cannot avoid the errors that might arise from a manual process.

In the following image, you see we have made a frequency distribution table. We did it manually, and it is just to introduce you to the terms related to a frequency distribution table.

• Bin: In the above image, there are 6 bins. They are >=80, 70-79, 60-69, 50-59, 40-49, and < 40.
• Bin Size: The size of the first bin (>=80) is 21. From 80 to 100, there are 21 numbers. The size of the second bin (70-79), third bin (60-69), fourth bin (50-59), and fifth bin (40-49) is 10 as there are 10 numbers in every bin. The size of the last bin (<40) is 40 as from 0 to 39 there are 40 values.
• Frequency: Frequency is how many values are counted for a bin. For example, for bin 70-79 we have found 2 scores. So the frequency of bin 70-79 is 2. For bin 50-59 we have found 4 scores. So the frequency of bin 50-59 is 4.
• Cumulative Frequency: You get the cumulative frequency from the standard frequency. In the above image, you see there is a Cumulative Frequency column. The first frequency is 7, which is the same as the standard frequency of 7 on the left. The next cumulative frequency is 9. 9 is found by summing standard frequencies 7 and 2 (7+2=9). In the same way, you can find the next cumulative frequency 13 (7+2+4), the next one 17 ( 7+2+4+4), the next cumulative frequency at 19 ( 7+2+4+4+2), and the last one 20 ( 7+2+4+4+2+1).

So, you now know the terminologies related to a frequency distribution table.

Prepare Dataset to Make a Frequency Distribution Table

Before you make a frequency distribution table in Excel, you have to prepare your data in the following ways:

• At first, find out the lowest and highest values in your data set. You can use the Excel MIN function and MAX function to find out the lowest and highest value respectively. Or you can use Excel’s features: Sort Smallest to Largest, Sort Largest to Smallest, or Sort to sort data and then find out the smallest and largest values from a data set. We prefer you to use the MIN and MAX functions.  These two will not change your data arrangement.
• Then decide how many bins you want to create. It is better to keep your number of bins between 5 and 15. 10 bins is ideal.
• Bin size will depend on how many bins you want to create. Say the lowest value is 23 and the highest value is 252. And you want to make 10 bins. Your bin size will be: (Highest value – Lowest value)/Bin Size = (252-23)/10=22.9. 22.9 or 23 is not a good bin size. We make it to 25.
• Now time to decide where you will start your bins. In the above example, it is not a good idea to start with the number 23. Let’s start with the number 21. So, the bins will be: 21-45, 46-70, 71-95, 96-120, 121-145, 146-170, 171-195, 196-220, 221-245, and 246-270.
• In the FREQUENCY function there is a parameter bins_array. To find that bins_array you have to use the highest value of the bins. For example, for the above bins, the bins_array will be: 45, 70, 95, 120, 145, 170, 195, 220, 245, and 270. Just remember this information. If you don’t understand, don’t worry. The concept will be clearer to you when you finish this tutorial.

7 Methods to Make a Frequency Distribution Table in Excel

In this section of the article, we are going to learn 7 easy ways to make a frequency distribution table in Excel.

Not to mention that we have used the Microsoft Excel 365 version for this article; you can use any other version according to your convenience.

1. Using PivotTable

Using PivotTable to create an Excel frequency distribution table is one of the easiest ways. In the following dataset, we have a record of 221 students and their test scores. Our goal is to separate the students according to a ten-point range (1–10, 11–20, and so on).

Let’s follow the steps mentioned below.

Step 01: Inserting Pivot Table

• Firstly, select any cell within the table.
• Then, click on the Insert tab.
• After that, in the Tables group choose the PivotTable option.

As a result, the Create PivotTable dialog box will appear on your worksheet as shown in the following image.

• In the Create PivotTable dialog box, choose the New Worksheet option.
• Then click on OK.

Following that, you will be able to see the PivotTable Fields task pane as shown in the image below.

Step 02: Placing Score field in the Rows area

• Firstly, place the Score field in the Rows area in the PivotTable Fields task pane.

To place a field in an area, you have to take your mouse pointer over the field; the mouse pointer will turn into a four-headed black arrow icon. Now click on your mouse and drag until you reach your area. When you are over the area, just release the mouse.

Note: You can also right-click on a field, and then can choose the Add to Row Labels option from the drop-down.

Step 03: Placing Student field in the Values area

• Following the same way, place the Student field in the Values area.

Values of the Student field are summarized by counts and you get a pivot table report like the below image.

Step 04: Grouping to get Ten-points Bin or Range

Now we’re going to make a grouping of ten-point range (1–10, 11–20, and so on).

• Firstly, right-click on any value in the Score field column in the pivot table, and a shortcut menu will appear.
• Following that, choose Group from the options of the shortcut menu.

Step 05: Getting the Grouped Pivot Table

• In the Grouping dialog box, you see the Starting at value is 27 as 27 is the lowest value of the score field. We want to make a frequency distribution as 21-30, 31-40, 41-50, and so on. So, we entered 21 as the Starting at value.
• After that, we entered Ending at value as 100.
• Then, we used By value as 10 as each bin will have 10 values.
• Following that, click the OK button.

Consequently, you will get a pivot table report like the following picture.

Step 06: Creating Histogram/Frequency Distribution Table and Graph

• Firstly, select any cell from the PivotTable.
• Now, go to the Insert tab from the Ribbon.
• After that, select the Insert Column and Bar Chart option.
• Then, choose the Clustered Column from the drop-down.

Consequently, you will be able to see the following chart on your worksheet.

Note: We have used an equal size range (1-10, 11-20, and so on) to create groups automatically in our example. If you don’t want to group the items in equal-sized ranges, you can create your own groups. Say, you may want to assign letter grades (A+, A, B, C, and so on) based on the students’ scores. To do this type of grouping, select the rows for the first group, right-click, and then choose Group from the shortcut menu. Repeat these steps for each new group you want to create. Then change the default group names with more meaningful names. 

Read More: How to Make a Relative Frequency Histogram in Excel (3 Examples)

2. Utilizing COUNTIFS Function

Now, we are going to learn how we can make a frequency distribution table in Excel by using the COUNTIFS function.

To show you how to make a frequency distribution table in Excel using the COUNTIFS function, we will use 3 examples.

Say your company surveyed 100 people to know two things:

• How many children each of the surveyees has.
• And their yearly income.

It is demonstrated in the following dataset.

Your boss ordered you to make two frequency distribution tables: one for No. of Children and another one for Income (Yearly).

Before making the frequency distribution, let’s give the ranges some unique names.

• The No. of Children range is C5: C104, I will name it Children.
• And the Yearly Income range is D5: D104, I will name it as Income.

You can use any 1 of the mentioned methods in this article to name the ranges in Excel.

Example 01: Frequency Distribution of No. of Children’s Column

• Firstly, use the formula in cell K4 to get the Highest Value in the No. of Children’s column.

• Now, hit ENTER.

As a result, you will get the following output on your worksheet.

• Following that, enter the formula given below in cell K5 to obtain the Lowest Value of the column named No. of Children.

Consequently, you will have the lowest value in the No. of Children column, as shown in the following image.

So, for column No. of Children, there is no use in making a frequency distribution like 0-1, 2-3, and 4-5. For this reason, we will use straight 0, 1, 2, 3, 4, and 5 as shown in the following image.

• Now, enter the following formula in cell G5.

Here, cell F5 refers to the cell of column No. of Children.

• After that, press ENTER.

Consequently, you will see the following image on your screen.

• Subsequently, use the AutoFill feature of Excel to get the rest of the outputs in the Frequency column.

• Then, insert the following formula in cell H5.

Here, cell G5 indicates the cell of column Frequency.

• Afterward, hit ENTER.

As a result, you will get the following output as shown in the picture below.

• Following that, in cell H6 use the following formula.

Here, cell H5 refers to the first cell of the column named Cumulative Frequency.

• Next, press ENTER.

Subsequently, you will have the following output on your worksheet.

• Now, by using the AutoFill option of Excel, you will get the remaining outputs of the Cumulative Frequency column.

Example 02: Frequency Distribution of Income (Yearly) Column

The lowest and highest values of the Income column are 20,000 and 180,000 respectively. Say you want to make a frequency distribution using the following bins:

• 50000 or less
• 50001 – 70000
• 70001 – 90000
• 90001 – 110000
• 110001 – 130000
• 130001 – 150000
• Over 150000
• Now, input the above bins manually like the image below.

Here, we also defined the bins_array values (you know, the highest values of the bins make the bins_array. In the image, you see that the last bin has no highest value, so the bins_array value for this bin is blank).

• Following that, for the 1st bin, enter the following formula in cell H13.

Here, cell G13 indicates the cell of the column named bins_array.

• Now, press ENTER.

Consequently, you will have the following output on your worksheet.

• Now, in cell H14 enter the following formula.

• After that, press ENTER.

Consequently, you will get the following output on your worksheet.

• Then, drag the Fill Handle up to cell H18 and you will get the following output in the Frequency column.

• Now, in cell H19 use the formula given below.

• Following that, hit ENTER.

As a result, you will get all of the values in the Frequency column as marked in the image below.

Note: Here, we have used a different formula for different cells. Because the bin sizes are not equal here. The first and the last bin sizes are different and the remaining bin sizes are equal.

• After that, use the steps mentioned earlier to obtain the following outputs in the Cumulative Frequency column.

Example 03: Frequency Distribution from Texts

Now, look at the following dataset. The Names column has a total of 50 names. Our first job is to list the unique names in a separate column. The next job is to find out the occurrences (Frequencies) of the Names in the column.

Let’s follow the steps mentioned below.

Steps:

• Firstly, go to the Data tab. In the Sort & Filter group of commands click on the Advanced command.

As a result, the Advanced Filter dialog box will appear.

• Under Action you will find two options: Filter the list, in-place, and Copy to another location. Select the Copy to another location radio button.
• Following that, in the List range field, we will insert range $B$4:$B$54 (including the column heading Names).
• Now, let the Criteria range as blank. In the Copy to the field, input $D$4.
• Finally, select the checkbox Unique records only and click on OK.

Consequently, you will get a list of unique records in cell D5 like the image below.

Now let’s find out the Frequency and Cumulative Frequency of these names.

• Firstly, enter the following formula in cell E5.

Here, the range $B$5:$B$54 indicates the range of Names and cell D5 refers to the cell of the unique Names.

• After that, hit ENTER.

As a result, you will then get the Frequency of the unique names from the range as shown in the following image.

• Now, using the AutoFill feature of Excel, we can get the rest of the outputs.

• Following that, use the steps mentioned earlier to obtain the following outputs in the Cumulative Frequency column.

Read More: How to Calculate Cumulative Frequency Percentage in Excel (6 Ways)

3. Applying FREQUENCY Function

Applying the FREQUENCY function is another efficient way to make a frequency distribution table in Excel. Let’s show you how to use the FREQUENCY function to make frequency distribution by using the following steps.

Steps:

• Firstly, insert the Income ranges and the bins_array values as shown in the following picture.

• Following that, enter the formula given below in cell D5.

Here, the range $C$5:$C$10 represents the range of cells in the column bins_array.

• Now, press ENTER.

As a result, you will get the Frequency for all the ranges at once.

• Next, use the steps mentioned previously to obtain the following outputs in the Cumulative Frequency column.

Read More: How to Find Mean of Frequency Distribution in Excel (4 Easy Ways)

4. Utilizing INDEX and FREQUENCY Functions

In this section of the article, we will use the INDEX function and the FREQUENCY function to make a frequency distribution table in Excel. Let’s follow the steps mentioned below to do this.

Steps:

• Firstly, insert the Income ranges and bins_array values as marked in the following image.

• Following that, enter the following formula in cell E5.

Here, the range $D$5:$D$10 refers to the range of cells of column bins_array, and cell B5 indicates the serial numbers.

• Now, press ENTER.

Subsequently, you will have the Frequency for the first Income range.

• At this stage, you can use the AutoFill feature of Excel to get the remaining outputs of the Frequency column.

• Then, use the same steps as mentioned earlier to get the following outputs in the Cumulative Frequency column.

Read More: How to Calculate Relative Frequency Distribution in Excel (2 Methods)

5. Using SUM and IF Functions

Now, we are going to learn how to make a frequency distribution table in Excel using the SUM and IF functions. It is quite a simple method. Let’s follow along.

Steps:

• Firstly, enter the Income ranges and the bins_array values as shown in the following image.

• Following that, enter the following formula in cell D5.

Here, cell C5 refers to the cell of the bins_array column.

• After that, hit ENTER.

Subsequently, you have the following output on your worksheet.

• Now, in cell D6, insert the formula given below.

• Then, press ENTER.

As a result, you will have the Frequency for the 2nd range.

• Next, drag the Fill Handle up to cell D10 to copy the formula in these cells and you will have the following output.

• Afterward, use the following formula in cell D11.

• Following that, press ENTER.

As a result, you will get the Frequency for all the ranges.

Note: Here, we have used different formulas for different cells. Because the bin sizes are not equal here. The first and the last bin sizes are different, and the remaining bin sizes are equal.

• After that use the steps mentioned previously to obtain the following outputs in the Cumulative Frequency column.

Read More: How to Create a Grouped Frequency Distribution in Excel (3 Easy Ways)

6. Applying SUMPRODUCT Function

In this portion of the article, we will apply the SUMPRODUCT function to make a frequency distribution table in Excel. Let’s use the following steps discussed below.

Steps:

• Firstly, insert the Income ranges and the bins_array values as shown in the following image.

• After that, enter the following formula in cell D5.

Here, cell C5 refers to the cell of the column bins_array.

• Now, press ENTER.

Subsequently, you will have the following output as marked in the following image.

• Following that, use the following formula in cell D6.

• Then, press ENTER.

As a result, you will have the Frequency for the 2nd Income range.

• Now, drag the Fill Handle up to cell D10 and you will get the following outputs in your worksheet.

• Afterward, use the following formula in cell D11.

• Subsequently, press ENTER.

As a result, you will have the Frequency for all the Income ranges as shown in the image below.

• Following that, use the same steps as mentioned earlier to get the following outputs in the Cumulative Frequency column.

Note: Here, we have used different formulas for different cells. Because the bin sizes are not equal here. The first and the last bin sizes are different and the remaining bin sizes are equal.

Read More: How to Calculate Standard Deviation of a Frequency Distribution in Excel

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to make a frequency distribution table in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!

Related Articles

• How to Make a Relative Frequency Table in Excel (with Easy Steps)
• Calculate Cumulative Relative Frequency in Excel (4 Examples)
• How to Make a Categorical Frequency Table in Excel (3 Easy Methods)