Create a Bell Curve with Mean and Standard Deviation in Excel

If you are looking for some special tricks to create a bell curve with mean and standard deviation in Excel, you’ve come to the right place. There is one way to create a bell curve with mean and standard deviation in Excel. This article will discuss every step of this method to create a bell curve with mean and standard deviation in Excel. Let’s follow the complete guide to learn all of this.

Watch Video – Create Bell Curve with Mean and Standard Deviation

What Is a Bell Curve?

There is a graph that shows the normal distribution of a variable called the Bell Curve. This is also known as the Normal Distribution Curve. We See this distribution all around us. When we review scores from an exam, we typically find that the majority of the numbers are in the middle. The peak on this curve represents the mean score. Both sides of this curve are lower. Additionally, it indicates that the probability is much lower for extreme values (i.e highest or lowest)

Bell Curve features include:

According to the data, 68.2% of the distribution is within one standard deviation of the mean.
Moreover, 95.5% of the distribution falls within two standard deviations of the average.
Finally, 99.7% of the distribution lies within three standard deviations of the mean.

What Is Mean and Standard Deviation?

Mean

We define the mean as the average of a set of values. An equal distribution of values across a data set is meant by the mean. Generally, the mean means the central tendency of a probability distribution in statistics.

Standard Deviation

Generally, the standard deviation in statistics measures the amount of variation or distribution in a set of numbers. If the value of standard deviation is low it means that the values are close to the mean value. On the other hand, if the value of standard deviation is high, it means the values are distributed in a larger range.

In the following section, we will use one effective and tricky method to create a bell curve with mean and standard deviation in Excel. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Step 1: Creating a Dataset in Excel

Here, we have created the basic outlines of creating a bell curve with mean and standard deviation in Excel.

In the following image, we can see the basic outlines of the bell curve and its related dataset.
Here, we have the Student Name and Score in the following dataset.
For further calculations, we have inserted the columns Values and Normal Values.

Step 2: Calculating Mean

Now we are going to determine the mean for creating a bell curve. We want to use the AVERAGE function to determine the mean value.

First of all, to determine the mean, we will use the following formula in the cell H5:

=AVERAGE(C5:C12)

This AVERAGE function will return the mean value for the range of the cells C5:C12.

Then, press Enter.

Step 3: Determining Standard Deviation

Here, we will determine the standard deviation for creating a bell curve. To do this, we will use the STDEV.P function.

Next, to determine the standard deviation, we will use the following formula in the cell H6:

=STDEV.P(C5:C12)

The above function will return the standard deviations of the range of the cells C5:C12.

Then, press Enter.

Step 4: Calculating Normal Values

Finally, we will normal values to create a bell curve. To do this you have to follow the following process. Here, we will use the NORM.DIST function to determine the normal distribution values.

According to our previous discussion, 7% of maximum and lowest values are within three standard deviations.
Next, to determine the value of 99.7% Low, we will use the following formula in the cell H7:

=H5-3*H6

Here, cell H6 is the standard deviation of the dataset.

Then, press Enter.

Next, to determine the value of 99.7% High, we will use the following formula in cell H8:

=H5+3*H6

Here, cell H6 is the standard deviation of the dataset.

Then, press Enter.

Then, we are putting 7 in cell H9. We want 8 values that are the reason why we are putting 1 less than our desired value.
Next, to determine the value of Gap, we will use the following formula in the cell H10:

=(H8-H7)/H9

Then, press Enter.

Now, we are going to add values to column D in the dataset.
Firstly, the first value will be from cell H7.
Then, you have to type the following formula in cell D6:

=D5+$H$10

Then, press Enter.

Next, drag the Fill Handle icon.
As a consequence, you will get the following Values column.

Next, to determine Normal Values, we will use the following formula in the cell E5:

=NORM.DIST(D5,$H$5,$H$6,FALSE)

This formula returns the normal distribution for the given mean and standard deviation. We have set these values in the code. Moreover, we have set cumulative to False to ensure we will get the ‘probability density function’.

Then, press Enter.

Next, drag the Fill Handle icon.
As a consequence, you will get the following Normal Values column.

Step 5: Creating Bell Curve with Mean and Standard Deviation in Excel

Now, we are going to create the bell curve. We have to follow the following process:

Firstly, select the range of the cells D5:E12.
Then, go to the Insert tab. Select Insert Scatter (X, Y) or Bubble Chart, and finally select Scatter with Smooth Lines.

As a consequence, we will be able to get our basic bell curve.
Now, we want to format our bell curve.

First of all, double-click on the horizontal axis and it will bring the Format Axis dialog box.
Next, you have to set the Minumum Bounds to 30 and Maximum Bounds to 85 as shown below:

Next, you have to uncheck Gridlines and Vertical Axis. Here, we get the Chart Elements by clicking on the Plus Sign.

Next, we have to add Straight Lines from the Shape to dictate the standard deviation in the bell curve.
Then, we add the Bell Curve as a Chart Title to our curve.
In addition, the Yellow line signifies the mean of the data in the bell curve. We have added these straight lines by turning on the Gridlines again.
Finally, we have turned these lines off.
As a consequence, we will be able to get the following bell curve.

💬 Things to Remember

✎ When you use the function carefully give all the required parentheses. Also, when you use NORM.DIST function, you’ve to make mean and standard deviation as an absolute cell reference.

✎ You have to adjust the row height after following each method.

Practice Section

We have already added a practice dataset in the Excel file. Therefore, you can easily follow our methods and practice them yourself.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and graphs in different spreadsheets for a clear understanding.

Bell Curve with Mean and Standard Deviation.xlsx

Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to create a bell curve with mean and standard deviation in Excel. If you have any queries or recommendations, please share them in the comments section below. Keep learning new methods and keep growing!

<< Go Back to Bell Curve in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!