Tanjim Reza
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.
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.
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)
- Next, click on the merged F5 cell and write the formula below. Subsequently, press the Enter button.
=STDEV(D5:D14)
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)
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.
Read More: How to Include Standard Deviation in Excel Radar Chart
Similar Readings
- How to Calculate Mean Variance and Standard Deviation in Excel
- How to Calculate Average and Standard Deviation in Excel
📌 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.
- 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 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.
- 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.
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!
