How to Create Grouped Frequency Distribution in Excel

A grouped frequency distribution describes how frequently values in a dataset occur in specific grouped ranges.

The easiest way to create a grouped frequency distribution for a dataset in Excel is to use the Group feature within Pivot Tables.

The following example shows exactly how to do so.

Example: Create Grouped Frequency Distribution in Excel

Suppose we have the following dataset in Excel that shows the points scored by various basketball players:

Suppose we would like to create a grouped frequency distribution for this dataset.

To do so, highlight the range A1:A16, then click the Insert tab along the top ribbon, then click the PivotTable icon:

In the new window that appears, type in the following information and then click OK:

In the PivotTable Fields panel that appears on the right side of the screen, drag Points to the Rows box and the Values box.

Then click on the dropdown arrow next to Sum of Points and click Value Field Settings:

In the new window that appears, click Count in the Summarizes value field by box, then click OK:

Next, right click any of the values in the Row Labels column of the pivot table and then click Group:

In the new window that appears, you can define the group sizes.

We will choose to group the values from 1 to 20, in increments of 5:

Once we click OK, the pivot table will automatically group the data values from 1 to 20 in increments of 5 and calculate the frequency of each group:

From the grouped frequency distribution we can see:

• There are 3 values in the dataset between 1 and 5.
• There are 4 values in the dataset between 6 and 10.
• There are 5 values in the dataset between 11 and 15.
• There are 3 values in the dataset between 16 and 20.

Note that we chose to use group sizes of 5 for this grouped frequency distribution, but feel free to choose whatever group size you’d like based on your dataset.