Rafiul Haq
We often need to plot a Bell Curve in the field of Statistics. Using Excel, that task will be so much easier. In this article, we will show you 2 easy methods of how to create a Bell Curve in Excel.
Download Practice Workbook
What Is a Bell Curve?
The Bell Curve is a graph that represents the normal distribution of a variable. This is also known as the Normal Distribution Curve. In our nature, we see this distribution everywhere. If we survey marks from an exam, we will notice that most of the numbers are in the middle. The peak point of this Curve signifies the mean of the distribution. The Curve is lower on both sides. This also denotes the probability, which will be much lower for the extreme values (i.e. highest or lowest).
The features of the Bell Curve are –
- First, 68.2% of the distribution is between one standard deviation of the mean.
- Next, 95.5% of the distribution falls between two standard deviations of the average.
- Lastly, 99.7% of the distribution falls between three standard deviations of the mean.
2 Ways to Create a Bell Curve in Excel
To demonstrate our methods, we have taken a dataset consisting of 2 columns: “Aspirant”, and “Score”. This dataset represents 8 student’s obtained scores in a particular subject. We will use this dataset for the first method only.
1. Create a Bell Curve in Excel with a Dataset
For the first method, we will use this dataset to create a Bell Curve in Excel. We’ll use AVERAGE and STDEV.P functions to find our dataset’s mean and standard deviation. Then we’ll use these data to create data points for our Bell Curve. Finally, we will use the NORM.DIST to find Normal data points for completing our Curve.
Steps:
- First, find out the mean of the distribution by typing the following formula in cell C14 and then press ENTER.
=AVERAGE(C5:C12)
This function will find out the mean value for the cell range C5:C12.
- Afterward, find out the mean of the distribution by typing the following formula in cell C15 and then press ENTER.
=STDEV.P(C5:C12)
This function will output the standard deviations for the cell range.
We have already discussed that the 99.7% of maximum and lowest values will be inside 3 standard deviations.
- Then, type the formula from below in cell C16.
=C14-3*C15
- Next, type the formula from below in cell C17.
=C14+3*C15
- Then, we are putting 7 in cell C18. We want 8 values, that is why we are putting 1 less than our desired value.
- Afterward, type this formula in cell C19.
=(C17-C16)/C18
These steps should look like this.
Now, we will add values to column D in the dataset.
- To begin with, the first value will be from cell C16.
- Then, select the cell range D6:D12 and type this formula.
=D5+$C$19
We are using the interval value to get the other values using this formula.
- After that, press CTRL+ENTER.
This will AutoFill the formula to the selected cells.
- Then, select the cell range E5:E12 and type this formula.
=NORM.DIST(D5,$C$14,$C$15,FALSE)
This formula returns the normal distribution for the given mean and standard deviation. We have set these values in the code. Moreover, we’ve set Cumulative to False, this will ensure we get the “probability density function”.
- Then, press CTRL+ENTER.
Thus, we’ve prepared our dataset to create a Bell Curve in Excel.
Now, we will create the Bell Curve.
- To begin with, select the cell range D5:E12.
- Next, from the Insert tab >>> “Insert Scatter (X,Y) or Bubble Chart” >>> select Scatter with Smooth Lines.
This will be our basic Bell Curve.
Now, we will format our Bell Curve.
- First, Double Click on the horizontal axis and it will bring up the Format Axis dialog box.
- Then, set the Bounds –
- Minimum: 30.
- Maximum: 85.
- Then, remove Gridlines and Vertical Axis by deselecting those. Here, we display the Chart Elements by clicking on the Plus sign.
- Afterward, we have added straight lines from Shape to denote the standard deviation in the Curve.
- Then, we’ve added a Chart Title to our Curve.
- Additionally, the Green line signifies the mean of the data in the Bell Curve. We’ve added these straight lines by turning on the Gridlines again.
- Finally, we have turned these lines off.
- So, this is what the final image should look like.
Read More: How to Create a Histogram with Bell Curve in Excel (2 Suitable Examples)
2. Create a Bell Curve without Dataset in Excel
For the last method, we will not have an existing dataset and we will create one to create a Bell Curve in Excel. Here we will use the “NORM.S.DIST” function in our cause. Moreover, we’re considering the mean is 0, and the standard deviation is 1.
Steps:
We’ve 2 columns in our dataset.
- To begin with, we have typed the first value as -3 in cell B5.
- We’re putting it as the 3 standard deviations from our value (our mean is 0 here).
- Then, select the cell range B6:B15 and type the following formula.
=B5+0.6
- Then, press CTRL+ENTER to AutoFill the formula.
- Afterward, select the cell range C5:C15 and type the following formula.
=NORM.S.DIST(B5,FALSE)
We use this function when we have 0 mean and 1 standard deviation. Again, we are using False in the function to return the “probability mass function”.
- Then, press CTRL+ENTER.
- Lastly, as shown in the first method, create the Bell Curve.
In conclusion, we have shown you the last method of creating a Bell curve in Excel without any existing dataset.
Read More: How to Create a Skewed Bell Curve in Excel (With Easy Steps)
Practice Section
We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.
Conclusion
We have shown you 4 quick methods of how to create a Bell Curve in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
