How To Make Frequency Polygon In Excel

6 min read

A frequency polygon is a powerful statistical tool used to visualize the distribution of a dataset, offering a clear view of the shape, central tendency, and spread of data points. Unlike a standard histogram, which uses bars, a frequency polygon connects the midpoints of the top of each bar with straight lines, creating a continuous line graph that makes it significantly easier to compare multiple distributions on the same chart. Learning how to make a frequency polygon in Excel is an essential skill for students, data analysts, and business professionals who need to present quantitative data effectively. While Excel does not have a dedicated "Frequency Polygon" button in the chart menu, the process is straightforward once you understand the data preparation steps involving class midpoints and frequency counts That's the part that actually makes a difference..

Understanding the Core Concepts

Before diving into the technical steps, it is crucial to grasp the underlying components. A frequency polygon relies on two primary data columns: the class midpoint (often called the class mark) and the frequency.

The class midpoint is the average of the lower and upper class limits for each bin. Still, to close the polygon shape properly—anchoring the line to the horizontal axis—you must add an extra class interval at the beginning and end of your data range with a frequency of zero. The frequency is simply the count of data points falling within that interval. To give you an idea, if a class interval is 10–20, the midpoint is 15. This ensures the polygon starts and ends at the baseline, providing a complete visual enclosure of the area under the curve.

You'll probably want to bookmark this section.

Preparing Your Source Data

The foundation of any accurate chart in Excel is clean, organized source data. Assume you have a raw dataset of exam scores ranging from 0 to 100. You cannot plot a polygon directly from raw scores; you must first summarize them into a grouped frequency distribution table.

  1. Determine Class Intervals: Decide on the number of classes (usually between 5 and 20) and the class width. For scores 0–100, intervals of 10 (0–10, 10–20, etc.) work well.
  2. Calculate Frequencies: Use the FREQUENCY function or a PivotTable to count how many scores fall into each interval.
  3. Calculate Midpoints: In a new column, calculate the midpoint for each class. The formula is (Lower Limit + Upper Limit) / 2. For the 0–10 class, the midpoint is 5.
  4. Add "Zero" Rows: This is the most critical step for a true polygon. Insert a row above your first class and a row below your last class.
    • For the top row: Use a midpoint that is one class width below your first midpoint (e.g., -5) and set Frequency to 0.
    • For the bottom row: Use a midpoint one class width above your last midpoint (e.g., 105) and set Frequency to 0.

Your final table should look similar to this structure:

Class Interval Midpoint (X-Axis) Frequency (Y-Axis)
Extra Start -5 0
0 – 10 5 3
10 – 20 15 8
20 – 30 25 15
30 – 40 35 22
40 – 50 45 18
50 – 60 55 12
60 – 70 65 7
70 – 80 75 4
80 – 90 85 2
90 – 100 95 1
Extra End 105 0

Step-by-Step Guide to Creating the Chart

Once your data table is structured correctly with the zero-frequency anchors, creating the chart takes only a few clicks.

1. Select the Data

Highlight only the two columns containing the Midpoints and Frequencies (including the extra zero rows). Do not select the "Class Interval" column, as that contains text labels which will confuse the X-axis scaling. In the example above, you would select the range containing values -5 through 105 and their corresponding frequencies 0 through 0.

2. Insert a Scatter Chart

This is the secret to a perfect frequency polygon in Excel. Do not use the "Line Chart" or "Column Chart" options.

  • Go to the Insert tab on the Ribbon.
  • In the Charts group, click the Insert Scatter (X, Y) or Bubble Chart icon (it looks like a scatter plot with dots).
  • Select Scatter with Straight Lines and Markers (the second option in the first row usually).

Why Scatter? A Scatter chart treats the horizontal axis as a value axis (numerical scale), plotting points at exact numerical distances. A Line chart treats the horizontal axis as a category axis, spacing points evenly regardless of the numerical gap between midpoints. Since class midpoints are numerical values (5, 15, 25...), a Scatter chart is the only mathematically correct choice.

3. Initial Chart Cleanup

Excel will generate a chart with markers connected by straight lines.

  • Remove the Legend: Click the legend (usually on the right or bottom) and press Delete. A single series rarely needs a legend.
  • Add Chart Title: Click the default "Chart Title" box and type a descriptive title, such as "Frequency Polygon of Student Exam Scores."
  • Add Axis Titles: Click the green + (Chart Elements) button next to the chart. Check Axis Titles. Label the horizontal axis "Score (Midpoint)" and the vertical axis "Frequency."

Refining the Visual Appearance

A default Excel chart often looks cluttered. Professional presentation requires formatting adjustments.

Formatting the Axes

The horizontal axis often starts at 0 by default, leaving empty space on the left if your data starts at -5 or 5.

  1. Right-click the Horizontal Axis and select Format Axis.
  2. In the pane, under Axis Options, set Minimum to your starting midpoint (e.g., -5 or 0) and Maximum to your ending midpoint (e.g., 105 or 100).
  3. Set Major Units to your class width (e.g., 10) so gridlines align perfectly with your midpoints.
  4. Optionally, change the Number Format to "Number" with 0 decimal places to avoid ".0" on integers.

Styling the Line and Markers

  1. Click the line/series in the chart to select it.
  2. Press Ctrl+1 (or right-click > Format Data Series).
  3. Line Options: Choose a Solid line. Increase Width to 2.0 or 2.5 pt for visibility. Pick a strong color (dark blue, green, or red).
  4. Marker Options: Select Built-in. Choose a Circular marker type. Set Size to 8–10 pt.
  5. Marker Fill: Select Solid Fill (match the line color).
  6. Marker Border: Select Solid Line, color White, Width 1.5 pt. This creates a nice "pop" effect where the marker sits on top of the line.

Gridlines and Background

  • Keep Major Horizontal Gridlines
Just Made It Online

What's New Today

Kept Reading These

You Might Want to Read

Thank you for reading about How To Make Frequency Polygon In Excel. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home