Relative Frequency Distribution – Excel and Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

Dennis Madrid

Last updated on April 27, 2023

This tutorial will demonstrate how to calculate the relative frequency distribution in Excel and Google Sheets.

Relative Frequency Distribution in Excel

A Frequency Distribution is a distribution of the number of occurrences of a set of events. In other words, a frequency distribution shows different values in a dataset and the number of times the values occur in the dataset.A Relative Frequency Distribution shows the occurrence of an event as a proportion of the total number of occurrences of all events in a given set of events. In other words, a relative frequency distribution shows the number of times a value appears in a dataset as a proportion of the size of the dataset.

The Relative Frequency of an event can be seen as the probability of the event occurring in a random draw.


Relative Frequency Distribution Formula

The relative frequency distribution is calculated using the formula:

Relative Frequency Distribution Formula

where fi is the relative frequency of each event, value, or class;

ni is the number of occurrence (frequency) of each event, value, or class; and

N is the total number of occurrences of all events (total frequency).


Properties of a Relative Frequency Distribution

  • Relative frequencies can be expressed as a decimal number of a percentage.
  • When expressed as a decimal number, each relative frequency takes up a value between 0 and 1, and the sum of all relative frequencies must be 1.
  • When expressed as a percentage, each relative frequency takes up a value between 0% and 100%, and the sum of all relative frequencies must be 100%.

How to Calculate Relative Frequency Distribution in Excel

Background: An Internet Service Provider is conducting a customer satisfaction survey from a random sample of its users. They asked each surveyed customer to rate their services in the past 90 days on a scale of 1 to 10, where 10 represents excellent, and 1 represents miserable. The results from the survey are collated and presented in the table below. Calculate the relative frequency distribution of the result of the survey.

fi Sample Table1 in Excel

First, calculate the total frequency.

fi Total in Excel

Then, calculate the Relative Frequency by dividing each Frequency by the Total Frequency.

Calculate the Relative Frequency in Excel

Note: Double-Click the bottom right corner of the cell to fill down the data to the rest of the column.

The complete Relative Frequency column is shown below.

Complete Relative Frequency Column in Excel

Note: The sum of all relative frequencies must be 1 .


You can express the Relative Frequencies as percentages by selecting the Relative Frequency column and clicking the ‘Percent Style’ button.

Relative Frequencies as Percentages in Excel

To visualize the relative frequency distribution, you can create a frequency distribution histogram or bar chart, depending on the type of data you are working with.

To do this, select the Clustered Column chart in the Insert Column or Bar Chart chart type on the Charts group of the Insert tab.

Visualize the Relative Frequency Distribution in Excel


The resulting bar chart is shown below.

fi Resulting Bar Chart in Excel

Edit the chart title by clicking on the Chart Title in the chart and typing your desired title.

fi Resulting Bar Chart Edit Title in Excel

Add the horizontal and the vertical axis titles by selecting the chart, then click on the Chart Design tab, then on the Add Chart Element button, then on Axis Title, and then on Primary Horizontal for the horizontal axis title or on Primary Vertical for the vertical axis title.

fi Vertical Axis Titles in Excel


Then, edit the titles as required.

fi Edit Titles Final in Excel


Relative Frequency Distribution in Google Sheets

The Relative Frequency Distribution can be calculated in Google Sheets in a similar way as it is calculated in Excel as shown in the picture below.

First, follow the method described in the Excel section to obtain the Relative Frequency Distribution table.

Relative Frequency Distribution Table in Google Sheets

To create the relative frequency distribution bar chart in Google Sheet, first, highlight the Satisfaction Score and the Relative Frequency column, and then click the Chart option from the Insert tab.

Relative Frequency Distribution Bar Chart in Google Sheets

The resulting chart is shown below.

Relative Frequency Distribution Result in Google Sheets

If the resulting chart is not a bar chart, change the Chart type to Column Chart in the Setup section of the Chart Editor. You can also make other settings as required.

Changing the Chart Type to Column in Google Sheets

AI Formula Generator

Try for Free