How to Create a Bell Curve with Mean and Standard Deviation in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


What Is 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.


Step-by-Step Procedure to Create a Bell Curve with Mean and Standard Deviation in Excel

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: Create Dataset

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 Student Name and Score in the following dataset.
  • For further calculations, we have inserted the columns Values and Normal Values.

Create Primary Outline


Step 2: Calculate 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.

Calculate Mean

Read More: How to Create a Histogram with Bell Curve in Excel (2 Suitable Examples) 


Step 3: Evaluate 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 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.

Calculate Standard Deviation

Read More: How to Create a Skewed Bell Curve in Excel (With Easy Steps)


Step 4: Calculate Normal Values

Finally, we will normal values for creating 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.

Calculate Normal Values

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

=H5+3*H6

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

  • Then, press Enter.

How to Create a Bell Curve with Mean and Standard Deviation in Excel

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

How to Create a Bell Curve with Mean and Standard Deviation in Excel

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

How to Create a Bell Curve with Mean and Standard Deviation in Excel

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

How to Create a Bell Curve with Mean and Standard Deviation in Excel

Read More: Plot Normal Distribution in Excel with Mean and Standard Deviation


Step 5: Create Bell Curve

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.

Create Bell Curve

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

How to Create a Bell Curve with Mean and Standard Deviation in Excel

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

How to Create a Bell Curve with Mean and Standard Deviation in Excel

  • Next, we have to add Straight Lines from the Shape to dictate the standard deviation in the bell curve.
  • Then, we add 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.

Read More: How to Make Bell Curve in Excel for Performance Appraisal


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


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. l. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo