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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Grouped Frequency Distribution in Excel


How to Create a Grouped Frequency Distribution in Excel: 3 Easy Ways

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

Excel Functions to Get a Grouped Frequency Distribution

  • In the second step, select Cell G5 and type the formula:
=FREQUENCY(C5:C15,F5:F10)

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

Read More: How to Do a Frequency Distribution on Excel


1.2 Insert COUNTIFS Function

Another way to create a grouped frequency distribution with formulas is to use the COUNTIFS function. This function counts the number of cells specified by a set of criteria. It also has 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.

Excel Functions to Get a Grouped Frequency Distribution

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.

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

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

Excel Functions to Get a Grouped Frequency Distribution

Read More: How to Make Frequency Distribution Table in Excel


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

Create a Grouped Frequency Distribution with Excel Pivot Table

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

Create a Grouped Frequency Distribution with Excel Pivot Table

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

Create a Grouped Frequency Distribution with Excel Pivot Table

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

Create a Grouped Frequency Distribution with Excel Pivot Table

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

Create a Grouped Frequency Distribution with Excel Pivot Table

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

Create a Grouped Frequency Distribution with Excel Pivot Table

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

Create a Grouped Frequency Distribution with Excel Pivot Table

  • 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 Find Mean of Frequency Distribution in Excel


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


Download Practice Book


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. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


<< Go Back to Frequency Distribution in Excel | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo