Frequency Distribution In Excel Using Pivot Table Excel Formulas

How to Create Frequency Distribution in Excel?

Frequency distribution in Excel calculates the rate of a change happening over time in the data. There are two methods to find the frequency distribution. First, we need to categorize our data into data sets. Then, we can use the array formula frequency function, or in the “Data Analysis” tab, we can use the “Histogram” tool to calculate the frequency distribution. There are two methods:

Frequency Distribution in Excel using FormulasFrequency Distribution in Excel using Pivot Table

#1 Create Frequency Distribution using Excel COUNTIF Formula

Let us learn this concept with the help of an example. A corporate company conducted a yearly review, and everyone got a rating out of 10. They did a total of 50 employees rating.

Below is the rating data for 50 employees.

#2 Frequency Distribution in Excel using Pivot Table

Let us learn this concept with the help of examples.

We need to check how many people got a rating from 4 to 6, 6 to 8, and 8 to 10. We are not applying a Pivot Table. Rather, we will use the COUNTIF function to get the total. Before that, we created frequency levels like this. We are applying the COUNTIF function to get the total count. For example, in cell E2, we mention the COUNTIF function, which counts all the numbers in the range A2 to A52 which are less than or equal to 6. In cell E3, we have used the COUNTIFS function, which counts numbers if the number is greater than 6, but less than 8. In cell E4, we use the COUNTIFS function, which counts numbers if the number is greater than 8, but less than 10. Conclusion: We have results here: 19 employees’ rating is between 4 to 6, 14 employees’ rating is between 6 to 8, and 18 employees’ rating is between 8 to 10.

Conclusion: We have results here: 19 employees’ rating is between 4 to 6, 14 employees’ rating is between 6 to 8, and 18 employees’ rating is between 8 to 10.

Example #1

Let us explain to you an example of a frequency distribution in Excel. We have units sold data with the price of the product.

Here, we need to know how many units were sold between 15 and 30, how many were sold between 31 to 45, and so on.

Step 1: We must first select the data and apply a Pivot Table.
Step 2: Then, drag and drop the “Product Price” heading to “Rows” and “Units Sold” to “Values.”
Step 3: Now, the pivot summary report should be like this.
Step 4: Right-click on the “Product Price” column and select “Group.”
Step 5: Once we click on “Group,” it will open up the below dialog box.
Step 7: Click on the “OK.” Pivot grouped values like 15 to 30, 31 to 45, 46 to 60, etc.

Conclusion: Now, we can analyze the highest number of units sold when the price is between 15 to 29, i.e., 54819.

When the product price is between 30 to 44 units sold, the quantity is 53794. Similarly, the least number of products sold when the price is between 45 to 59, i.e., 10982.

Example #2

A survey on money spent on alcohol based on the age group. We have the data for different age groups and money spent every month. We need to determine which age group is spending more from this data.

In this data, the lowest age is 15, and the highest age is 72. So, we need to find out between 15 to 30, 30 to 45, 45 to 60, etc.

Below is the data.

Step 1: We must apply the PivotTable to this data. For example, in “Rows,” put “Age” and “Amt Spent” for “Values.”
Step 2: Now, the pivot summary report should be like this.
Step 4: Click on the “OK.” It will group the age and return the sum for the age group.

Conclusion: It is clear that the age group between 15 to 29 is spending more money on alcohol consumption, which is not a good sign.

But, the age group 30 to 44 spends less on alcohol. So, maybe they have realized the mistake they made at a young age.

Things to Remember

We need to identify the frequency level first before making the frequency distribution.Use a pivot table for frequency distributionFrequency DistributionFrequency distribution refers to the repetitiveness of a variable, i.e., the number of times a variable occurs in a data set. In excel, it is a function to tabulate or graphically represent the recurrence of a particular value in a group or at an interval.read more is always dynamic.Using the COUNTIF function, we must create frequency levels in Excel manually.

How to Create Frequency Distribution in Excel?#

#1 Create Frequency Distribution using Excel COUNTIF Formula#

#2 Frequency Distribution in Excel using Pivot Table#

Example #1#

Example #2#

Things to Remember#

Recommended Articles#