Creating a Frequency Polygon with the Excel Spreadsheet Program

We can use the Excel spreadsheet program to assist us in creating frequency polygons. We can start with a frequency distribution which is already in a spreadsheet which may look like the following for the data on ages of children in after school program.

Using the Office 98 Version of Excel (in either the Windows or the Macintosh versions), starting with the worksheet containing the frequency distribution for Ages of Children in After School Program, we must modify the frequency distribution somewhat before we can create a frequency polygon of this data.

We must reverse the order of the age and frequency data in the spreadsheet as well as add one age value below the lowest value and one value above the highest value each with a frequency of 0. We can proceed as follows to create this modified frequency distribution:

  1. Make a copy of the Ages of Children frequency distribution by selecting cells A1 through B10, select Copy from the Edit menu, click on cell E1 (the new location of the copy) and select Paste from the Edit menu.
  2. Click on cell H1 in the copy and correct the title to something like Modified Frequency Distribution for Ages.
  3. Select cells E3 through F10, select Cut from the Edit menu, click on cell E4 and select Paste from the Edit menu. This frees up cells E3 and F3. Click on cell E3 and enter 12. Click on cell F4 and enter 0.
  4. Select cells E11 through F11, select Cut, from the Edit menu, click on cell E12 and select Paste from the Edit menu. Cells E11 and F11 are now empty. Click on E11 and enter 4. Click on F11 and enter 0.
  5. At this point your modified frequency distribution for ages should look like this:

  6. In the modified frequency distribution select cells E3 through F11. Select Sort from the Data menu, select Ascending under Sort by if it is not already selected, and click OK.

    The modified frequency distribution should look like this:

    We will use this modified frequency distribution to prepare a frequency polygon.

Using the Modified Frequency Distribution for Ages we can create a frequency polygon with Excel as follows:

  1. In the Modified Frequency Distribution for Ages spreadsheet, select cells E2 through F11.
  2. Click on the Chart Wizard icon.
  3. In the Chart Wizard - Step 1 of 4 - Chart Type window select Line Chart type and select Line Chart sub-type. The description for this chart sub-type says "Line. Displays trend over time or categories." Click Next.
  4. In the Chart Wizard - Step 2 of 4 - Chart Source Data window, click on the Data Range tab. Select Columns in Series in: if it is not already selected.
  5. Click on the Series tab. Select Age in the Series box and click the Remove button. Click on the small icon to the right of the Category (X) axis labels: box.
  6. With the Source Data - Category (X) axis labels: window showing, select the range labels for the Age variable in the modified frequency distribution, that is select cells E3 through E11. The box in the Source Data window will now show something like =Sheet1!$E$3:$E$11. Click on the icon to the right of the box and you will return to the Source Data window.
  7. Click Next.
  8. In the Chart Wizard - Step 3 of 4 - Chart Options window, click on the Titles tab if it is not already selected. Click on the Chart title: box and enter the name of the chart - Ages for Children in After School Program.
  9. Press the tab key to move to the Category (X) axis box and enter the title of the X-axis - Age (Years).
  10. Press the tab key to move to the Value (Y) Axis: box and enter the title of the Y-axis - Frequency.
  11. Click on the Legend tab and then click in the Show legend box to deselect it. Click Next.
  12. In the Chart Wizard - Step 4 of 4 - Chart Location window, click the As object in: button and click Finish.
  13. Move the chart to the desired place on the worksheet page by clicking on it and dragging it. You may also change the size of your chart by clicking and dragging on one of the darkened boxes at the corners, sides, or top and bottom of the chart.
  14. Save As your Worksheet (under a new name) so that you save your new chart to your computer disk.

The completed worksheet page, using the data for Ages for Students in After School Program should look something like the following:

Return to Ed 602 Lesson 4