How Do I Make an Ogive in Excel?

Learning to make an ogive in Excel gives you a way to display cumulative frequencies using Microsoft Office's spreadsheet software, which is useful if you have to display and analyze data regularly. The process of putting together the ogive chart is straightforward, only requiring a few simple calculations prior to plotting the graph. Thanks to its built-in calculation and graphing functions, Excel works well as an ogive graph maker.

An ogive chart is a display of cumulative relative frequency on the y-axis throughout several groups arranged on the x-axis, displayed as a line graph. "Cumulative relative frequency" is the key phrase. The "frequency" of something is how often it is observed, so if you have a class of 20 students, and five of them wear size 8 shoes, then the frequency of size 8 feet in the group is five. Turning this into a relative frequency would mean scaling it to the size of the overall group, so five out of the 20 students account for a quarter (or 25 percent) of the whole group.

The "cumulative" part means that the frequency shown in each group includes the frequency of that group added to that of all the groups that came before. So if five students have size 8 feet, but two have size 7 feet, two have size 6 feet, and one has size 5 feet, then the "size 8" section displays 10, or 50 percent, and shows that half of all students have size eight feet or smaller.

In short, an ogive chart is a line graph that increases from one group to the next according to how much each group adds to the total relative frequency. By the last group, the cumulative relative frequency will be one (in decimal form) or 100 percent.

Making an ogive chart in Excel is really making a cumulative frequency graph in Excel, so you need to set up the data accordingly. Imagine you're working with the shoe size data described in the previous section:

Size 5: one

Size 6: two

Size 7: two

Size 8: five

Size 9: six

Size 10: three

Size 11: one

The sizes should occupy one column in Excel, and then the number of students in each group (the frequency) should be in the next column. Create a new column entitled "Cumulative Frequency" and add the values from the previous column to make your running total. The easiest way to do this is to use the "Sum" function in Excel. If your data runs from cell B2 to cell B8, type "=SUM($B$2:B2)" in cell C2 and then drag the formula down to cell C8 by clicking and dragging the bottom right corner of cell C2 downward. The formula should update, so it sums from cell B2 to the cell directly beside the cell containing the formula. If you do this correctly, the last cell is the total number of observations. In the shoe size example, this would be 20.

Add a column for cumulative relative frequencies and calculate them. Do this by dividing each of the cumulative frequencies by the total in the last row. In the example, you enter "=C2/20" in the cell and drag this formula down. If you want percentages, enter "=(C2/20)*100" instead.

You can now use your data to produce an ogive in Excel. Highlight the data and go to the "Insert" tab. Click the drop-down menu below "Line" and choose "Line with Markers." Highlighting the data isn't easy when the columns aren't next to each other, but you can highlight it all and remove the extra series that show up based on the frequency and cumulative frequency columns. This is the ogive chart. You can edit the graph or the category labels to suit your preferences or needs from this point onward.