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

To explain how to plot frequency distributions, we will use a dataset containing information about some students’ Marks.

Method 1 – Plotting a Frequency Distribution in Excel with Histogram Chart

Steps:

• Select all cells of the dataset.

• Go to the Insert tab and select the Insert Static Chart icon. A drop-down menu will appear.
• Select Histogram.

• You will see a chart like the picture below.

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

• You will see the plot of the frequency distribution like the picture below.

Method 2 – Applying Excel Formulas to Plot the Frequency Distribution in Excel

Case 2.1 – Using the FREQUENCY Function

Steps:

• Create a section for the groups. You need to write the Lower Limit and Upper Limit for each group. We grouped the dataset by 10 starting from 31.

• Select Cell G5 and insert the following formula:

• Press Enter to see the result.

• Select the Upper Limits like the picture below.

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

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

• Click on Edit in the Horizontal Axis Labels box. It will open the Axis Labels dialog box.

• Select the range E5:F11.
• Click OK to proceed.

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

• You will see the plot of the frequency distribution.

Case 2.2 – Applying the COUNTIFS Function

Steps:

• Create a section for the Upper Limit, Lower Limit, and Frequency.

• Select Cell G5 and insert the formula given below:

• Press Enter to see the result.

• Drag the Fill Handle down.

• You will find the frequency for each group.

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

Method 3 – Inserting a Pivot Table to Plot the Frequency Distribution in Excel

Steps:

• Select all the cells of the dataset.

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

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

• Right–click on Cell A4 and select Group from the drop-down menu.

• Type the Starting point, Ending point, and Group By value. We wanted to group them by 10.
• Click OK to move forward.

• Click OK.

• Go to the PivotTable Analyze tab and select the PivotChart icon.

• In the Insert Chart window, select Column and click on Clustered Column icon.
• Click OK to see the graph.

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

• The frequency distribution plot will look like the picture below.

Method 4. Plotting the Frequency Distribution with the Excel Data Analysis ToolPak

Let’s set the Lower and Upper limits for each “bin”.

Steps:

• Go to the Data tab and click on the Data Analysis option.

• Select Histogram in the Data Analysis box and click OK.

• In the Histogram window, insert $C$5:$C$15 in the Input Range box.
• Put $F$5:$F$11 in the Bin Range box.
• Check Chart Output and click OK to proceed.

• You will see a histogram on a new worksheet like the below picture.

Read More: How to Create a Distribution Chart in Excel

Download the Practice Book

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