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

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.

Plot Frequency Distribution in Excel with Histogram Chart

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

Plot Frequency Distribution in Excel with Histogram Chart

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

Plot Frequency Distribution in Excel with Histogram Chart

  • Now, rightclick on the values of the X-axis and select Format Axis from the Context Menu. It will open the Format Axis settings.

Plot Frequency Distribution in Excel with Histogram Chart

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

Plot Frequency Distribution in Excel with Histogram Chart

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

Plot Frequency Distribution in Excel with Histogram Chart


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.

Apply Excel Formulas to Plot Frequency Distribution in Excel

  • Secondly, select Cell G5 and type the formula below:
=FREQUENCY(C5:C15,F5:F10)

Apply Excel Formulas to Plot Frequency Distribution in Excel

  • Press Enter to see the result.

Apply Excel Formulas to Plot Frequency Distribution in Excel

  • Thirdly, select the Upper Limits like the picture below.

Apply Excel Formulas to Plot Frequency Distribution in Excel

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

Apply Excel Formulas to Plot Frequency Distribution in Excel

  • After that, rightclick on the chart and click on Select Data from the Context Menu.

Apply Excel Formulas to Plot Frequency Distribution in Excel

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

Apply Excel Formulas to Plot Frequency Distribution in Excel

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

Apply Excel Formulas to Plot Frequency Distribution in Excel

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

Apply Excel Formulas to Plot Frequency Distribution in Excel

  • As a result, a chart will appear on the excel sheet.
  • Rightclick on the chart and click on the Format Data Series option.

Apply Excel Formulas to Plot Frequency Distribution in Excel

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

Apply Excel Formulas to Plot Frequency Distribution in Excel

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

Apply Excel Formulas to Plot Frequency Distribution in Excel


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.

Apply Excel Formulas to Plot Frequency Distribution in Excel

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

Apply Excel Formulas to Plot Frequency Distribution in Excel

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.

Apply Excel Formulas to Plot Frequency Distribution in Excel

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

Apply Excel Formulas to Plot Frequency Distribution in Excel

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

Apply Excel Formulas to Plot Frequency Distribution in Excel


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.

Insert Pivot Table to Plot Frequency Distribution in Excel

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

Insert Pivot Table to Plot Frequency Distribution in Excel

  • Click OK to proceed.

Insert Pivot Table to Plot Frequency Distribution in Excel

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

Insert Pivot Table to Plot Frequency Distribution in Excel

  • Now, rightclick on Sum of Marks and select Value Field Settings.

Insert Pivot Table to Plot Frequency Distribution in Excel

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

Insert Pivot Table to Plot Frequency Distribution in Excel

  • After that, rightclick on Cell A4 and select Group from the drop-down menu.

Insert Pivot Table to Plot Frequency Distribution in Excel

  • 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, rightclick 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

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo