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.

**Table of Contents**hide

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