In this article, we will learn to plot frequency distribution in Excel. A frequency distribution is a representation showing the number of repeated items in a graphical plot or tabular form. There are two types of frequency distribution: Grouped and Ungrouped frequency distribution. Today, we will demonstrate 4 easy methods. Using these methods, you will be able to plot frequency distribution in Excel easily. So, without any delay, letâ€™s start the discussion.

To explain the methods, we will use a dataset containing information about some studentsâ€™ Marks. We will use 4 different ways and show how we can plot frequency distribution. Throughout the whole article, we will use the same dataset.

**Table of Contents**Expand

## 1. Plotting Frequency Distribution in Excel with Histogram Chart

In the newer versions of Excel, you can easily plot the frequency distribution with the histogram chart. The process is straightforward. You donâ€™t need to do any preprocessing in this case. So, letâ€™s follow the steps below to learn more about this method.

**STEPS:**

- First of all, select all cells of the dataset.

- Secondly, go to the
**Insert**tab and select the**Insert Static Chart**icon.Â A drop-down menu will appear. - Select
**Histogram**from there.

- As a result, you will see a chart like the picture below.

- Now,
**right**â€“**click**on the values of the**X**-axis and select**Format Axis**from the**Context Menu**. It will open the**Format AxisÂ**settings.

- In the
**Axis Options**section, set the**Bin Width**to**7**. You choose the**Bin Width**according to your preferences. - The
**Bin Width**indicates the size of a group or interval.

- Finally, you will see the plot of the frequency distribution like the picture below.

## 2. Applying Excel Formulas to Plot Frequency Distribution in Excel

We can use some formulas to plot frequency distribution in Excel. We will use these formulas to preprocess the dataset and then plot them using the **Clustered Column **chart. You can use the FREQUENCY or COUNTIFS function for this purpose. We will show the process in the upcoming sections. To apply this method, we need to create groups. So, we can say that we will use this method for the grouped frequency distribution.

### 2.1 Using FREQUENCY Function

In the first sub-section, we will use **the FREQUENCY function **to create the formula. The FREQUENCY function determines how often a value appears in a range. Letâ€™s follow the steps below to learn more about this method.

**STEPS:**

- In the first place, create a section for the groups. You need to write the
**Lower Limit**and**Upper Limit**for each group. - Here, we grouped the dataset by
**10**starting from**31**.

- Secondly, select
**Cell G5**and type the formula below:

`=FREQUENCY(C5:C15,F5:F10)`

- Press
**Enter**to see the result.

- Thirdly, select the
**Upper Limits**like the picture below.

- Now, go to the
**Insert**tab and select the**Insert Column**icon. A drop-down menu will appear. - Select
**Clustered Column**from there.

- After that,
**right**â€“**click**on the chart and click on**Select Data**from the**Context Menu**.

- In the
**Select Data Source**window, select**Frequency**in the**Legend Entries**box and type**=â€˜FREQUENCY Functionâ€™!$G$4:$G$11**in the**Change data rangeÂ**box. - You need to type the sheet name in place of the FREQUENCY Function.

- After that, click on
**Edit**in the**Horizontal Axis Labels**box. It will open the**Axis Labels**dialog box.

- At this moment, select the
**range E5:F11**. - Click
**OK**to proceed.

- As a result, a chart will appear on the excel sheet.
**Right**â€“**click**on the chart and click on the**Format Data SeriesÂ**option.

- In the
**Format Data Series**section, set the**Gap Width**to**3**%.

- Finally, you will see the plot of the frequency distribution.

### 2.2 Applying COUNTIFS Function

Like the previous method, we can also use **the COUNTIFS function** to determine the frequency. The COUNTIFS function counts the number of cells specified by a set of criteria. This process is similar to the previous one. We will use a different formula this time. Letâ€™s observe the steps below to learn more about the method.

**STEPS:**

- Firstly, create a section for the Upper Limit, Lower Limit, and Frequency.

- Secondly, select
**Cell G5**and type the formula below:

`=COUNTIFS($C$5:$C$15,">="&E5,$C$5:$C$15,"<="&F5)`

