This post explains tips to add a standard deviation (SD) straight line to a column chart in Google Sheets. We will also include the mean line.
In my previous tutorial related to the function DSTDEV, I posted an image of the mean and SD straight lines on a chart.
The purpose was to show you which numbers (height of dog breeds) are within one standard deviation of the average (mean).
Since that post was not related to charts, I didn’t include the tips to create them.
So in this tutorial, you can learn how to add mean and standard deviation straight lines on a column chart in Google Sheets.
Error Bars
We can use the Error Bars within the chart customization to display or add standard deviation bars on a column chart.
It is the easiest way. But it won’t be visually pleasing. Here are the steps, but concise, if you are interested.
- Select the data in A1:B12, i.e., the labels (dog breed names in column A) and numbers (heights in column B).
- Go to the menu Insert chart and select Column chart.
- Click Series under “Customisation” and check “Error Bars.”
- Select Standard deviation against “Type.” Leave the default value 1 in the field next to it.
That’s it!
Adding Standard Deviation Lines on a Column Chart in Google Sheets
In screenshot # 1, the red horizontal line represents the average height of dogs in the array B2:B12, which is 294.
Note:- Later, I will use the below Average formula in cell C2 to calculate the mean, but in an array form.
=average(B2:B12)For calculating SD and then adding the standard deviation (green) lines on the chart, you can use either of the below formulas/methods.
1. Population:
In the above example, there are 11 dog breeds.
Assume that is the complete data. In such a case, you can use the below STDEV.P formula to find the standard deviation, which returns 119.58.
=STDEV.P(B2:B12)2. Population Sample:
If the data is a sample (a part of a large dataset), there is another function. Use the below STDEV.S formula in this case which would return 125.41.
=STDEV.S(B2:B12)I will use this second formula because my data is a sample.
Please note that the functions STDEV and STDEV.S are the same. You can check my STDEV tutorial to learn the use.
Steps to Add Mean and Standard Deviation Straight Lines on a Chart
I have already given the necessary formulas to plot the said graph. Now let me elaborate on how to modify and include them. I mean, how to format the source data.
Data Formatting
We need three additional columns – one to show the average and the other two columns for standard deviation ±.
Enter the column names in C1:E1 as shown below (please refer to screenshot # 3).
We will enter array formulas in C2:E2. So C3:E12 must be kept blank.
To calculate the mean of the numbers in B2:B12 and fill it in C2:C12, use the below formula in C2.
=ArrayFormula(if(len(B2:B12),average(B2:B12),))Use this formula in D2 to subtract the standard deviation from the mean and fill the range D2:D12.
=ArrayFormula(if(len(B2:B12),C2-STDEV.S($B$2:$B$12)))The following formula goes in E2 to add standard deviation to the mean and fill the range E2:E12.
=ArrayFormula(if(len(B2:B12),C2+STDEV.S($B$2:$B$12)))
With this, our data formatting is complete!
Chart Insertion and Settings
We must choose the combination chart to add standard deviation straight lines on a column chart in Google Sheets.
We use columns to visualize dog breed height and lines for SD and Mean. Here we go!
Select the range A1:E12 and go to Insert menu and select Chart.
Please make sure that the below are the settings under the “Setup” tab in the chart editor.
| Chart type | Combo |
| Stacking | None |
| Data range | A1:E12 |
| X-axis | Dog Breed |
| Series | Height (mm) |
| Average | |
| Standard Deviation | |
| Standard Deviation | |
| Enable tick marks against; | Use row 1 as headers |
| Use column A as labels |
Now under the “Customise” tab, under “Series,” select “Height (mm)” and set the format type to “Column.”
The other three series (Average, Standard Derivation [twice]) must be “Line.”
This way, we can add mean and standard deviation lines to a column chart in Google Sheets.
In all aspects, it will look better than the Error Bar one above.
That’s all. Thanks for the stay. Enjoy!
 straight line to a column chart in Google Sheets. We will also include the Mean line){kind=link}