How Do I Make an Ogive in Excel?

By Ron Price

Learn how to make an ogive curve in a Line or Scatter chart format using the Excel FREQUENCY function.


An ogive (pronounced as "oh-jive") is a graphical representation of a cumulative frequency distribution and can be created using Microsoft Excel. However, the data on which the ogive is based needs to be organized in a certain way so that the required values are available. Typically, the data is placed into a frequency distribution, like the one shown in the following example. If you understand frequency distribution tables and cumulative frequency values, you may skip to Step 3. However, if all of this is new to you, continue on to Step 1.

Step 1

The =FREQUENCY() function tallies the number of data values that fall into specified class ranges. For instance, from the data in the sample, this function produces a tally of the Data values for each of the classes, defined by the upper limits of the Bins column.

Arrange data in adjacent columns that are, from left to right, Data, Bins.

Excel's FREQUENCY() function uses two arguments: the cell range of the data values and the cell range of the class upper limits. Enter your raw data in one column and the upper limit of each class range in another.

Step 2

The raw data highlighted.

On the Excel worksheet, highlight only the cells containing data.

The menu choices to sort the data in ascending mode.

On the Home tab of the Excel Ribbon, click on the Sort & Filter icon and choose Sort Smallest to Largest on its menu to sort the data into ascending order.

Step 3

Highlight the range of cells in which the distribution counts are to be entered.

Select the cells in which you want to place the frequency counts; cells C2 through C8 are used in the example. Click onto the formula bar and enter the =FREQUENCY(data_range,bins_range) function with the cell range for the data replacing data_range and the cell range for the class upper limits as the bins_range. Using the data in the examples, the entry on the formula bar is =FREQUENCY(A2:A31,B2:B8). The data_range is A2:A31 and the bins_range is B2:B8. Do NOT press Enter or click the check mark yet!

Press the Control, Shift, and Enter keys simultaneously (CTRL+SHIFT+ENTER) to apply the function. Braces ({ }) are placed in front and behind of the function statement to indicate that it is an array formula. The frequency distribution counts are placed in a cell associated with each upper limit, as shown in the previous example.

Step 4


The examples given here are relatively simple. To use a function to add the cumulative count down the distribution classes, you can use =SUM() progressively or some version of adding the value of the cell above (0 to the first cell) to the frequency value on that row.

Add the cumulative distribution for each range.

The last step in preparing the data for an ogive curve is to add up the cumulative distribution values. These values are a running total that starts with the first class and ends with the last class. The example shows the cumulative distribution on the example worksheet.

Step 5

Either a Line or Scatter chart can be used to create an ogive chart.

Select the Insert tab on the Excel ribbon and choose the Line chart or Scatter chart option in the Charts section. The two most commonly used chart types are the Marked Line (on the Line chart menu) and the Scatter with Lines and Markers (on the Scatter chart menu).

Select the cumulative frequency values, then choose the chart type for the ogive. You should now have an ogive curve.

An ogive curve showing the cumulative frequency distribution for a data set.