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.
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.
- 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.
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.
- 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.
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
- 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.
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.
- 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.
- 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.
- After completing the above process, you will be able to create a frequency distribution which has been given in the below screenshot.
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.
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.
