Charts and graphs are a great way to visualize data in your spreadsheets. Thankfully, Google Sheets offers a robust set of tools that allow you to do so. One great way is to use a Google Sheets distribution chart. It is usually known as a bell curve graph. To create one, you will need to use the normal distribution of the data.
Creating a bell curve graph allows you to understand your data better. It helps you know where most of the data points exist in a chart, how close the points are in relation to one another, and what data points can be considered outliers. This article will discuss what a bell curve is, why you should use one and how to make a normal distribution curve in Google Sheets.
Read on to learn how to make a bell curve in Google Sheets in a few simple steps.
Table of Contents
What Is a Bell Curve?
Bell curve graphs, also called normal distribution charts, are generally used to analyze financial data in statistics. The line created in this graph is usually a symmetrical curve that has a bell shape, hence the name.
The highest part of the curve shows the mean, median, and mode of the data and is the densest part of the graph. The curve’s width shows the standard deviation around the mean.
The data usually follows a 68/95/99.7 rule in a bell curve. Here, 68% of the information is within one standard deviation of the data’s mean. 95% of the data is within two standard deviations of the mean, and 99.7% of the data is within three standard deviations of its mean.
Examples of the bell curve can often be seen in real life. Take the example of the height of students in a class. You will conclude that most of the students are close to the data set’s average, while some fall just a bit below average or just a bit above. In a bell curve, this is to the right and left of the highest point on the curve.
Why You Should Use a Bell Curve
Bell curve Google Sheets graphs are not just used in finance to analyze stocks or to showcase property values. You can also use them in daily situations like visualizing classroom grades, reviewing scores for products, and generally for data groups with denser values near the data set’s mean.
These graphs can help you decide whether a stock is safe to invest in. It also allows you to make a fair offer on a property. These are also useful in comparing review scores as the average value is usually near the middle of the scale, giving you a much clearer judgment of whether you should buy that product or not.
For example, on a 1 to 10 review score scale, if most reviews are at around 8, then 8 will be the highest point in the bell curve, whereas others will be lower. Let’s say there is a small minority that has rated the product at 3, then that could be considered an outlier which means you can not regard them as reliable.
How to Make a Bell Curve in Google Sheets
Building a bell curve works similarly to making a line of best fit to a scatter plot in Google Sheets. But, before we can create a Google Sheets bell curve, let’s discuss the dataset we will use. The example spreadsheet below contains the exam marks for 15 students.
The first thing we need to do is add the columns to the spreadsheet. These are the average, standard deviation, standard deviation +3, standard deviation -3, sequence, and the distribution.
First, we need to calculate the mean (average). You can do this by using the AVERAGE formula. To do so, follow these steps:
- Click on the cell where you wish to input the formula (below the Average title in the example, D2).
- Enter the starting part of the formula that is =AVERAGE(
- Enter the range. In this case, it is the cell range B2:B16
- Add a closing bracket to finish off the formula
- Press Enter to execute the formula
Now, let’s find the standard deviation of the data using the STDEV.P formula. You can do this by using the same steps as above.
Here is the formula used to find the standard deviation in this case:
Note the range stays the same. This is important for building an accurate bell curve.
Now, calculate the standard deviation -3 and standard deviation +3 values. To find the standard deviation -3 value, multiply the standard deviation by 3 and then subtract it from the average. For standard deviation +3, multiply the standard deviation by 3 and then add it to the average value.
The formulas used here are:
Standard Deviation (-3): =D2-(E2*3)
Standard Deviation (+3): =D2+(E2*3)
Now we need to show the numbers in the entire sequence. This will be used to plot the graph.
The formula used to do this is:
This function simply fills an ordered list of numbers based on your given parameters.
The last column contains the distribution of the data. You do this by using an ARRAYFORMULA and the NORM.DIST formula. The syntax for the entire formula is:
=ARRAYFORMULA(NORM.DIST(Range, Average, SD, FALSE))
Building a Bell Curve in Google Sheets
Now that we have the data we need let’s create the bell curve. Here are the steps to do so:
- To start, highlight the data in the Sequence and Distribution columns
- Click on Insert in the top bar
- In the drop-down menu, click on Chart
- The Chart editor will show up on the right side of the screen
- In the Chart type, select Line chart
Customizing the Standard Deviation Graph in Google Sheets
Although it is optional, you can customize the look of the bell curve chart.
Doing this requires you to access the chart editor. To do this:
- Click on the three dots icon on the top right of the chart.
- Click on Edit chart. This will open the chart editor on the right side of the screen.
- In the Chart Editor, click on Customize. This will take you to another window that allows you to change the visual appearance of the chart.
Here you will find various sections. You can change the background color, font, title text, line color, opacity, and other options. When you finish customizing the chart to your liking, click on the cross symbol to close the chart editor. Doing so will save your changes.
How Do You Make a Bell Curve in Google Sheets?
To create a standard deviation graph on Google Sheets, first, you need to find the data’s mean and standard deviation. Then, calculate the standard deviation -3 and standard deviation +3 values. From there, create a sequence starting from the smallest values to the highest ones in the data set. Find the distribution of the data in the values. Once you have this data, use the Chart feature in Sheets to create a line graph. This will be in the shape of a bell curve.
How Do You Construct a Bell Curve? / How Do I Create a Curved Trendline in Google Sheets?
- Select the Sequence and Distribution values once you have the required values to create a bell curve.
- There, click on Insert and then on Chart from the drop-down menu.
- Select Line graph in the Chart type section of the Chart editor
Wrapping Up the Bell Curve
Creating a bell curve requires you to do many steps and find several initial data points. However, the end result is worth the trouble.
When you create the bell curve, pay close attention to the size of the curvature. A tall, narrow curve means a small standard deviation which means the data is not too spread out. A short but wide curve represents a sizeable standard deviation, meaning the points are much more spread out.
We hope this article helped you better understand how to make a bell curve in Google Sheets. If you’d like to learn more about charting in Google Sheets, check out these guides: