Maruf Islam
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.
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
Step 2: Drag the Fill Handle to display the upper limits within the range.
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.
Step 4: After executing the formula, find the cumulative frequencies using consecutive addition as done in the below picture.
=I7+H8
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).
🔺 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.
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)
Step 2: Calculate the Standard Deviation using the STDEV function in cell E4.
=STDEV(C:C)
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)
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).
🔺 Instantly, Excel displays the cumulative distribution graph. Modify the graph according to your needs, similar to the depiction in the picture below.
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)
Step 2: In D5, use the following formula and apply the Fill Handle to populate other cells.
=1/COUNT(C:C)+D4
Step 3: Go to Insert > Charts section > Select any chart (Scatter with Straight Lines).
🔺 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.
⧠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.
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.
