Bell Curve in Excel (Plot and Format)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn about the Bell curve in Excel. You will get the step-by-step procedure to draw a Bell curve with the necessary explanation here.

The Bell curve provides a quick visualization of a dataset summary. We can get a dataset’s mean, mode and median using the Bell curve of a normal distribution. We can get an overall idea about the checkpoints that divide the dataset into multiple regions.

image0-bell curve overview


Download Practice Workbook

You can download the workbook from here and practice yourself.


What Is Bell Curve?

The Bell curve is a normal distribution graph with a rounded peak with two gradually declining ends. The Bell curve is an essential representation of a normal distribution. The data is distributed in multiple regions with fixed percentage values.

  • 68.2% of the dataset falls within one standard deviation of the mean in the center.
  • 95.5% of the dataset falls within two standard deviations of the mean in the center.
  • And 99.7% of the dataset lies within three standard deviations of the mean in the center.

image1-bell curveLet’s get a quick review of what mean and standard deviation refers to.

Mean: Mean is the mathematical average of two or more numbers. For example, we can get the mean price of multiple TVs by finding out the average of the TV prices.

Standard Deviation: Standard deviation is a quantity that is expressed by how much the members of a dataset differ from the mean value. This is a measurement of the dispersion of the dataset.


When Do We Need to Use Bell Curves?

We need to use the Bell curves to visualize the distribution of a dataset. This dataset can be the test scores of an exam. From the bell curve, we get an overall idea about the dispersion of the dataset with respect to the mean value.


How to Create a Bell Curve in Excel

Here, we will demonstrate step-by-step procedures to create a Bell curve in Excel. The following image contains the dataset on which we will work throughout this article.

image2-Dataset


Step-01: Calculate Mean

  • Type the following formula in cell C15 and hit ENTER.
=AVERAGE(D5:D13)

image3-Mean


Step-02: Calculate Standard Deviation

  • Type the following formula in cell C16 and press ENTER.
=STDEV.P(D5:D13)

image4-SD


Step-03: Compute Different SD Values

  • Now we need to calculate different SD values and perform calculations using them.
  • Type the following formulas in cells C19, C20, C21, C22, C23, C24 and C25 and hit ENTER to get the respective values.
=C15-3*C16
=C15-2*C16
=C15-1*C16
=C15
=C15+1*C16
=C15+2*C16
=C15+3*C16

image5-Different SD values


Step-04: Calculate X-axis Values of Bell Curve

  • Now, we will get the x-axis values to plot the Bell Curve. Note that the range is between 3 SD Below (78) and 3 SD Above (141).
  • Type the following formula in cell B28 and hit ENTER.
=C19

image6-first x value

  • Click on cell B28, go to Fill and click on Series.

image7-series1

  • Fill in the series fields as shown in the following image and click OK.

image8-Filling Series

  • As a result, the series will be filled up to 141.

image9-series is filled


Step-05: Compute Normal Distribution Values (Y-axis Values) of Bell Curve

  • Now we need to calculate corresponding y-axis values for the Bell curve.
  • Type the following formula in cell C28 and press ENTER to get the normal distribution value of 78.
=NORM.DIST(B28,$C$15,$C$16,FALSE)
Note: You can also use the following formula to get the normal distribution value of 78.
=EXP(-((B28-$C$15)^2)/(2*$C$16^2))/($C$16*SQRT(2*PI()))

image10-Normal Value1

  • Now we can autofill other normal distribution values.
  • Place the mouse cursor on the bottom right corner of cell C28 and double-click on the AutoFill handle.

image11-autofill

  • As a result, normal distribution values are calculated and placed in the corresponding cells.
  • We can get a glimpse of it from the following image.

image12-values auto filled


Step-06: Insert Chart of Bell Curve

  • Now we can insert the chart of the Bell curve.
  • Select cell B28, and navigate to Insert >> Insert Scatter (X,Y) or Bubble Chart >> Scatter with Smooth Lines.

image13-inserting chart

  • As a result, we will get a Bell curve. This is also known as the normal distribution curve.

image14-raw curve


