Frequency Distribution in Excel (4 Suitable Methods)

Frequency distribution in Excel refers to a tabular representation that shows the number of occurrences of each value or range of values in a dataset.

In this free Excel tutorial, we will learn how to perform various tasks associated with frequency distribution using different methods and formulas.

In the following overview image, there is a list of 10 patients with their corresponding heights in cm. We have created a frequency distribution table by grouping the heights into 4 suitable classes, starting with 151 cm and ending with 190 cm, with a 10 cm class interval. We have also created a histogram based on the frequency distribution. We have used the PivotTable feature to get the outputs.

In this blog post, we will learn how to do frequency distribution and plot necessary diagrams using Excel’s various features and formulas. We will create a frequency distribution table using PivotTable; the FREQUENCY function; the COUNTIFS function; and Data Analysis Tookpak. We will also draw frequency distribution histograms by inserting column charts or using the Data Analysis feature. Finally, we will show you how to accomplish some closely related tasks to frequency distribution, such as calculating cumulative relative frequency, cumulative frequency percentage, or plotting an Ogive curve.

⏷What Is Frequency Distribution?
⏷Using Pivot Table to Create Frequency Distribution Table
⏷Inserting FREQUENCY Function to Do Frequency Distribution
⏷Using COUNTIFS Function to Do Frequency Distribution
⏷Using Data Analysis Toolpak to Do Frequency Distribution
⏷Calculate Cumulative Relative Frequency in Excel
⏷Calculate Cumulative Frequency Percentage in Excel
⏷Draw an Ogive Graph in Excel

What Is Frequency Distribution?

A frequency distribution is a summary of the number of occurrences of different values or ranges of values in a dataset. It organizes data into groups and shows how many times each group occurs, providing a way to understand the distribution and pattern of data values. Frequency distribution is commonly used to analyze and summarize data in areas such as market research, finance, quality control, etc. For instance, in finance, it can help analyze the distribution of stock prices or investment returns. In marketing, it aids in understanding customer demographics or purchase patterns. For quality control, it is employed to assess variations in product specifications.

1. Using Pivot Table to Create Frequency Distribution Table in Excel

The first section is about making a frequency distribution table in Excel using the pivot table feature and plotting a histogram based on that distribution. The following dataset holds the values for the measured heights of a group of patients in a doctor’s chamber.

To create a frequency distribution table and plot a histogram, follow the steps below.

Steps:

• Select the range B6:C16 => Go to Insert tab => Select PivotTable inside Tables group.

A dialogue box named PivotTable from Table or Range will pop up.

• Inside the dialogue box, check the Existing Worksheet box to keep the pivot table inside the same worksheet as the data table.
• Fix the location of the pivot table in your desired cell. Here, we have selected cell E6.
• Click on OK.

A pivot table is now inserted.

• Inside the PivotTable Fields, place the Height (cm) parameter inside both the Rows and Values fields.

• Now right-click on any of the cells inside the Sum of Height (cm) column and select Value Field Settings… from the context menu.

A dialogue box named Value Field Settings will pop up.

• Inside the dialogue box, select Count from the list.
• Click on OK.

• After that, right-click on any cells inside the Row Labels column and select Group… from the context menu.

A dialogue box named Grouping will pop up.

• Set the Starting at:, Ending at:, and By: values suitably for your data range. We have set 151, 190, and 10, respectively, suitable for this dataset.
• Click on OK.

Our frequency distribution table is now ready. We grouped the heights of the patients into 4 suitable classes, starting with 151 cm and ending with 190 cm, with a 10 cm class interval, and then determined the number of heights in each of the four groups.

Additionally, if you want to plot a histogram based on that frequency distribution, follow the steps below:

• Select the range E6:F10 and click on Insert tab => Select Insert Column or Bar Chart drop-down inside the Charts group => Click Clustered Column.

A column chart that represents the desired histogram will be inserted. Finally, you could change some chart styles, such as chart titles, axis titles, fill color, etc., to make your histogram easier to understand and visually appealing.

2. Inserting FREQUENCY Function to Do Frequency Distribution in Excel

In this part, we will do frequency distribution based on the same dataset using the FREQUENCY function instead of PivotTable.

Follow the steps below to complete the task.

Steps:

• First, calculate the minimum and maximum values of the dataset in cells C18 and C19, respectively, using the following formulas:

• Create two columns in a separate table based on those values and manually input the upper and lower limits of the class distributions (see the image below). Our class interval is 10.
• Then, inside cell G8, write the following formula:

• Press ENTER. As this is an array formula, the rest of the cells will also get populated automatically.

3. Using COUNTIFS Function to Do Frequency Distribution in Excel

Here, we will do the same frequency distribution based on the previous dataset using the COUNTIFS function. Also, we will use the combination of the MID and FIND functions to calculate the upper and lower limits for each of the class distributions.

To do that, follow the steps below.

Steps:

• Inside cell F7, type the following formula:

• Press ENTER and use the Fill Handle to copy the formula down to the rest of the cells. It’ll return the lower limits.

• Again, inside cell G7, put the following formula:

• Press ENTER and use the Fill Handle as before. It’ll return the upper limits.

The lower and upper limits are extracted from the class distributions. Now, we are ready to do the frequency distribution.

• Write the following formula inside cell H7:

• Press ENTER and use the Fill Handle to copy the formula down.

4. Using Data Analysis Toolpak to Plot Frequency Distribution Histogram in Excel

In this part, we will create a frequency distribution table and plot a histogram based on the same dataset using the Data Analysis feature of Excel. We only need the upper limits (bin) of the distribution classes to do the operation. Follow the steps below to do so.

Steps:

• Write the following formula inside cell F7:

• Press ENTER and use the Fill Handle to copy the formula down.

The upper values, or bins, are extracted from the class distribution. The reason behind using the NUMBERVALUE function is to format the bin range as a Number. It is mandatory to use this range inside the Data Analysis tool.

Now, we are ready to create the frequency distribution table and histogram.

• Click on the Data tab => Data Analysis under the Analysis group.
• The Data Analysis dialogue box will appear. Inside the box, select Histogram => OK.

A dialogue box named Histogram will open.

• Set the Input Range as $C$7:$C$16.
• Set the Bin Range as $F$7:$F$10.
• Check the Output Range and set the output range to $E$12.
• Check the Chart Output checkbox and click OK.

The frequency distribution table, along with the histogram, will appear.

How to Calculate Cumulative Relative Frequency in Excel

In this part, we will calculate the cumulative relative frequency for the same dataset. Cumulative relative frequency is the sum of the relative frequencies of all values in a dataset up to a certain point. It represents the proportion or percentage of data points that are less than or equal to a specific value. This cumulative approach helps analyze the distribution of data across different ranges and provides insights into the overall pattern of the dataset.

To calculate the cumulative relative frequency, follow the steps below.

Steps:

• Calculate the total frequency using the following formula in cell C23:

• Then, inside cell D19, put the following formula:

• Press ENTER and use the Fill Handle to copy the formula down.

The relative frequencies are calculated.

• Now, inside cell E19, write the following formula:

• Press ENTER.

• Then, go to cell E20 and write the following formula:

• Press ENTER and use Fill Handle as before.

Cumulative relative frequencies are calculated in this way.

How to Calculate Cumulative Frequency Percentage in Excel

In this section, we will show how to calculate the cumulative frequency percentage in Excel. Cumulative frequency percentage is almost similar to cumulative relative frequency but is expressed as a percentage rather than a proportion. Both measures involve the summation of the frequencies up to a specific point in a dataset, representing the cumulative distribution of values.

Follow the steps below to accomplish the task:

Steps:

• First, calculate the total frequency as before in cell C23.
• Inside cell D19, write the following formula:

• Press ENTER.

• Again, put the following formula inside cell D20:

• Press ENTER and use the Fill Handle to copy the formula down.

• Finally, write the following formula inside cell E19:

• Press ENTER and use the Fill Handle as before.

Remember to keep the formatting of the range E19:E22 in percentage. You can learn how to convert number to percentage in Excel by going through the linked article.

How to Draw an Ogive Graph in Excel

In this last section, we will show how to make an Ogive graph in Excel. An Ogive curve, also known as a cumulative frequency curve, is a graphical representation of the cumulative frequency distribution of a dataset. It is constructed by plotting points that correspond to the cumulative frequencies at upper limit values of the class distributions.

Follow the steps below to accomplish the task.

Steps:

• Write the following formula inside cell C22:

• Press ENTER and use the Fill Handle to copy the formula down.

The upper limits of the class distributions are extracted, and we will use these as X-axis values while plotting the Ogive curve.

• We’ll calculate the frequencies now. Inside cell D22, put the following formula:

• Press ENTER. As this is an array formula, the rest of the cells will also get populated automatically.

• To calculate cumulative frequencies, write the following formula inside cell E22:

• Press ENTER.

• Again, inside cell E23, type the following formula:

• Press ENTER and use the Fill Handle as before.

The cumulative frequencies are calculated, and we will use these as Y-axis values while plotting the Ogive curve.

• Select the range C21:C25. Then, by pressing CTRL, simultaneously select the range E21:E25.
• Click on the Insert tab => Insert Scatter (X, Y) or Bubble Chart => Scatter with Straight Lines and Markers.

A scatter plot named Cumulative Frequency is inserted.

Finally, you could change some chart styles, such as chart titles, axis titles, fill colors, etc., to make your Ogive curve easier to understand and visually appealing.

Download Practice Book

This article has shown how to do frequency distribution in Excel. The four methods have discussed the techniques of performing frequency distribution using different features and formulas in Excel. We showed you how to calculate different parameters related to cumulative frequency, as it is a topic very closely related to frequency distribution. Leave a comment for any further queries.

Frequency Distribution in Excel: Knowledge Hub

• How to Calculate Percent Frequency Distribution in Excel
• How to Do Cross Tabulation in Excel
• How to Make a Contingency Table in Excel
• How to Make an Ogive Graph in Excel
• How to Make a Relative Frequency Table in Excel
• How to Make a Categorical Frequency Table in Excel
• How to Calculate Cumulative Frequency Percentage in Excel
• How to Calculate Cumulative Relative Frequency in Excel
• How to Calculate Relative Frequency Distribution in Excel
• How to Create a Grouped Frequency Distribution in Excel
• How to Do a Frequency Distribution on Excel
• How to Make a Relative Frequency Histogram in Excel
• How to Find Mean of Frequency Distribution in Excel
• How to Calculate Upper and Lower Limits in Excel
• How to Make Frequency Distribution Table in Excel

<< Go Back to Excel for Statistics | Learn Excel