How to Make a Bell Curve in Google Sheets (Step-by-step Guide)

Charts and graphs are valuable resources for visualizing data in a spreadsheet. And guess what? Google Sheets provides you with tons of useful tools to create charts and graphs seamlessly. One remarkable way to visualize data in your spreadsheet is to use a Google Sheets distribution chart, popularly known as a bell curve graph.

To be able to make a bell curve in Google Sheets, you’ll need to leverage the normal distribution of data. Making a bell curve in Google Sheets provides you with better insight into your data.

For starters, a bell curve graph helps you know where the most data points exist in a chart. Not just that, it also helps you know how close the points are, especially in relation to one another. Having a bell curve in Google Sheets is a brilliant way to know what data points are outliers.

In this Google Sheets tutorial, we will give you a walkthrough of what a bell curve in Google Sheets is and why you should use one. And yes, we will also show you a step-by-step guide on how you can make a bell curve in Google Sheets.

But before we jump into the practical aspect of this guide, let’s start with the basics.

The Bell curve: What is it?

A bell curve graph, also popularly known as a normal distribution chart, is a brilliant tool offered by Google Sheets for analyzing data. The line typically created when making a bell curve in Google Sheets looks symmetrical in shape, hence the name.

The highest point on the curve represents the mean, median, and mode of the data set you’re dealing with. Plus, it is the densest part of the graph. A bell curve typically has a curve width that represents the standard deviation around the mean.

There are specific rules you must know before creating a bell curve in Google Sheets. To start with, the data typically follows the 68, 95, and 99.7% rules in a bell curve. What this simply means is that 68% of the information in a bell curve is within one standard deviation of the data’s mean. On the other hand, 95% of the data is housed within two standard deviations of the mean. And lastly, 99.7% of the data is within three standard deviations of its mean.

We know this sounds a bit confusing, but when we get into the practical aspects of this guide, you’ll have a better understanding of what we just explained.

Why you should use a bell curve in Google Sheets

Even though bell curves are popularly used in finance to analyze stocks and show property values, this remarkable tool is finding tremendous use cases in several areas, including daily situations like reviewing scores for products, visualizing classroom grades, and analyzing data groups with denser values.

With a bell curve graph, you can easily decide whether it is safe to invest in a stock. Not just that, you can also use a bell curve graph to get a fair deal on a property.

If you’re ever tasked with comparing review scores, using a bell curve graph will allow you to make a better judgment.

Without mincing words, there are several use cases for a bell curve in Google Sheets. And as we delve deeper, we will show you exactly how you can make a bell curve in Google Sheets.

Making a bell curve in Google Sheets

Having covered the basics of everything you need to know about a bell curve graph, we want to jump into the exciting part, which is actually making a bell curve in Google Sheets. While the process might be a bit long, our comprehensive step-by-step guide will simplify the entire process.

For our guide, we will be working with sample data featuring students’ scores. Here, check it out.

But before we can make a bell curve graph in Google Sheets, using this sample data, we need to first do a few calculations. Here are the calculations we need to make.

First, we need to find the average (mean value)
The standard deviation value
The +/- standard deviation values of the average
The range sequence
The normal distribution for all data points.

Before we show you how to make the calculations above, we need to create columns for each of these calculations in our spreadsheet. So let’s go ahead and update our spreadsheet.

From the image above, you can see that we have now updated our spreadsheet to include columns for:

Average

Standard deviation
Average
Low
High
Sequence and Distribution

With that done, we can now jump into the practical aspects- creating a bell curve in Google Sheets. Follow the step below to get started.

Step 1: Calculate the average of the data

The first thing we want to do is calculate the average of the data we want to use to make the bell curve. And to do this, we need to use the AVERAGE function. Here is the formula we will use: =AVERAGE(B2:B12).

So click on cell C2, which is where we would like to generate the result, and enter the following formula in the formula tab: =Average(B2:B12).

After hitting enter, you should get the average return as 82.63636364.

Step 2: Calculate the standard deviation

After successfully finding the average for the data we are working with, we also need to calculate the standard deviation. To find the standard deviation, we will use the following formula:

=STDEV.P(B2:B12).

To apply the above formula, head to cell D2, where we would like to generate the result and type the formula in the formula bar.

With the formula now type in. Hit enter. This should generate a standard deviation of 3.418471941.

Step 3: Calculate the low standard deviation value of the average

Calculating the low standard deviation value of the average is pretty straightforward. To make this easy for you, you can use the following formula:

=average-3*standard deviation.

For our example, we used this exact formula: =C2-3*D2.

To execute this step, simply click on Cell E2, where we want to generate the result for our low standard deviation value of the average, and type the following formula in the formula bar: =C2-3*D2.

After typing your formula in the formula bar, hit enter on your keyboard. You should get the following figures in cell E2: 72.38094781.

Step 4: Calculate the high standard deviation value of the average

Now that we have calculated the low standard deviation value of the average, we need to also calculate the high standard deviation value of the average. And to achieve that, we will use the following formula.

=average+3*standard deviation.

For our guide, we will use the following exact formula: =C2+3*D2.

To execute this step, you’ll need to click cell F2, where we want our result generated. After that, type the following formula in the formula bar: =C2+3*D2..

After typing the formula, you can hit enter. If you did everything right, you should get the following figure in cell F2: 92.89177946.

Step 5: Generate a sequence of numbers

After generating results for columns C, D, E, and F, we also need to generate a sequence number for column G. And for this, we will use the formula below:

=sequence(high-low+1,1,low).

For this tutorial, we used the formula: =sequence(F2-E2+1,1,E2).

To execute this step, we need to choose the cell where we would like the result to be generated. So for our tutorial, we will click on cell G2. Now, head to the formula bar and type the following formula: =sequence(F2-E2+1,1,E2)

After hitting enter, you should see a sequence of whole numbers in cell G2. Here is what ours looks like:

Step 6: Calculate the normal distribution of all the data values

Done with the sequence? Great. Now, we need to calculate the normal distribution of all the data values. To do this, we will use the NORM.DIST function.

Here is the pattern we will use for our guide: =ArrayFormula(NORM.DIST(Data Cell Range, Average Standard Deviation,False)).

For this guide, we will enter the below formula:

=ARRAYFORMULA(NORM.DIST(G2:G22,$C$2,$D$2,FALSE)) in cell H2.

If you did everything right, you should have a spreadsheet that looks like this:

Step 6: Create a bell curve

Having finished the necessary calculations, it’s time to create a bell curve in Google Sheets. To start, we need to highlight the values in the Sequence and Distribution columns.

Step 6.1: Insert > Chart

With the values in Columns C and D selected; head to the Insert menu and select the option for Chart.

After executing the steps we showed you, Google Sheets will automatically create a chart in your spreadsheet. Here is what ours looks like:

Step 7: Change Line chart to Smooth line chart

Even though we have now created our bell curve, we aren’t done yet. At this point we want the curve to be smooth. Here is how to do that.

Click anywhere within the chart. You should see three dots on the top right of the chart, click on it and select Edit chart.

This should launch the Chart editor. Now, head to the Chart editor on the extreme right of your spreadsheet and select Setup. Underneath that, you’ll find the option for Chart type. Click on the option and select Smooth line chart.

Here is a short video showing you how to execute this step:

After the changes have been effected, your chart should now look exactly like this:

Conclusion

Making a bell curve in Google Sheets is a bit tricky. Plus, there are many steps involved. Thankfully, we have simplified the entire process in today’s guide. Simply follow all the detailed steps outlined in today’s guide and you should be able to create a bell curve graph without any fuss.