Frequency distribution in Excel

  • Post author:
  • Post category:Uncategorized

A frequency distribution table and chart provide an easy way to present how our data is spread out over a specified range.  Through pivot tables, Excel allows us to create a frequency distribution table and chart.  

Below is an example of a frequency distribution chart created through pivot tables.  

Figure 1.  Final result: Frequency distribution

How to create a frequency distribution

Here are the steps to make a frequency distribution:

  • Prepare our data
  • Insert a pivot table
  • Insert a frequency distribution table
  • Insert a frequency distribution chart

Prepare our data

Below is a list of names and scores that we want to present as a frequency table and a frequency chart.  

Figure 2.  Sample data for frequency distribution

Insert pivot table

We insert a pivot table by selecting our data (B2:C12), then clicking Insert tab > PivotTable.  

Figure 3.  PivotTable option in Insert tab

In the Create PivotTable dialog box:

  • Tick Existing Worksheet
  • In Location, click the button and select cell E2

This will insert the pivot table in the existing worksheet in cell E2.  

Figure 4.  Create Pivot Table dialog box

  • In order to create a pivot table that shows the scores, tick and drag the Score field to the box for ROWS and VALUES.   

Figure 5.  Drag and drop the Score field

  • Under VALUES, let us change the function from SUM to COUNT by clicking the down arrow in Sum of Score and selecting Value Field Settings.  

Figure 6.  Value Field Settings option

  • Choose from the type of calculation and select Count.  

Figure 7.  Change type of calculation from Sum to Count

We have now created a pivot table showing the count per score.  

Figure 8.  Pivot table created

How to create a frequency distribution table

  • Click anywhere under Row Labels and select Group

Figure 9.  Select Group in menu options

The grouping default values will be the minimum and maximum values in our list, and grouping is set by 1.  

Figure 10.  Default grouping parameters

For this example, we want to group the scores by 5, starting from 75 to 100.  

Enter the following in the textboxes:

  • Starting at: 75
  • Ending at: 100
  • By: 5

Figure 11. Customize grouping parameters

Click OK and the frequency distribution table will be created, showing the frequency or count of scores per specific range.  

Figure 12.  Output: How to create a frequency distribution table

How to create a frequency distribution chart

Click anywhere on the pivot table then click Insert tab > PivotChart

Figure 13.  PivotChart in Insert tab

The Insert Chart dialog box will appear.  Select the Clustered Column chart type and click OK.  

Figure 14.  Insert Clustered Column chart

We will instantly create a frequency distribution chart, showing the score ranges in the horizontal axis and the corresponding frequencies in the vertical axis.  

Figure 15.  Output: Create frequency distribution chart

We can customize our frequency distribution chart by performing the following:

  • Change chart title to Scores Frequency Distribution
  • Delete the legend
  • Format the vertical axis (set maximum value to 5.0, major units to 1.0)
  • Change the chart design to Style 8

Finally, we have created a frequency distribution chart, as shown below.  

Figure 16.  Output: Create frequency distribution