Calculate Frequency Distribution in Excel

Frequency Distribution

A Frequency Distribution is a summary of how often each value occurs by grouping values together.

For example, you have data for class sections with the number of students in each section.

Excel : Frequency Distribution

There are multiple ways to calculate frequency distribution (table) with Excel.

With COUNTIFS Function
With FREQUENCY Function

Calculate Frequency Distribution in Excel

Step 1 : Enter the above data in cells B3:C15. The first row of table has headers.

The same data entered into a sheet in excel appears as follows :

Step 2 : Select the desired class intervals

Step 3 : Create a table with the columns - Class intervals, Lower limit, Upper limit and Frequency.

Step 4 : Calculate lower and upper limits

Suppose class interval column starts from cell E5 (excluding header).

Lower limit -

Enter the following formula in cell F5 and paste it down till the last row of the table.

=MID(E5,1,FIND("-",E5,1)-1)

Upper limit -

Enter the following formula in cell G5 and paste it down till the last row of the table.

=MID(E5,FIND("-",E5,1)+1,2)

Step 5 : Calculate the count in each class intervals.

Frequency Distribution Using COUNTIFS Function

To calculate the last column of the above table, enter the following formula in cell H5 and paste it down till the last row of the table.

=COUNTIFS($C$4:$C$15,">="&F5,$C$4:$C$15,"<="&G5)

Frequency Distribution Using FREQUENCY Function

Create a table with the columns - Class Intervals, Upper Limit and Number of Sections

Upper Limit can be calculated with the formula below in cell F5 -

=MID(E5,FIND("-",E5,1)+1,2)

Paste the formula down till cell F9. Make sure you enter double minus (--) before MID function.

In the next step, select range G5:G9 and then enter FREQUENCY Function

=FREQUENCY(C4:C15, F5:F9)

F5:F9 refers to bins of Upper Limit.

Press CTRL SHIFT ENTER to submit the above FREQUENCY formula as it's an array formula. If it is entered correctly, you would see formula wrapped in curly braces { }

Histogram

We can prepare histogram using frequency table.

Steps to Calculate Histogram

Highlight the values in the class intervals column (Column E) and Frequency column (Column H) of the frequency distribution table. (Hold down Ctrl key while highlighting two ranges)
Click on the Insert tab and select Clustered Column from the Column or Bar Chart dropdown menu.
Remove Series1: Click on the chart to select it, then right-click on Series1 in the chart legend and select Delete.

Adding axis title

Click on the chart.
Click the Chart Design tab under Chart Tools.
Click Add Chart Element in the Chart Layouts group.
Hover over Axis Titles.
Select Primary Horizontal.
Select Primary Vertical.

Changing the Scale of y-axis

Select the chart.
Right-click on the vertical axis (y-axis) of your chart.
Select Format Axis from the menu.
In the Format Axis pane, expand the Axis Options section if it's not already expanded.
Under Bounds, for Minimum, select Fixed and enter the lowest number you want on your Y-axis. For example, enter 0.
For Maximum, select Fixed and enter the number where you want the Y-axis to end. For example, enter 5.
Close the Format Axis pane.

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn