Frequency Distribution can be defined with a graph or a set of data that is organized to express the frequency of each possible outcome of a repeatable case performed several times. If you have any concrete dataset, you can make a frequency distribution table in Excel. Excel gives you the platform to make a frequency distribution table using Excel function, pivot table, or any histogram. This article will mainly focus on how to do frequency distribution in Excel. I hope you will find this article very informative and from there you can gain a lot of knowledge regarding this topic.
As the frequency distribution expresses every possible outcome of a set of data, this can be really helpful in our statistical analysis. We have found four different and efficient ways to make a frequency distribution table in Excel including Excel function, and pivot table. All of the methods are really effective in our day-to-day purpose.
1. Using Pivot Table to Make Frequency Distribution Table in Excel
We can use Pivot Table to make a frequency distribution table in Excel. To show this, we take a dataset that includes some salesman’s name, product, and sales amount. We want to find out the frequency between a given amount.
To make a frequency distribution table in Excel, you need to follow the steps carefully.
Steps
- First, we need to select the whole dataset.
- Then, go to the Insert tab in the ribbon.
- From the Tables group, select PivotTable.
- PivotTable from table or range dialog box will appear.
- In the Table/Range section, select the range of cells B4 to D19.
- Next, select the New worksheet to place the PivotTable.
- Finally, click on OK.
- Then, click on the Sales options in the PivotTable Fields.
- Now, drag the Sales in the Values section.
- Now, you need to change the Sum of Sales into the Count of Sales.
- To do this, right-click on any cell of the Sum of Sales column.
- In the Context Menu, select Value Field Settings.
- A Value Field Settings dialog box will appear.
- Then, from Summarize value field By section, Select the Count option.
- Finally, click on OK.
- It will count every sales amount as 1. But when you make a group by using those amounts then the count will change according to that range.
- Next, right-click on any cell of the sales.
- From the Context Menu, select Group.
- A Grouping dialog box will appear.
- It will automatically select the starting and ending by your dataset’s highest and lowest values. You can change it or leave it as such.
- Change the grouping By We take it as 500.
- Finally, click on OK.
- It will create several groups. The Count of Sales also changes with this.
- Next, go to the Insert tab in the ribbon.
- From the Charts group, select Recommended Charts.
- We take the Column charts for this dataset, It will show the frequency distribution within a specified range.
Read More: How to Do a Frequency Distribution on Excel
2. Inserting Excel FREQUENCY Function to Make Distribution Table
To make a frequency distribution table, we can use the FREQUENCY function. The FREQUENCY function denotes how often the numeric value appears in your given range. This function provides the frequency distribution from your dataset.
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.
To apply the FREQUENCY function to make a frequency distribution table, you need to follow the following steps carefully.
Steps
- First, create a lower range and upper range by studying your dataset.
- Next, select the range of cells G5 to G14.
- Then, write down the following formula in the formula box.
=FREQUENCY(C5:C16,F5:F14)
- As this is an array function, we need to press Ctrl+Shift+Enter to apply the formula. Otherwise, it won’t apply the formula. You need to press Enter for a normal function, but for an array function, you need to press Ctrl+Shift+Enter.
Note
Here, we take a higher range as bins because we all know bins mean less than that defined value. So, the function search frequencies less than the higher range,
Read More: How to Create a Grouped Frequency Distribution in Excel
3. Applying COUNTIFS Function to Create Frequency Distribution Table
Next, we can utilize the COUNTIFS function to make frequency distribution in Excel. The COUNTIFS function basically counts the number of cells where your given condition meets. This can easily find the frequency of a certain dataset.
To apply the COUNTIFS function, you need to follow the following rules through which you can make the frequency distribution table in Excel.
Steps
- First, take your dataset and create a lower and upper range by studying it.
- Then, select cell G5.
- Now, write down the following formula in the formula box.
=COUNTIFS(C5:C16,"<="&10)
Breakdown of the Formula
COUNTIFS(C5:C16,”<=”&10)
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.
- Press Enter to apply the formula.
- Next, select cell G6.
- Then, write down the following formula in the formula box.
=COUNTIFS($C$5:$C$16,">"&10,$C$5:$C$16,"<="&20)
Breakdown of the Formula
COUNTIFS($C$5:$C$16,”>”&10,$C$5:$C$16,”<=”&20)
- For more than one condition, we use the COUNTIFS function. First of all, we set the range of cells from C5 to C16. As our range is between 10 and 20, we set our first condition to greater than 10.
- In the next case, we also take the same range of cells. But this time the condition is less than or equal to 20.
- Finally, the COUNTIFS function returns the frequency of the marks between 10 and 20.
- Then, press Enter to apply the formula.
- Next select cell G7.
- Then, write down the following formula in the formula box.
=COUNTIFS($C$5:$C$16,">"&20,$C$5:$C$16,"<="&30)
- Next, press Enter to apply the formula.
- Then, do the same for other cells to get the desired frequencies.
Read More: How to Find Mean of Frequency Distribution in Excel
4. Using Excel Data Analysis Tool to Develop Frequency Table
Another useful method to make a frequency distribution table in Excel is the use of the Data Analysis Tool. This method is really popular for making any frequency distribution table. To use this method properly, you need to follow the following steps carefully.
Steps
- First, you need to enable the Data Analysis Tool.
- To do this, go to the File tab in the ribbon.
- Next, select the More command.
- In the More command, select Options.
- An Excel Options dialog box will appear.
- Then, click on Add-ins.
- After that, click on Go.
- From the Add-ins available section, select Analysis Toolpak.
- Finally, click on OK.
- To use the Data Analysis Tool, you need to have a Bin range.
- We set a bin range by studying our dataset’s lowest and highest values.
- We take the interval 500.
- Now, go to the Data tab in the ribbon.
- Next, select Data Analysis from the Analysis group.
- A Data Analysis dialog box will appear.
- From the Analysis Tools section, select Histogram.
- Finally, click on OK.
- In the Histogram dialog box, select the Input Range.
- Here, we take the Sales column as the Input Range.
- Next, select the Bin Range that we created above.
- Then, set the Output options in the New Worksheet.
- After that, check Cumulative Percentage and Chart Output.
- Finally, Click on OK.
- It will express the frequencies and cumulative percentage.
- When we represent this in the chart, we will get the following result, see the screenshot.
Read More: How to Make a Relative Frequency Histogram in Excel
Download Practice Workbook
Conclusion
We have shown all four effective ways to make a frequency distribution table in Excel. The frequency table can easily be made in Excel by using the built-in Excel function or pivot table. All of these methods are very effective in making a frequency distribution table in Excel. I hope you get all the information regarding the frequency distribution issue in Excel. If you have any questions, feel free to ask in the comment box.
