How to Make a Frequency Polygon in Excel?

Frequency is the number of occurrences of a repeating event per unit of time. A frequency Polygon is a graphical representation of the frequencies i.e., the distribution of data in a dataset. The frequency polygon became very useful in comparing multiple occurrences of distinct categories in a single chart. In a frequency polygon curve, the x-axis represents the dataset values and the y-axis represent the number of occurrence of each distinct category in datasets.

Steps to Make a Frequency Polygon in Excel

In this example, we will create a frequency polygon for random datasets for the number of visitors visiting and the number of days passed since the article was published. For this, we will be using the following random dataset.

Step 1: Determine Classes In Dataset

In this step, we will determine the distinct classes(categories) in our dataset. For this, we are required for the Lower Limit and the Upper Limit for each class and we will arrange them in separate columns. For example, for Class 1-3 Days, the lower limit is 1 and the upper limit is 3.

Step 2: Determine Frequencies

In this step, we will determine the frequency for each distinct class. In order to determine the frequency we will need the mid-point. 

Mid Point = (Lower Limit + Upper Limit) / 2

For example, Mid Point (Class 1-3 Days) = (1 + 3)/2 = 2. Similarly, we will need to do this for all the distinct classes. Once we will get the midpoint for each class we add it to a new column in our dataset.

Note: In order to get enclose curve we need to add 0 in the dataset as default value for 0 days and for days greater than 11.(lower lime and upper limit should show similar distribution as other classes) This will start the graph from origin and end at x-axis to form a close polygon. 

Step 3: Insert Frequency Polygon Chart

In this step, we will insert the frequency poly chart for our dataset. For this Select Mid Point + Num Of Visitors(frequency) Column > Insert > Charts > Insert Scatter > Scatters with Smooth Lines and Markers.

Step 4: Output

Once we click on Scatters with Smooth Lines and Markers excel will automatically insert a frequency polygon chart for our dataset.