Mursalin Ibne Salehin
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
