How to Make a Cumulative Distribution Graph in Excel

Most of the time, users usually have raw data and want to find or insert graphs in Excel. Inserting a Cumulative Distribution Graph can be an effective way to showcase the raw data. There are multiple ways to make a cumulative distribution graph in Excel. Making a Frequency Table, applying the NORM.DIST function, and using the Actual Frequency to find the cumulative data (i.e., Frequency or Percentage), then users can use the data to insert any desired graph as the cumulative distribution graph in Excel.

Let’s say we have the following data to make a cumulative distribution graph of. The below data represents the individual heights in meters.

Dataset-How to Make a Cumulative Distribution Graph in Excel

In this article, we demonstrate multiple methods to make a cumulative distribution graph in Excel.


Download Excel Workbook


3 Easy Ways to Make a Cumulative Distribution Graph in Excel

There is no special graph known as the Cumulative Distribution Graph. The cumulative distribution of certain data and inserting a typical graph of them is referred to as the Cumulative Distribution Graph. So, the main theme is how users find the Cumulative Distribution of their data. Here, we will discuss the 3 most common approaches to finding the cumulative distribution and, thereafter, inserting its graph. Follow any of these methods to make a cumulative distribution graph in Excel.


Method 1: Making a Frequency Table to Insert a Cumulative Distribution Graph

Within the existing data, find the maximum (using the MAX function), minimum (using the MIN function) values, and the data Range (interval between the Max and Min values divided by the Bin value). To make a frequency table and then insert a cumulative distribution graph, go through the steps below.

Step 1: Type the below formula into the G7 cell.

=$G$4+F7*$I$4

Cumulative Frequency-How to Make a Cumulative Distribution Graph in Excel

Step 2: Drag the Fill Handle to display the upper limits within the range.

Fill Handle

Step 3: Write the following formula in H7 to display all the frequencies. Press CTRL+SHIFT+ENTER (except Excel or Office 365) to execute the formula.

=FREQUENCY(C:C,G7:G10)

The FREQUENCY function is an array function and takes C:C as its data_array and G7:G10 as its bins_array arguments.

Frequency Formula

Step 4: After executing the formula, find the cumulative frequencies using consecutive addition as done in the below picture.

=I7+H8

Cumulative

Step 5: Prepare a simple data representation consisting of the range and cumulative frequencies. Afterward, go to Insert > Choose any graph type from the Charts section (here Insert Line or Area Chart is chosen).

Graph Insertion

🔺 Furnish the graph according to your taste. Also, you can use the Format Axis side window to adjust the plot area. The final depiction of the cumulative distribution graph may look similar to the picture below.

Line Graph-How to Make a Cumulative Distribution Graph in Excel

Read More: How to Plot Frequency Distribution in Excel (4 Easy Ways)


Method 2: Applying NORM.DIST Function to Find a Cumulative Distribution Plot in Excel

The NORM.DIST function returns the normal distribution, maintaining a fixed Mean and Standard Deviation. Therefore, users need to find the Mean and Standard Deviation of the existing entries. However, by assigning the Cumulative argument to True, the normal distribution becomes the cumulative distribution.

Step 1: First sort the data using the Custom Sort option (smallest to largest). After that, find the Mean using the AVERAGE function in cell D4.

=AVERAGE(C:C)

NORM.DIST function-How to Make a Cumulative Distribution Graph in Excel

Step 2: Calculate the Standard Deviation using the STDEV function in cell E4.

=STDEV(C:C)

Standard Deviation

Step 3: The syntax of the NORM.DIST function is

NORM.DIST(x,mean,standard_dev,cumulative)

Insert the following formula into cell F4, then drag the Fill Handle to display all the percentages.

=NORM.DIST(C4,$D$4,$E$4,TRUE)

Formula

Step 4: Highlight the Height and Cumulative Distribution % column then move to Insert > Chart section > Choose any chart to depict the data into (here Scatter with Smooth Lines chart is chosen).

Graph

🔺 Instantly, Excel displays the cumulative distribution graph. Modify the graph according to your needs, similar to the depiction in the picture below.

Scatter Graph with Smooth Line

Read More: How to Plot Normal Distribution in Excel (With Easy Steps)


Method 3: Using Actual Frequency to Make a Cumulative Distribution Graph

Sometimes users need to find the incurred cumulative percentages as entries go by. In such cases, users usually have hundreds of rows.

Step 1: Use the Custom Sort to organize the data in ascending order, then write the below formula in the D4 cell.

=1/COUNT(C:C)

Actual Frequency-How to Make a Cumulative Distribution Graph in Excel

Step 2: In D5, use the following formula and apply the Fill Handle to populate other cells.

=1/COUNT(C:C)+D4

Fill Handle

Step 3: Go to Insert > Charts section > Select any chart (Scatter with Straight Lines).

Scatter Line Graph

🔺 In a moment, Excel inserts the Cumulative Distribution Chart. Add Chart and Axis titles and use the Format Axis window to best fit the plot area.

Graph


To best fit or adjust the plot area, double click on any axis value and the Format Axis side window appears. Provide your preferred Bounds as depicted in the image below to increase the appeal.

Format Axis-How to Make a Cumulative Distribution Graph in Excel

Read More: How to Create a Distribution Chart in Excel (2 Handy Methods)


Conclusion

This article demonstrates the methods for compiling cumulative distribution data and then making a cumulative distribution graph in Excel. The applicability of these methods depends on the data types. We hope you find the above-described methods useful in your case. Comment if you have further inquiries or have anything to add.

Have a quick visit to our amazing website and check out our recent articles on Excel. Happy Excelling.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo