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.

## Download Practice Book

You can download the practice book from here.

## 4 Easy Ways to Plot Frequency Distribution in Excel

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.

### 1. Plot 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. Apply 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 Use 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 Apply 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 know 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. Insert 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. Plot 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.

## 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 (With Easy Steps)****Make a t-Distribution Graph in Excel (with Easy Steps)****How to Create a Probability Distribution Graph in Excel (With 2 Examples)****Plot Normal Distribution in Excel with Mean and Standard Deviation****How to Make a Cumulative Distribution Graph in Excel**