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.

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.

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.

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.

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

Step 1: Calculating Mean & Standard Deviation in Excel

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)`

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

Step 2: Finding Data Points of the Normal Distribution Chart

The second step is to find the normal distribution points.

• To do 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)`

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.

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

Step 3: Plotting Normal Distribution Chart in Excel

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.

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

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

• 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.

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

Read More: How to Create a Distribution Chart in Excel

Step 4: Modifying 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.

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

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

• Now, double-click on the Horizontal axis.

• 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.

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

• Next, double-click on the graph line.

• 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.

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

💬 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.

Related Articles

<< Go Back to Excel Distribution ChartÂ |Â Excel ChartsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio