STDEV and its related functions allow you to determine the standard deviation of a data sample or data set, measuring the variation between those numbers against the average. To help you work out how to use standard deviation in Excel, we’ll explain how to use it in this step-by-step guide.
Phrases like standard deviation put fear into the hearts of nervous mathematicians worldwide, but the concept is pretty simple. Standard deviation takes a range of data and determines how far each value is separated from the average of the total.
Rather than work this out manually, you can use functions like STDEV, STDEV.S, and STDEV.P instead. In this guide, we’ll explore:
- What is the STDEV function and what is it used for?
- How does standard deviation work in Microsoft Excel?
- Things to consider before using standard deviation in Excel
- How to use STDEV or STDEV.S in an Excel formula: A step-by-step guide
- Using STDEVA, STDEV.P, or STDEVPA for standard deviation in Excel
If you want to understand how to calculate standard deviation in Excel, this guide will help. Let’s get started.
1. What is the STDEV function and what is it used for?
To help explain standard deviation functions like STDEV and STDEV.S in Excel, we’ll give you an example. Let’s assume that you have a group of 10 people and you’re interested in learning what the average height of the group is.
You could use the AVERAGE function to determine the average height—so far, so good. However, what if there are outliers in the data? What if you wanted to determine the variance (the typical difference between the average height and the data set)?
This is standard deviation at work, letting you see the spread of values in a data sample or data set. You can learn more about standard deviation (and how it differs from standard error) in this guide.
Using STDEV or STDEV.S in Microsoft Excel
Just like the SUBTOTAL function and other Excel functions, the STDEV function exists to serve a single purpose: to allow you to calculate standard deviation in an Excel formula. However, while STDEV is perfectly suitable for the task, Microsoft has replaced it with a newer function: STDEV.S.
There isn’t much difference between STDEV and STDEV.S, with both having the same arguments and working in the same way. With no guarantees that Excel will continue to support STDEV in newer releases, however, it’s probably best to use STDEV.S to calculate the standard deviation from a data sample.
If you’re trying to calculate standard deviation from a population data set, you’ll need to use the closely-related STDEV.P function (or the older STDEVP which, like STDEV, is no longer recommended). Population, in this context, means an entire data set, rather than a sample (section) of it.
You can also use STDEVA to work with data that contains text or logical values (like TRUE or FALSE). By default, text and logical values are ignored by STDEV.P and STDEV.S.
2. How does standard deviation work in Microsoft Excel?
As we’ve explained, standard deviation works by determining the total variance between the average of a range of numbers and the numbers themselves.
For instance, if you had a range of cells containing the same number, the standard deviation would be zero: there isn’t any variation at all. In contrast, if you had a range of cells that contained numbers that all had a variation of one (for instance, 10, 11, or 12), then the standard deviation would be one, too.
This makes the standard deviation functions (STDEV, STDEV.S, STDEVP, STDEV.P, STDEVPA and STDEVA) incredibly useful for potential data analysts, helping to determine the outlier values in a data sample or population set.
Each of these standard deviation functions work in a similar way. For instance, if you want to determine standard deviation from a data sample using STDEV.S, you’d need to use the following structure:
=STDEV.S(number1,number2, …)
All standard deviation functions (STDEV, STDEV.S, STDEVP, STDEV.P, STDEVPA and STDEVA) in Excel must contain at least one argument: number1. In this example formula, number1 refers to the cell range containing your data set. If you want to use numbers within the function directly, number1 would refer to the first number in that range.
Each additional number argument is used to add additional numbers or cell ranges to the overall standard deviation calculation. You can mix and match these, adding numbers and cell ranges.
3. Things to consider before using standard deviation in Excel
Before you use Excel to calculate standard deviation using STDEV, STDEV.S, or another related function, you’ll need to be aware of some important quirks and limitations. These include:
- STDEV has been superseded by STDEV.S, so if you’re creating new formulae, you should use STDEV.S. Likewise, STDEVP has been replaced by STDEV.P for standard deviation calculations using an entire data set (or population).
- If you’re creating a standard deviation formula using STDEV, STDEV.S, STDEV.P, STDEVPA or STDEVA, you can use numbers, cell references, and arrays for the number arguments.
- The STDEV and STDEV.S functions in Excel use Bessel’s correction (n-1) to combat estimation bias due to the smaller sample size, compared to the entire data set. For entire population sets, where this isn’t an issue, use STDEV.P, STDEVPA or STDEVA, where n (rather than n-1) is used instead.
- For Excel to consider values that contain logical values (TRUE/FALSE) or text strings, you’ll need to use the STDEVA or STDEVPA functions. All other standard deviation functions (including STDEV.S and STDEV.P) ignore these values.
- Only one number argument (number1) is required. You can add up to 255 number arguments (containing numbers or cell references) in any standard deviation formula in Excel.
4. How to use STDEV or STDEV.S in an Excel formula: A step-by-step guide
To determine the standard variation of a data sample using STDEV or STDEV.S in Excel, you’ll need to follow these steps:
Step 1: Select an empty cell
You’ll need to begin by opening the Excel spreadsheet containing your data (or a blank spreadsheet if you’re inserting your numbers into the formula manually). Start by selecting an empty cell and, once the cell is selected, press the formula bar below the ribbon bar.
Once the formula bar is active (the blinking cursor will appear), you can begin to insert your new STDEV.S function, typing =STDEV.S( with an open parenthesis. If you’re using the older STDEV function, type =STDEV( instead.
Step 2: Insert your initial number or cell reference
Adding =STDEV( or STDEV.S( into the formula bar will begin the formula creation process. A hint, showing the available formula arguments, will appear in a box directly beneath the formula bar.
Using this syntax and structure (and as explained above), begin to insert your data, adding at least one number argument to the formula. If you want to insert numbers into the formula directly, type these (one after each other), separating each value with a comma.
You can also use a cell reference for the number1 and additional number arguments. This can be done by inserting it manually (eg. B1:B5) or by selecting the cells with your mouse or trackpad.
It’s also possible to mix and match cell references and numbers. STDEV and STDEV.S will determine the standard deviation from the data set as a whole. Once you’re happy with the formula, close it with a closing parenthesis.
STDEV and STDEV.S will ignore any values that contain numbers or logical values (TRUE or FALSE). If a range of cells contain no numbers, Excel will return a #DIV/0 error message.
5. Using STDEVA, STDEV.P, or STDEVPA for standard deviation in Excel
The STDEV and STDEV.S functions are useful for sample data sets that contain standard numbers. If you’re working with data that contains text, logical values, or if you want to find the standard deviation from an entire population data set, you’ll need to use STEDVA, STDEV.P, or STDEVPA instead.
Using STDEVA or STDEVPA
If you want to create a standard deviation formula that considers logical values (TRUE or FALSE) or text strings, then you’ll need to use STDEVA or STDEVPA instead. Use STDEVA for a sample data set and STDEVPA for an entire population set (where estimation bias is much less likely).
Both STDEVA and STDEVPA work much like the STDEV and STDEV.S functions, with the same arguments and structure:
=STDEVA(number1,number2, …)
=STDEVPA(number1,number2, …)
The only difference is how Excel considers cell references or entries that contain text or logical values. For instance, if you have a cell range (A2:A9) and two of the cells contain text, the value of those cells is counted as zero, as is FALSE. For TRUE, STDEVA and STDEVPA count it as the value 1.
Using STDEV.P
Likewise, for the STDEV.P function, the only difference is the method Excel uses to determine the variance. For smaller samples, Bessel’s correction is used as the method, where the method n-1 is used (n referring to the number count). This helps to overcome bias issues in estimating the variation using a smaller sample size.
If you’re using an entire data set, then you don’t need to use this method to overcome potential estimation bias. Rather than using STDEV or STDEV.S (where S means sample), it’s better to use STDEV.P (where P means population, or the entire data set).
The arguments and structure remain the same, as follows:
=STDEV.P(number1,number2, …)
This will ignore text and logical values, so be sure to use STDEVPA instead if this is a requirement. You can also use the outdated STDEVP function, but there are no guarantees that this will remain available in future Excel releases.
Final thoughts
Standard deviation is a tricky mathematical concept made easy by functions like STDEV, STDEV.S, STDEV.P and others in Microsoft Excel. If you’re struggling, you can create a pivot table to determine the standard deviation of a data sample or set instead.
Excel has all kinds of functions, formulae, and features that can make data analysis a little easier. If you want to learn more, here are 10 essential Excel formulas for data analysts to use. You can also take our five day short course for new data analysts to get to grips with data a little easier. You might also find these Excel tutorials useful: