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

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 make a frequency distribution table in Excel. I hope you will find this article very informative and from there you can gain a lot of knowledge regarding this topic.


Download Practice Workbook

Download the practice workbook


4 Easy Ways to Make Frequency Distribution Table in Excel

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

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.

Make Frequency Distribution Table Using Pivot Table

  • 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.

Make Frequency Distribution Table Using Pivot Table

  • Then, click on the Sales options in the PivotTable Fields.

  • Now, drag the Sales in the Values section.

Make Frequency Distribution Table Using Pivot Table

  • 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.

Make Frequency Distribution Table Using Pivot Table

  • 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.

Make Frequency Distribution Table Using Pivot Table

  • 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.

Make Frequency Distribution Table Using Pivot Table

  • 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.

Make Frequency Distribution Table Using Pivot Table

Read More: How to Make a Categorical Frequency Table in Excel (3 Easy Methods)


2. Utilizing FREQUENCY Function

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.

Make Frequency Distribution Table in Excel

  • 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.

Make Frequency Distribution Table in Excel

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 Easy Ways)


3. Applying COUNTIFS Function

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)

Make Frequency Distribution Table Using COUNTIFS Function

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.

Make Frequency Distribution Table Using COUNTIFS Function

  • 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)

Make Frequency Distribution Table Using COUNTIFS Function

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.

Make Frequency Distribution Table Using COUNTIFS Function

  • 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.

Make Frequency Distribution Table Using COUNTIFS Function

  • Then, do the same for other cells to get the desired frequencies.

Make Frequency Distribution Table Using COUNTIFS Function

Read More: How to Make a Relative Frequency Table in Excel (with Easy Steps)


4. Use of Data Analysis Tool

Another useful method to make a frequency distribution table in Excel is the use of the Data Analysis Tool. This method is really popular to make 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.

Make Frequency Distribution Table Using Data Analysis

  • Now, go to the Data tab in the ribbon.
  • Next, select Data Analysis from the Analysis

Make Frequency Distribution Table Using Data Analysis

  • A Data Analysis dialog box will appear.
  • From the Analysis Tools section, select Histogram.
  • Finally, click on OK.

Make Frequency Distribution Table Using Data Analysis

  • 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.

Make Frequency Distribution Table

  • 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 (3 Examples)


Conclusion

We have shown all the four effective ways to make a frequency distribution table in Excel. The frequency table can easily make in Excel by using the build-in Excel function or pivot table. All of these methods are very effective to make 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, and don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo