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:

`=MIN(C7:C16)`

`=MAX(C7:C16)`

- 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:

`=FREQUENCY(C7:C16,F8:F10)`

- 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:

`=MID(E7,1,FIND("-",E7,1)-1)`

- 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:

`=MID(E7,FIND("-",E7,1)+1,3)`

- 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**:

`=COUNTIFS($C$7:$C$16,">="&F7,$C$7:$C$16,"<="&G7)`

- 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**:

`=NUMBERVALUE(MID(E7,FIND("-",E7,1)+1,3))`

- 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**:

`=SUM(C19:C22)`

- Then, inside cell
**D19**, put the following formula:

`=C19/$C$23`

- 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:

`=D19`

- Press
**ENTER**.

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

`=E19+D20`

- 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:

`=C19`

- Press
**ENTER**.

- Again, put the following formula inside cell
**D20**:

`=D19+C20`

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

- Finally, write the following formula inside cell
**E19**:

`=D19/$C$23`

- 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**:

`=NUMBERVALUE(MID(B22,FIND("-",B22,1)+1,3))`

- 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:

`=FREQUENCY(C7:C16,C22:C24)`

- 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**:

`=D22`

- Press
**ENTER**.

- Again, inside cell
**E23**, type the following formula:

`=E22+D23`

- 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**