How to Plot Frequency Distribution in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF