How to Make Frequency Distribution Table in Excel (4 Easy Ways)

Method 1 – Using Pivot Table to Make Frequency Distribution Table in Excel

Let’s take a dataset that includes some salesman’s name, product, and sales amount. We want to find out the frequency between a given amount. Steps:

Make Frequency Distribution Table Using Pivot Table

Make Frequency Distribution Table Using Pivot Table

Make Frequency Distribution Table Using Pivot Table

Make Frequency Distribution Table Using Pivot Table

Make Frequency Distribution Table Using Pivot Table

Make Frequency Distribution Table Using Pivot Table

Make Frequency Distribution Table Using Pivot Table

Method 2 – Inserting Excel FREQUENCY Function to Make Distribution Table

To use the FREQUENCY function, we take a dataset that includes some student name and their exam marks. We want to get the frequency of these marks.

Steps:

Make Frequency Distribution Table in Excel

=FREQUENCY(C5:C16,F5:F14)

Make Frequency Distribution Table in Excel

Note

Here, we take a higher range as bins because the function searches for values lower than the higher range.

Method 3 – Applying COUNTIFS Function to Create Frequency Distribution Table

Steps

=COUNTIFS(C5:C16," <="&10)

Make Frequency Distribution Table Using COUNTIFS Function

Breakdown of the Formula

Here, the range of cells is C5 to C16. The condition is less or equal to 10. The COUNTIFS function returns the total number of occurrences that is less than or equal to 10.

Make Frequency Distribution Table Using COUNTIFS Function

=COUNTIFS($C$5:$C$16,">"&10,$C$5:$C$16," <="&20)

Make Frequency Distribution Table Using COUNTIFS Function

Breakdown of the Formula

Make Frequency Distribution Table Using COUNTIFS Function

=COUNTIFS($C$5:$C$16,">"&20,$C$5:$C$16," <="&30)

Make Frequency Distribution Table Using COUNTIFS Function

Make Frequency Distribution Table Using COUNTIFS Function

Method 4 – Using Excel Data Analysis Tool to Develop Frequency Table

Steps

Make Frequency Distribution Table Using Data Analysis

Make Frequency Distribution Table Using Data Analysis

Make Frequency Distribution Table Using Data Analysis

Make Frequency Distribution Table

Download Practice Workbook