Plot the Normal Distribution in Excel with Mean and Standard Deviation

What Is the Normal Distribution?

The normal distribution is the probability of data distribution. The graph generally looks like a bell curve. To plot the normal distribution, you need to find the mean and standard deviation.

Mean: Mean is the average value of all data. Use the AVERAGE function.

Standard Deviation: It is the measurement of the deviation of your data from the mean value. Use the STDEV function.


Introduction to NORM.DIST Function

Objective:

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

NORM.DIST Function Syntax

Arguments:

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

mean:  the average value of your dataset.

standard_dev:  the standard deviation of your dataset.

cumulative:TRUE or FALSE value.  TRUE represents the cumulative distribution function and FALSE represents the probability mass function.


The dataset showcases 10 students’ IDs, names, and marks in the final exam.

Plot the normal distribution of the marks with mean and standard deviation:

Students' Marksheet

 


Step 1- Calculating the Mean and the Standard Deviation in Excel

  • Select new columns named Mean, Standard Deviation, and Normal Distribution Points.
  • Merge E5:E14.
  • Merge F5:F14.
  • Click E5 and enter the following formula.
  • Press Enter.
=AVERAGE(D5:D14)

Calculate Mean to Plot Normal Distribution

  • Click F5 cell and enter the formula below.
  • Press Enter.
=STDEV(D5:D14)

Calculate Standard Deviation to Plot Normal Distribution


Step 2 – Finding Data Points in the Normal Distribution Chart

  • Click G5 cell and use the following formula.
  • Press Enter.
=NORM.DIST(D5,$E$5,$F$5,FALSE)

Calculate Normal Distribution Points

Note:

The mean argument and standard_dev argument should be absolute. Press F4 key or enter the dollar sign ($) before the row and column heading.

  • Drag down the Fill Handle to see the result in the rest of the cells.

Drag Fill Handle to Copy Formula

This is the output.

All Normal Distribution Points


Step 3 – Plotting the Normal Distribution Chart in Excel

  • Select the cells in the column >> go to the Home tab >> Editing >> Sort & Filter >> Sort Smallest to Largest.

Sort Marks to Plot Normal Distribution with Mean and Standard Deviation

  • In the Sort Warning window, select Continue with the current selection.
  •  Click OK.

Sort Warning Window

  • Marks are sorted from the smallest to the largest value.

Sorted Marks to Plot Normal Distribution with Mean and Standard Deviation

  • Select the Marks column and the Normal Distribution Points column.
  • Go to the Insert tab >> Insert Line or Area >> Scatter with smooth lines.

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

You will see the normal distribution chart.

Normal Distribution with Mean and Standard Deviation

Read More: How to Create a Distribution Chart in Excel


Step 4 – Modifying the Chart

  • Click the chart >> Chart Elements >>check Axis Titles>> uncheck Gridlines.

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

  • Double-click the Chart Title and both Axis Titles. Rename the titles.

Rename Chart Title and Axis Titles

  • This is the output.

Plot Normal Distribution with Mean and Standard Deviation with Titles

  • Double-click the Horizontal axis.

Access the Horizontal Axis Options

  • Go to Axis Options >> set Minimum Bounds to 50.0.

Fix Minimum Bound of Axis

  • This is the output.

Normal Distribution with Mean and Standard Deviation with New Axis Bounds

  • Double-click the graph line.

Access the Format Data Series Options

  • In the Format Data Series task pane, go to Series Options >> Fill & Line >> Marker >> Marker Options >> choose Built-in.

Add Markers to the Normal Distribution Graph

This is the output.

Plot Normal Distribution with Mean and Standard Deviation

Read More: How to Create a Probability Distribution Graph in Excel


Things to Remember

  • Sort data before plotting the normal distribution.
  • The mean and standard deviation must be numeric. Otherwise, the #VALUE error is displayed.
  • The standard deviation must be greater than zero. Otherwise, the #NUM! error is displayed.

Download Practice Workbook

Download the practice workbook.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
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

2 Comments
  1. It is very useful information. Now I am able to easily plot standard distribution graphs and do my grade analysis.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo