A frequency polygon graph in Excel is a useful way to represent the frequency of different observations. For example, if you own a business and count the number of customers you have each day for 30 days, you end up with a series of numbers that can be hard to interpret at first. However, if you separate the observations into groups, such as 0 to 9 customers, 10 to 19 customers, 20 to 29 customers and so on, with a count for how many observations fall into each group such as 15 days had between 30 and 39 customers, but only one had between 0 to 9, you can make a polygon chart to display the results in an intuitive fashion.
Preparing the Data
Before you can create a frequency polygon in Excel, the first step is putting your data in the right format. Ideally, you have a column containing the different groups ("0 to 9 customers," "10 to 19 customers," "20 to 29 customers" and so on in the number of customers example) and the frequencies of observations in the next column. If you want to calculate a cumulative frequency, you can create another column to the right where you do this. The "Sum" function works well for this.
Video of the Day
If the frequency data is in cells B2 to B31, add the cumulative frequencies in column C, writing "=SUM($B$2: B2)" in cell C2, and then drag this formula down to cell C31. Drag the formula by hovering your mouse pointer over the lower right corner of the cell, so the pointer turns into a black cross with no arrowheads. Then, left-click and drag it down. The "B2" part of the formula updates (to "B3," "B4," "B5" and so on) but the first B2 (with dollar signs) stays in place. You can turn these into percentages by dividing each result for cumulative frequency by the sum of all of the frequencies and multiplying by 100. This can be added using another column.
Depending on the data that you have and how you want to present your graph, it might help to use a midpoint for the groupings rather than the whole range. For example, if your groups are "1 to 5," "6 to 10," "11 to 15" and so on, you could create a midpoint column to the right of this reading "3," "8," "13" and so on. You can plot the polygon graph in any case, but there might be a benefit of one approach over another for your specific situation.
Plotting a Frequency Polygon in Excel
Use Excel as a frequency polygon maker after setting up the data. Highlight the two columns of data you want to use (for example, either your group ranges/titles or their midpoints and the corresponding frequencies) and go to the "Insert" tab. Find "Line" or the jagged line icon representing a line graph in the "Charts" group and click on the drop-down arrow beneath it. Choose "Line with Markers" to plot the graph. You can highlight non-neighboring columns by highlighting the first in the usual way and then holding "Ctrl" while clicking and dragging down the second column.
Your polygon graph will be displayed on the screen. You can now edit the style, layout or other aspects of the graph.
Plotting Cumulative Frequency Polygons
The process for plotting a cumulative frequency polygon or a cumulative percentage polygon in Excel is much the same as plotting an ordinary one. Highlight the groups column and either the cumulative frequencies or the percentage versions of the cumulative frequencies. As in the previous section, go to "Insert," "Line" and "Line with Markers" to plot the graph.