How to Illustrate Relative Frequency Distribution in Excel

The Relative Frequency Distribution in Excel is a phenomenon where any numerical values symbolize the density of the occurred values within a range. In this article, we demonstrate how to calculate and illustrate Relative Frequency Distribution in Excel.

Let’s say we have five typical products and their sales Quantity in a dataset. And we want to calculate the relative frequencies of the products then illustrate the distribution using a chart.

Dataset-Relative Frequency Distribution Excel


Download Excel Workbook


What is Relative Frequency?

The number of times an entry occurs in a dataset is frequency. For example, from our dataset, we can count the value instances within a range as shown in the following picture.

Range

The above screenshot shows how many values are in those respective ranges in our dataset. In an instant, we can figure out that we have 3 instances between 50 to 59 (range) in our dataset and it goes the same for the others.

Now, relative frequency is a ratio or quotient of the occurrence time of values within a range and the total occurrence. So, the formula turns out to be

                                Relative Frequency= (Frequency within a Range/Total Number of Frequency)

Related frequency

We can illustrate this Relative Frequency Distribution using a Bar or Pivot chart. For this reason, we illustrate relative distribution using the Bar and Pivot Chart in the latter sections.


2 Easy Ways to Illustrate Relative Frequency Distribution in Excel

Method 1: Relative Frequency Distribution Using Bar Chart (Calculated by Formula)

As we have a typical sale dataset with sold Quantity in it, at first, we have to count the occurrences of sold Quantities of the products. Then we calculate the relative frequency using the Division Operator (/).

However, we don’t know the maximum and minimum value of the Quantity amounts. That’s the reason, prior to the frequency or relative frequency calculation we need to find the maximum and minimum values.


🔁 Relative Frequency Calculation

Step 1: Write the following formulas in any adjacent cells (i.e., F5 and G5) to get the Maximum and the Minimum values from the Quantity range.

=MAX(D5:D19)
=MIN(D5:D19)

Formula max value

We use the MAX function to get the Maximum value within the Quantity range.

Formula Min value

Applying the MIN function, we get the Minimum value among the Quantity amounts.

Step 2: Now, Delete the MAX and MIN values as you know them.

Insert two adjacent columns named BIN (Range) and Frequency.

Type 50 to 130 in BIN column sequentially (leaving 10 differences between the Min (i.e., 50) and Max (i.e.,130) values).

BIN range

Step 3: Paste the following formula to count the frequencies in an adjacent cell (i.e., G5).

=FREQUENCY(D5:D19,F5:F12)

The syntax of the FREQUENCY function is

FREQUENCY (data_array, bins_array)

The arguments refer,

data_array; array of values of which you want the frequencies.

bins_array; array of intervals, indicating a range of numbers where the frequencies occur within.

Comparing the syntax and arguments,

D5:D19=data_array

F5:F12=bins_array

In the formula, we enter a short number of cell references for bins_array. That’s because the FREQUENCY function returns more than one item than bins always.

Formula insertion

Step 4: Press ENTER and the formula spills the frequencies in all the rows similar to the picture below.

Formula result

Step 5: Use the below SUM formula to add all the frequencies.

=SUM(G5:G13)

Sum formula-Relative Frequency Distribution Excel

After Hitting ENTER, you’ll get the total frequency number like the following screenshot.

Sum result

Step 6: To calculate the relative frequency just use the Division Operator (/) as done in the formula.

=G5/$G$14

Relative frequency-Relative Frequency Distribution Excel

Step 6: Press ENTER then Drag the Fill Handle to bring out all the relative frequencies of each number range.

Relative frequency result


🔁 Inserting the Bar Chart

Since we calculate the relative frequency, now we can insert a Bar chart to illustrate the Relative Frequency Distribution.

Step 1: Select the relative frequency range (i.e., H5:H12). Then Go to Insert Tab > Select Clustered Column (from the Charts section).

Insert tab-Relative Frequency Distribution Excel

In a moment, the Bar Chart appears. Edit the Bar Chart Title (i.e., Relative Frequency Distribution) as shown in the following image.

Bar Chart


🔁 Editing Bar Chart X-Axis

You see, in the x-axis, the chart doesn’t include the Bin(range) values. To include the range values in the x-axis you have to edit the chart as instructed in the following steps.

Step 1: Right Click on the chart. A Context Menu appears.

In the Context Menu, Select Select Data.

Context menu-Relative Frequency Distribution Excel

Step 2: Select the Data Source window that appears.

In the window, Click on Edit (under horizontal axis label).

All other selections will be made automatically.

Select Data Source window

Step 3: Axis Labels window appears.

In the Axis Labels window, Select the Range (i.e., F5:F13) you want to appear in the horizontal axis.

Click OK.

Axis labels-Relative Frequency Distribution Excel

Executing all these above-discussed steps leads you to an outcome like the picture below. In the picture, we can see the Relative Frequency Distribution illustrated by a Bar Chart.

Illustration by Bar Chart

You can use other forms of charts however for convenience we use the Bar Chart illustration.

Read more: How to Calculate Percent Frequency Distribution in Excel


Similar Readings


Method 2: Relative Frequency Distribution Using Pivot Chart

From the dataset, we know that we don’t have the frequencies for each number range. To get the frequencies, we use Excel’s Pivot Table feature to count the frequencies then use the frequencies and row labels to insert a Pivot Chart.


🔁 Frequency Calculation

Step 1: Select the entire dataset after that Go to the Insert Tab > Click on Pivot Table (in the Tables section).

Pivot table insertion-Relative Frequency Distribution Excel

Step 2: PivotTable from table or range dialog box appears.

In PivotTable from table or range dialog box, Mark the New Worksheet option.

Click OK.

PivotTable from table or range

Step 3: Pivot Table Fields side menu appears.

Checked Quantity in the Pivot Table Fields side menu then Drag the Quantity field to the Rows and Values region as shown in the below screenshot.

Pivot Table fields

 

After executing Step 3, all the frequencies of different number ranges appear in the following image.

Pivot Table range


🔁 Relative Frequency Calculation

In the earlier section of this method, we find the frequencies of different number ranges. Now, we need to calculate the relative frequencies. So, we change the resultant range’s heading Row Labels to Range and Count of Frequencies to Frequency. We only do that for a better presentation.

Step 1: Write the below formula in any adjacent cell (i.e., D3).

=C3/$C$9

Division operator-Relative Frequency Distribution Excel

Step 2: Hit ENTER then Drag the Fill Handle to bring out the relative frequencies for respective number ranges.

Fill handle


🔁 Inserting Pivot Chart

After getting the relative frequencies, it’s possible to make an illustration of that relative frequency distribution using a Pivot Chart. By following the latter sequences, you can insert a Pivot Chart.

We make a separate range using the Range and Relative Frequency column in order to insert the Pivot Chart.

Step 1: Select the entire range (i.e., B11:C17). Afterward, Go to Insert Tab > Click on Pivot Chart (in Pivot Chart section).

Inserting Pivot Table-Relative Frequency Distribution Excel

Step 2: The PivotTable Fields side menu appears.

Checked both Range and Relative Frequency fields as shown in the following screenshot.

Pivot table fields

 You can view a more lucid illustration of the Relative Frequency Distribution in the latter picture.

Pivot Chart-Relative Frequency Distribution Excel


Conclusion

In this article, we discuss Relative Frequency Distribution in Excel and illustrate it using Bar and Pivot Charts. I hope this article clears the concept of relative frequency and its usage to understand a dataset. Comment, if you have further queries or have something to add. See you in my other articles.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo