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.

**Table of Contents**hide

## 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.

In the above image, there are__Bin:__**6**bins. They are**>=80**,**70-79**,**60-69**,**50-59**,**40-49**, and**< 40**.The size of the first bin (__Bin Size:__**>=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 is how many values are counted for a bin. For example, for bin__Frequency:__**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**.You get the cumulative frequency from the standard frequency. In the above image, you see there is a__Cumulative Frequency:__**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 1**7 ( 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.

`=MAX(Children)`

- 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*.

`=MIN(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**.

`=COUNTIFS(Children, "="&F5)`

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.

`=G5`

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.

`=H5+G6`

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**.

`=COUNTIFS(Income, "<="&G13)`

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.

`=COUNTIFS(Income, ">"&G13, Income, "<="&G14)`

- 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.

`=COUNTIFS(Income,">150000")`

- 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**.

`=COUNTIF($B$5:$B$54, D5)`

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**.

`=FREQUENCY(Income,$C$5:$C$10)`

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**.

`[email protected](FREQUENCY(Income,$D$5:$D$10),B5)`

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**.

`=SUM(IF(Income<=C5,1,0))`

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.

`=SUM(IF((Income>C5)*(Income<=C6),1,0))`

- 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**.

`=SUM(IF((Income>C10), 1, 0))`

- 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**.

`=SUMPRODUCT(--(Income<=C5))`

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**.

`=SUMPRODUCT((Income>C5)*(Income<=C6))`

- 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**.

`=SUMPRODUCT(--(Income>C10))`

- 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!

20 30 45 66 35

34 55 27 44 66

44 23 78 45 77

55 45 87 98 97

ques? how to insert all these values into one column in excel

Hi Raj,

Do you want to put all the row values in a single cell? You can try out the following formula to concatenate them. But it will be converted into a string.

If you want to know something else entirely, kindly elaborate on your problem. We will try to help the best we can.

Thank you this is wonderfull

Glad it had been helpful for you.