Step-07: Create a Label Table for Bell Curve

  • Now we want to label different SD positions.
  • Insert 0 value in cells D19:D25 as the y-axis values of the labels.

image15-y values


Step-08: Plot Label Data

  • Right-click on the chart area and choose Select Data.

image16-select data

  • Click on Add option to insert the data points.

image17-Add data

  • As a result, an Edit Series window will pop up.

image18-edit series

  • Fill the fields in the window as shown in the following figure and click OK.

image19-Edit series2

  • We will see a line graph in the chart.

image20-data in chart


Step-09: Change Chart Type of Label Series

  • We do not need the continuous line chart here. Rather we need the data point representing different SD values.
  • Right-click on the chart area and select Change Chart Type.

image21-change chart type

  • Go to Combo, click on the drop-down against Series2, click on Scatter, and finally click OK.

image22-change chart type2

  • Hence, we will get a scatter plot.

image23-scatter plot


Step-10: Set Horizontal Axis Scale

  • Now we want to adjust the horizontal axis scale to get a better view of the Bell curve.
  • Right-click on the horizontal axis and select Format Axis.

image24-Format axis

  • In the format axis window, fill the data as shown in the following image.

image25-format axis

  • As a result, the graph will be rescaled accordingly.

Image26-rescale horizontal axis in Excel.


Step-11: Positioning Data Labels

  • Now we want to position the data points below the horizontal axis line.
  • Select any data point of the scatter plot, click on Chart Elements, go to Data Labels, and click on More Options.

image27-data labels

  • As a result, the Format Data Labels window will appear.
  • Select the options as shown in the following image.

image28-position

  • Therefore, data labels will be added to the chart.

image29-data labels are added


Step-12: Format Bell Curve in Excel

  • At this point, we want to format the chart for a better look.
  • Click on Chart Elements and uncheck the tick against Gridlines.
  • image30-uncheck grid line.Hence the gridlines will be removed from the chart.

image31-grid lines removed

  • Now we want to add vertical lines on the data labels to distinguish different SD points.
  • Click on the chart area, go to Insert >> Shapes >> Line and select a line.

image32-line insertion

  • Place the lines on data labels and adjust the length by dragging the lines while pressing and holding the Shift key.
  • As a result, our Bell curve or normal distribution curve will look like the following image.

image33-bell curve


Things to Remember

While working on the Bell curve in Excel, we should keep some points in mind.

  • The dataset should follow exactly or nearly a normal distribution.
  • Use the NORM.DIST function properly.
  • Make the mean and standard deviation absolute references.

Frequently Asked Questions

1. How can I generate a Bell curve in Excel without Data Analysis tool?

You can generate a Bell curve in Excel without a Data Analysis tool. In this regard, you need to calculate the mean and standard deviation and the normal distribution values using formulas. Then you can insert a chart to plot a Bell curve.

2. Can I customize the appearance of the bell curve in Excel?

Yes, you can customize the appearance of the bell curve in Excel. You can apply available regular chart customization.

3. Can I use the Bell curve in Excel to identify outliers or anomalies in my data?

Yes, the Bell curve in Excel helps to spot outliers or anomalies in your data.


Conclusion

I’ve put together the essential descriptions of the Bell curve in this article. If you have gone through this, you will now be able to plot the Bell curve according to your need. This will help you to visualize your dataset and provide you with a solid understanding of your dataset. If you face any difficulty regarding the Bell curve, please let us know in the comment section. Team Exceldemy will be there to solve your problem. Have a good day!


Bell Curve in Excel: Knowledge Hub


<< Go Back to Excel for StatisticsLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hadi Ul Bashar
Hadi Ul Bashar

Hadi Ul Bashar holds a BSc degree in Biomedical Engineering from Bangladesh University of Engineering and Technology.  In January 2023, Bashar joined SOFTEKO as an Excel and VBA content developer. Since then, he has authored over 50 articles. He was engaged in two specialized training programs focused on VBA and Chart & Dashboard designing in Excel. Currently he is creating video content on Excel and VBA and has created over 70 videos. Reading books and traveling are his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo