Plot Normal Distribution in Excel with Mean and Standard Deviation

A normal distribution graph is a great tool to measure the probability distribution of a given dataset. It might happen frequently that you have a large dataset and you need to find the data distribution. Regarding this, you have landed in the right place for sure! In this article, I will show you all the steps to plot normal distribution in Excel with mean and standard deviation.


Download Practice Workbook

You can download our practice workbook from here for free!


What Is Normal Distribution?

The normal distribution is mainly the probability distribution of data. This graph generally looks like a bell curve. To plot normal distribution, you need to find the mean and standard deviation of the data at the very beginning. Afterward, you will need to find the normal distribution points and thus plot the graph.

Mean: Mean is the average value of all of your data. In Excel, you can find this by using the Average function.

Standard Deviation: It is mainly the measurement of the deviation of your data from the mean value of your data. You can calculate this by the STDEV function.


Introduction to NORM.DIST Function

Objective:

The NORM.DIST function is mainly used to find the normal distribution points for each data of a given dataset.

NORM.DIST Function Syntax

Arguments:

This function has mainly 4 arguments. Such as:

x: This is the data for which you are calculating the normal distribution.

mean: This is the average value of your dataset.

standard_dev: This is the standard deviation of your dataset.

cumulative: This is mainly a TRUE or FALSE value, where the TRUE value represents the cumulative distribution function and the FALSE value represents the probability mass function.


Steps to Plot Normal Distribution in Excel with Mean and Standard Deviation

Say, you have a dataset of 10 students with their IDs, names, and marks for the final exam. Now, you need to plot the normal distribution of the marks of the students with mean and standard deviation. Follow the step-by-step guidelines below to accomplish this.

Students' Marksheet

Here, we have used Microsoft Office 365 to accomplish this. You can use any other Excel version and still achieve your target following these steps. If you face any problems regarding Excel versions, let us know by dropping a comment in the comment section.


πŸ“Œ Step 1: Calculate Mean & Standard Deviation

At first, you need to calculate the mean and standard deviation to plot a normal distribution.

  • To do this, first and foremost select new columns named Mean, Standard Deviation, and Normal Distribution Points. Subsequently, merge the E5:E14 cells and merge the F5:F14 cells.
  • Afterward, click on the merged E5 cell and insert the following formula. Subsequently, press the Enter button.
=AVERAGE(D5:D14)

Calculate Mean to Plot Normal Distribution

  • Next, click on the merged F5 cell and write the formula below. Subsequently, press the Enter button.
=STDEV(D5:D14)

Calculate Standard Deviation to Plot Normal Distribution

Read More: Generate Random Number with Mean and Standard Deviation in Excel


πŸ“Œ Step 2: Find Data Points of Normal Distribution Chart

The second step is to find the normal distribution points.

  • For doing this,Β  click on the G5 cell and write the following formula initially. Afterward, press the Enter button.
=NORM.DIST(D5,$E$5,$F$5,FALSE)

Calculate Normal Distribution Points

Note:

Here the mean argument and standard_dev argument should be absolute. To do this, press the F4 key or put the dollar sign ($) before the row and column heading.

  • Now, place your cursor in the bottom right position of your cell. At this time, the fill handle will appear. Drag it below to copy the same formula.

Drag Fill Handle to Copy Formula

Thus, you will have all the points to plot the normal distribution of this dataset.

All Normal Distribution Points

Read More: How to Include Standard Deviation in Excel Radar Chart


Similar Readings


πŸ“Œ Step 3: Plot Normal Distribution Chart

Now, you have to plot the normal distribution by the extracted points.

  • In order to do this, at the very beginning, you have to sort the Marks column. So, select the cells of this column >> go to the Home tab >> Editing group >> Sort & Filter tool >> Sort Smallest to Largest option.

Sort Marks to Plot Normal Distribution with Mean and Standard Deviation

  • As a result, the Sort Warning window will appear. Select the Continue with the current selection option and click on the OK button.

Sort Warning Window

  • Consequently, you can see the dataset is now sorted by the marks of the students from smallest to largest value.

Sorted Marks to Plot Normal Distribution with Mean and Standard Deviation

  • Afterward, select the Marks column cells and Normal Distribution Points column cells. Subsequently, go to the Insert tab >> Insert Line or Area Chart >> Scatter with smooth lines option.

Insert Scatter Plot to Plot Normal Distribution with Mean and Standard Deviation

And, finally, you can see the normal distribution of the data.

Normal Distribution with Mean and Standard Deviation

Read More: How to Make a t-Distribution Graph in Excel (with Easy Steps)


πŸ“Œ Step 4: Modify the Chart

Now, for a better look, you should modify the chart now.

  • To do this, click on the chart >> Chart Elements icon >> tick the Axis Titles option >> untick the Gridlines option.

Modify Chart Elements to Plot Normal Distribution with Mean and Standard Deviation

  • Afterward, double-click on the Chart Title and both Axis Titles. Subsequently, rename the titles as your desire.

Rename Chart Title and Axis Titles

  • After renaming the titles, the chart will look like this now.

Plot Normal Distribution with Mean and Standard Deviation with Titles

  • Now, double-click on the Horizontal axis.

Access the Horizontal Axis Options

  • As a result, the Format Axis task pane will open in the right side window of Excel. Subsequently, go to the Axis Options group >> make the Minimum Bounds as 50.0.

Fix Minimum Bound of Axis

  • As a result, the graph’s axis will be a little bit changed now. And, it would look like this.

Normal Distribution with Mean and Standard Deviation with New Axis Bounds

  • Next, double-click on the graph line.

Access the Format Data Series Options

  • As a result, the Format Data Series task pane will appear on the right side. Subsequently, go to the Series Options group >> Fill & Line group >> Marker group >> Marker Options group >> choose the Built-in option.

Add Markers to the Normal Distribution Graph

Thus, you will have a beautiful normal distribution Excel with mean and standard deviation. And, the outcome would look like this.

Plot Normal Distribution with Mean and Standard Deviation

Read More: How to Make a Cumulative Distribution Graph in Excel


πŸ’¬ Things to Remember

  • It is better to sort the data before plotting the normal distribution. Else, an irregular curve may occur.
  • The mean and standard deviation of the data must be numeric. Else, it will show the #VALUE error.
  • The standard deviation must be greater than zero. Or else, it will show you the #NUM! error.

Conclusion

To conclude, in this article, I have shown detailed steps to plot normal distribution Excel with mean and standard deviation. I would suggest you go through the full article carefully and practice thoroughly. You can also download our practice workbook from here for free. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo