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.

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

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`

`)`

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)`

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

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 **Frequenc**y.

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

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

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

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

`=SUM(G5:G13)`

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

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

`=G5/$G$14`

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

**🔁 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).

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

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

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

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

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

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

**Use Formula in an Excel Table Effectively (With 4 Examples)****How to Use Excel Table Reference (10 Examples)****Lookup a Table and Return Values in Excel (3 Simple Ways)****How to Refresh All Pivot Tables in Excel (3 Ways)**

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

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

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

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

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

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

**🔁 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).

**Step 2:** The **PivotTable Fields** side menu appears.

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

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

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