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.

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

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

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

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

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