In this article, we will learn to **create a grouped frequency distribution in Excel**. A grouped frequency distribution indicates the number of occurrences of data in an interval. Each group has a lower and an upper limit. Today, we will use **3 **easy methods. After reading the article, one can easily create a grouped frequency distribution in Excel.

**Table of Contents**hide

## Download Practice Book

Download the practice book here.

## 3 Easy Ways to Create a Grouped Frequency Distribution in Excel

To explain the methods, we will use a dataset that contains information about the **Marks **of some students. We will try to create a grouped frequency distribution and show it in a chart or graph. Throughout the whole article, we will use the same dataset.

### 1. Excel Functions to Get a Grouped Frequency Distribution

Some Excel functions can easily calculate the frequency in a set of data. Today, we will use two functions for that purpose. Our first function is **the FREQUENCY function**. And the second function is **the COUNTIFS function**. We will discuss the use of these functions in the following section.

#### 1.1 Apply FREQUENCY Function

You can use the **FREQUENCY **function to create a grouped frequency distribution. It is an array formula. The **FREQUENCY **function determines how often a value appears in a range. It has two compulsory arguments: the **data array **and the **bins array**. You need to enter the **dataset **in place of the **data array** and the **upper limit** in place of the **bins array**. Let’s follow the steps below to learn more.

**STEPS:**

- First of all, you need to choose the
**upper**and**lower**limit and enter them in your dataset like the picture below. - Here, we have grouped the marks by
**10**. The starting group is**31-40**where the lower limit is**31**and the upper limit is**40**. In Excel, these groups are called bins. So, we have a total of**7**bins here.

- In the second step, select
**Cell G5**and type the formula:

`=FREQUENCY(C5:C15,F5:F10)`

- After that, press
**Ctrl**+**Shift**+**Enter**to see the**frequency distribution**.

- Now, to plot the result, select the upper limits.

- Next, go to the
**Insert**tab and click on the**Insert Column**icon. - Then, select the
**Clustered Column**icon. A chart will appear on the sheet.

- In the following step,
**right-click**on the chart and click on**Select Data**. It will open the**Select Data Source**dialog box.

- In the
**Select Data Source**dialog box, enter the**Frequency**in the**Legend Entries**section. - Then, click on
**Edit**in the**Horizontal Axis Labels**section.

- Select the
**Lower**and**Upper**limits like the picture below and click**OK**to proceed.

- As a result, the
**Select Data Source**dialog box will look like this. - Just click
**OK**.

- In the end, you will see the frequency on a graph.

#### 1.2 Insert COUNTIFS Function

Another way to create a grouped frequency distribution with formulas is to use the **COUNTIFS **function. The **COUNTIFS **function counts the number of cells specified by a set of criteria. It has also two required arguments. You must enter the **criteria range** and the **criteria **to get a result. Let’s pay attention to the steps below to know more.

**STEPS:**

- In the first place, select
**Cell G5**and type the formula:

`=COUNTIFS($C$5:$C$15,">="&E5,$C$5:$C$15,"<="&F5)`

- Press
**Enter**to see the results.

Here, the **COUNTIFS **function counts the occurrence of marks in the **range C5:C15 **when it’s greater than **E5 **and less than **F5**.

- After that, use the
**Fill Handle**down to copy the formula.

- As a result, you will see results like the picture below.

- Moreover, you can also generate a plot to represent the frequencies by following the previous sub-method.

**Read More: ****How to Make Frequency Distribution Table in Excel (4 Easy Ways)**

**Similar Readings**

**How to Calculate Cumulative Frequency Percentage in Excel (6 Ways)****Calculate Relative Frequency Distribution in Excel (2 Methods)****How to Calculate Cumulative Relative Frequency in Excel (4 Examples)**

### 2. Create a Grouped Frequency Distribution with Excel Pivot Table

We can also use **the Pivot Table feature** of Excel to create a grouped frequency distribution. Here, we don’t need to set the upper and lower limit manually. Instead, we can finish the whole process with a few simple steps. So, without any delay, let’s follow the steps below.

**STEPS:**

- To begin with, select the
**Name**and**Marks**of the students. Here, we have selected**Cell B5**to**C15**.

- Secondly, go to the
**Insert**tab and select**PivotTable**. A dialog box will pop up.

- Click
**OK**to proceed. A new worksheet and**PivotTable Fields**will appear.

- In the
**PivotTable Fields**section, click on**Marks**and drag it to the**Rows**&**Values**sections.

- After that, click on
**Sum of Marks**and select**Value Field Settings**.

- In the
**Value Field Settings**dialog box, select**Count**and click on**OK**.

- In the following step,
**right-click**on**Cell A4**and select**Group**from the drop-down menu.

- Then, type the
**Starting**point,**Ending**point, and the**Group By**value. Here, we wanted to group them by**10**.

- Click
**OK**to see the groups and frequencies.

- Next, select the data and go to the
**Insert**tab. - Select the
**Insert Column**icon and then, click on the**Clustered Column**icon.

- Finally, you will see a chart of the
**groups**and**frequencies**.

**Read More: ****How to Make a Categorical Frequency Table in Excel (3 Easy Methods)**

### 3. Apply Histogram to Create a Grouped Frequency Distribution in Excel

In the last method, we will use the **Histogram **tool to create a grouped frequency distribution in Excel. It’s a straightforward method with simple steps. Again, we will use the same dataset. Let’s observe the steps below for more information.

**STEPS:**

- Firstly, go to the
**Data**tab and select**Data Analysis**.

- After that, select
**Histogram**from the**Data Analysis**message box and click**OK**.

- In the following step, type the
**Input Range**&**Bin Range**and check**Chart Output**. - Click
**OK**to move forward.

- Finally, you will see the
**Upper Limit**of the groups and the**Frequencies**both in a table and chart.

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

## Conclusion

In this article, we have demonstrated **3 **easy methods to **Create a Grouped Frequency Distribution in Excel**. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Visit **the ExcelDemy website** for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.