Bell Curve in Excel (Plot and Format)

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.

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.

Let’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.

Step-01: Calculate Mean

• Type the following formula in cell C15 and hit ENTER.

Step-02: Calculate Standard Deviation

• Type the following formula in cell C16 and press ENTER.

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.

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.

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

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

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

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.

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

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

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.

• As a result, we will get a Bell curve. This is also known as the normal distribution 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.

Step-08: Plot Label Data

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

• Click on Add option to insert the data points.

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

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

• We will see a line graph in the 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.

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

• Hence, we will get a 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.

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

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

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.

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

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

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.

• Hence the gridlines will be removed from the chart.

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

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

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

• Create a Bell Curve in Excel
• Create a Skewed Bell Curve
• Bell Curve with Mean and Standard Deviation
• Bell Curve for Performance Appraisal

<< Go Back to Excel for Statistics | Learn Excel