How To Calculate Standard Deviation In Google Sheets

Kieran Dixon Updated:  October 18, 2021

Google Sheets allows you to quickly find the standard deviation of a data set.

It's actually quite a powerful tool to complete statistical analysis.

Before you can get the standard deviation, you need to ask yourself two questions about your data:

1. Is your data a sample or a population?
2. How do you want to handle non-numeric data (e.g. text and Boolean)?

If you've already answered these questions, you can skip to the functions or example calculation.

A data set's standard deviation simply indicates how much each data point deviates from the mean (the average).

A larger standard deviation means the data points are more dispersed.

A smaller standard deviation means the data points group closely around the mean.

Question 1: Standard Deviation Of A Sample Or Population?

Standard deviation is calculated differently based on whether the data is a sample from a larger population or the entire population itself.

Here are a couple of examples to clarify the difference between a sample and a population:

A school year group of 150 students sits a 100-question multiple choice test.

A teacher at this school has access to only the results of their 30 students.

The teacher wants to calculate:

1. The standard deviation of their class
2. The standard deviation of the year group

For the standard deviation of the class, the teacher should use the population standard deviation equation.

Why?

Because the teacher has access to all of the relevant data points. None are being left out.

For the standard deviation of the year group, the teacher should use the sample standard deviation equation.

Why?

Because the teacher has access to only 30 of the 150 results. They have a sample of a larger population and some data points from the population will be excluded from the calculation.

Here's the difference between the equations:

Sample Standard Deviation Equation

Where:

• s = Sample standard deviation
• Σ = The sum of…
• X = Each data point in the sample
• X̄ = Sample mean (average)
• n = Number of data points in the sample

Population Standard Deviation Equation

Where:

• σ = Population standard deviation
• Σ = The sum of…
• X = Each data point in the population
• μ = Population mean (average)
• n = Number of data points in the population

Essentially: When you're calculating the standard deviation of a population you divide by n and when it's a sample you divide by n-1.

Google Sheets has functions for both of these equations.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

Question 2: Handling Non-Numeric Data

Standard deviation functions only work with numbers.

If there is non-numeric data in your set, you need to tell Google Sheets what to do with it.

These non-numeric values (e.g. text or Boolean data) can be:

1. Ignored (treated as a blank cell)
2. Included in the calculation as zero (0)

From the example above with the teacher, students who weren't at school when the test took place are included in the data set with "Absent" listed as their result.

The teacher would want this text to be ignored, otherwise the standard deviation would increase significantly because a number of students would register a score of zero when in reality they haven't taken the test yet.

How To Calculate Standard Deviation In Google Sheets

Now that you've answered the questions above you can use this table to determine which function you should use:

All of the formulas work the same:

• value1 = value, cell reference, or range reference
• [value2, ...] = [optional] additional arguments (as many as required)

Simply provide your data as arguments and get the relevant standard deviation returned.

Example Standard Deviation Calculation In Google Sheets

Here's a the results from a class of 10 from the school example described above:

Here are the standard deviation outputs for the various formulas available:

Remember from earlier that for the standard deviation of the class itself the best option is STDEVP (18.87973634) while for the year group it's STDEV (20.0249843).

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

You might like:

COUNT Google Sheets Function [With Quiz]

The COUNT function in Google Sheets tells you how many numbers are present in a dataset, ignoring everything else. Learn how to count here.

How To Highlight Duplicates In Google Sheets

Do you need to find and highlight duplicates in Google Sheets using conditional formatting? The custom formulas you need are right here.

MINA Google Sheets Function [With Quiz]

The MINA function in Google Sheets finds the minimum! Text or numbers... doesn't matter - MINA finds the smallest / most negative.