- Press
**Enter**to see the result.

In this formula, the COUNTIFS function counts the occurrence of marks in the **range C5:C15 **when itâ€™s greater than **E5 **and less than **F5**.

- After that, drag the Fill HandleÂ down.

- As a result, you will find the frequency for each group.

- Now, repeat the steps of the previous method to get a plot of frequency distribution like the below picture.

## 3. Inserting Pivot Table to Plot Frequency Distribution in Excel

Another way to plot frequency distribution in Excel is to use the **Pivot Table**. We can use the Pivot Table feature for different purposes in Excel. Letâ€™s follow the steps below to learn the whole method.

**STEPS:**

- To begin with, select all the cells of the dataset.

- Secondly, go to the
**Insert**tab and click on the**PivotTable**option. A message box will pop up.

- Click
**OK**to proceed.

- In the
**Pivot Table Fields**settings, select**Marks**and drag it into the**Rows**and**ValuesÂ**box.

- Now,
**right**â€“**click**on**Sum of Marks**and select**Value Field Settings**.

- In the
**Value Field Settings**dialog box, select**Count**and click**OK**to proceed.

- After that,
**right**â€“**click**on**Cell A4**and select**Group**from the drop-down menu.

- Then, type the
**Starting**point,**Ending**point, and**Group ByÂ**value. - Here, we wanted to group them by
**10**. - Click
**OK**to move forward.

- After clicking
**OK**, you will see results like the picture below.

- At this moment, go to the
**PivotTable Analyze**tab and select the**PivotChartÂ**icon.

- In the
**Insert Chart**window, select**Column**and then, click on**Clustered ColumnÂ**icon. - Click
**OK**to see the graph.

- Now,
**right**â€“**click**on the graph and select**Format Data Series**from the menu.

- In the Format Data Series section, set the
**Gap Width**to**3**%.

- Finally, the frequency distribution plot will look like the picture below.

## 4. Plotting Frequency Distribution with Excel Data Analysis ToolPak

In the last method, we will use the Data Analysis ToolPak to plot frequency distribution in Excel. Here, we will use the same dataset. But we need to set the Lower and Upper limits before starting the procedure.

Letâ€™s pay attention to the steps below to see how we can plot the frequency distribution using the **Data Analysis ToolPak**.

**STEPS:**

- Firstly, go to the
**Data**tab and click on the**Data AnalysisÂ**option.

**Note: **If you donâ€™t see the **Data Analysis **option, then you need to load the **Data Analysis ToolPak**. To load the **Data Analysis Toolpak**,

- Click on the
**File**tab and then, select**Options**. - In the
**Excel Options**window, select**Add-ins**. - Now, select
**Excel Add-ins**in the**Manage**box and click on**Go**. - In the
**Add-ins**dialog box, check**Analysis ToolPak**and click**OK**to proceed.

- Secondly, select
**Histogram**in the**Data Analysis**box and click**OK**.

- In the
**Histogram**window, type**$C$5:$C$15**in the**Input RangeÂ**box. - Also, type
**$F$5:$F$11**in the**Bin RangeÂ**box. - After that, check
**Chart Output**and click**OK**to proceed.

- Finally, we will see a histogram on a new worksheet like the below picture.

**Read More:** How to Create a Distribution Chart in Excel

**Download Practice Book**

## Conclusion

In this article, we have discussed 4 easy methods to Plot Frequency Distribution in Excel. I hope this article will help you to perform your tasks easily. Moreover, using Method-1 you can plot ungrouped frequency distribution in Excel. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. You can also visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

## Related Articles

- How to Plot Normal Distribution in Excel
- Plot Normal Distribution in Excel with Mean and Standard Deviation
- How to Create a Probability Distribution Graph in Excel
- How to Plot Poisson Distribution in Excel
- How to Plot Weibull Distribution in Excel
- How to Plot Particle Size Distribution Curve in Excel

**<< Go Back to Excel Distribution ChartÂ |Â Excel ChartsÂ |Â Learn Excel**