How to Do a Frequency Distribution on Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

A frequency distribution can help you figure out what proportion of distribution is made up of specific values. By grouping values together, a frequency distribution summarizes how often each value occurs. Today, In this article, we’ll get to learn how to do a frequency distribution on Excel effectively with appropriate illustrations.


Introduction to Frequency Distribution

By grouping values together, a Frequency Distribution summarizes how often each value occurs. A frequency distribution is a data set or graph designed to represent the frequencies of occurrence outcomes in the discipline of statistics. Each frequency distribution result is a recurrent occurrence that may be seen several times from the data collection.


How to Do a Frequency Distribution on Excel: 3 Suitable Ways

Let’s say, we have a dataset that contains information about several Sales Representatives of XYZ Group. The name of the Sales Representatives, types of the products, and the Quantity of the products are given in column B, C, and D respectively. We will use the FREQUENCY, and COUTIFS functions, and create a Pivot Table to do a frequency distribution on Excel. Here’s an overview of the dataset for today’s task.

do a frequency distribution on excel


1. Use FREQUENCY Function to Do a Frequency Distribution

Here, we’ll learn how to calculate the frequency distribution by using the FREQUENCY function. Please follow the instructions below to learn!

Steps:

  • First of all, select cell G5.

Use FREQUENCY Function to Do a Frequency Distribution on Excel

  • After selecting cell G5, type the FREQUENCY Function in the Formula Bar. The FREQUENCY Function in the Formula Bar is,
=FREQUENCY(C5:C17, F5:F8)
  • Where C5:C17 is the data_array and F5:F8 is the bins_array,

  • Hence, simply press Enter on your keyboard. As a result, you will be able to get the output of the FREQUENCY function in column G.

Use FREQUENCY Function to Do a Frequency Distribution on Excel

Read More: How to Create a Grouped Frequency Distribution in Excel


2. Apply COUNTIFS Function to Do a Frequency Distribution

In this method, we will make a frequency distribution by using the COUNTIFS function. This is an easy and time-saving task also. Let’s follow the instructions below to do a frequency distribution!

Step 1:

  • First, select a cell. We will select H5 for the convenience of our work.

Apply COUNTIFS Function to Do a Frequency Distribution on Excel

  • After selecting cell H5, write down the below function.
=COUNTIFS($D$5:$D$17,">="&F5,$D$5:$D$17,"<="&G5)

  • Hence, simply press ENTER on your keyboard. As a result, you will get 3 as the return of the COUNTIFS function.

Apply COUNTIFS Function to Do a Frequency Distribution on Excel

Step 2:

  • Further, autoFill the COUNTIFS function to the rest of cells in Column H.

Read More: How to Make Frequency Distribution Table in Excel


3. Create a Pivot Table to Do a Frequency Distribution

Undoubtedly, creating a pivot table to do a frequency distribution is an easy task. This is time-saving also. Let’s follow the instructions below to do a frequency distribution!

Step 1:

  • First of all, select data range B4 to D17. After that, from your Insert tab, go to,

Insert → PivotTable → From Table/Range

Create a Pivot Table to Do a Frequency Distribution on Excel

  • As a result, a PivotTable from table or range dialog box will appear in front of From the PivotTable from table or range dialog box, firstly, select ‘Pivot Table’!$B$4:$D$17 in the Table/Range selecting box. Secondly, check the Existing Worksheet. At last, press OK.

  • As a result, a pivot table will be created. Then Drag the Quantity field to the Rows and Values region as shown in the below screenshot.

Create a Pivot Table to Do a Frequency Distribution on Excel

Step 2:

  • Now, press right-click on any cell in the column heading with Sum of Quantity. Hence, a will will appear in front of you. From that window, select Value Field Settings.

Create a Pivot Table to Do a Frequency Distribution on Excel

  • Further, a Value Field Settings dialog box pops up. From the Value Field Settings dialog box, firstly, select Count. Secondly, Press OK.

  • After pressing OK, you will be able to count the frequency number which has been given in the below screenshot.

Create a Pivot Table to Do a Frequency Distribution on Excel

  • Alongside, press right-click on any cell in the column heading with Row Labels. Hence, a will will appear in front of you. From that window, select Group.

  • Further, a dialog box named Grouping pops up. From the Grouping dialog box, firstly, type 81 in the Starting at typing box. Secondly, type 120 in the Ending at typing box. Thirdly, type 10 in the By typing box. At last, press OK.

Create a Pivot Table to Do a Frequency Distribution on Excel

  • After completing the above process, you will be able to create a frequency distribution which has been given in the below screenshot.

Create a Pivot Table to Do a Frequency Distribution on Excel

Read More: How to Calculate Relative Frequency Distribution in Excel


Create a Histogram Using Frequency Distribution

In this portion, we will make a Histogram to understand the frequency distribution. Let’s follow the instructions below to create a Histogram!

Steps:

  • First, select data range F5 to G8. After that, from your Insert tab, go to,

Insert → Charts → 2D Column

  • Further, you be able to create a histogram chart to understand the frequency distribution easily which has been given in the below screenshot.

Create a Histogram Using Frequency Distribution

Read More: How to Make a Relative Frequency Histogram in Excel


Things to Remember

👉 Frequency distributions can display the number of observations that fall into each range or the percentage of data that fall into each range.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to calculate frequency distribution will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles


<< Go Back to Frequency Distribution in Excel | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo