When working in Google Sheets there may be times where you want to show the normal distribution for a dataset.
One great way to do that is to make a bell curve.
In this tutorial, I will show you how to make a bell curve in Google Sheets.
Table of Contents
What is a Bell Curve?
A bell curve (also called normal distribution) is a type of distribution for a variable that is commonly used in statistics and for analyzing financial data.
This is a type of distribution that occurs naturally in many situations where the graph is a symmetrical bell-shaped curve (which is why it’s called a bell curve).
The highest point of a bell curve shows the mean, mode, and median of the data, which is where most of the data points cluster.
The width of the curve shows the standard deviation around the mean.
In a bell curve (normal distribution) you commonly see the 68-95-99.7 rule which is where:
- 68% of data is within 1 standard deviation of the mean
- 95% of data is within 2 standard deviations of the mean
- 99.7% of the data is within 3 standard deviations of the mean
Making a Bell Curve
To make a bell curve in Google Sheets, we need to calculate a few data points to plot the graph with.
This is what we need:
- The average (mean)
- The standard deviation
- The standard deviation +/- 3 values
- The range sequence
- The normal distribution of the data points
This is the data that I will be using to make my bell curve:
The data contains test scores for various students. I will use this data to calculate each of the data points for our normal distribution graph.
The first thing I am going to do is add 6 new columns to my spreadsheet.
The six new columns will be:
- Average
- Standard deviation
- Standard deviation -3
- Standard deviation +3
- Sequence
- Distribution
Here is what this looks like in my spreadsheet:
Now we will calculate each of these data points.
To calculate the average we will use the AVERAGE function and place our data range inside the function:
The formula I have used in this example is:
=AVERAGE(B2:B12)
- B2:B12 is my data range that contains the test scores that I want to create the bell curve with
The next thing we need to calculate is the standard deviation. There are two different functions you can use for this: STDEV.P and STDEV.S
- STDEV.P is used if you are calculating the standard deviation from the entire list (population) of values
- STDEV.S is used if you are calculating the standard deviation with a sample of the data
In my example, I have all of the test scores in my spreadsheet so I will use STDEV.P since I have the entire population to calculate the standard deviation from.
The formula that I have used for this is
=STDEV.P(B2:B12)
- B2:B12 is the data range that contains the list of scores that will be used to create the bell curve.
The next two columns that we need to calculate are the standard deviation +3 values and standard deviation -3 values.
To calculate these columns we will be taking average -3x the standard deviation and the average +3x the standard deviation.
Here is the minus 3x standard deviation:
The exact formula I have used is:
=C2–(D2*3)
- This is the average minus the standard deviation x3
Next, I will add the formula to calculate the average +3x the standard deviation:
The formula I have used for this is:
=C2+(D2*3)
- This is the average plus 3x the standard deviation
Next, we will use the SEQUENCE function to show the entire sequence of numbers for the test scores.
The formula for calculating the sequence in this scenario is:
=SEQUENCE(STDEV(+3)-STDEV(-3)+1,1,STDEV(-3))
Here is how this looks in my spreadsheet:
The exact formula in my spreadsheet is:
=SEQUENCE(F2-E2+1,1,E2)
The last column that we need to calculate is the distribution. We will use the NORM.DIST along with ARRAYFORMULA to calculate this.
The formula we will use for this is:
=ARRAYFORMULA(NORM.DIST(Sequence_Range,Average,Standard_Deviation,FALSE))
In my spreadsheet the exact formula would be:
=ARRAYFORMULA(NORM.DIST(G2:G22,$C$2,$D$2,FALSE))
Now that we have all of this calculated, we can use it to create the bell curve.
Here are the steps:
1. First highlight the data in your sequence and distribution columns
2. Then select in the Insert chart button or in the top menu select Insert>Chart
3. In the Chart editor, change the chart type to a Smooth line chart
4. You will now have a bell curve graph showing in your spreadsheet
Closing Thoughts
One thing to pay attention to with this graph is the size of the curve.
A narrow and tall curve shows that there is a low standard deviation so the data is not spread out as far. I wider and shorter curve means a larger standard deviation which means the data points are a little more spread out from the average.
Hopefully, you found this tutorial helpful!
More Google Sheets Tutorials:
How to Make a Histogram
How to Make a Scatter Plot