Standard Deviation in Excel and Sheets

Introduction

If you have been working with data, I’m sure you use Microsoft Excel or Google Sheets on a daily basis. These tools make data storage and organization so easy, that they’ve become indispensable for data analysts, finance professionals, and even students. The best part of using these programs is the built-in functions they have, which save a lot of time and manual work. It’s all about knowing what to use — and when. This article will teach you how to use one such important formula in Excel and Google Sheets –  STDEV – to calculate the standard deviation of a dataset.

Overview

• Learn what standard deviation is and where to use it.
• Learn how to calculate the standard deviation of a dataset mathematically.
• Understand the uses and differences between the 6 standard deviation formulas in Excel and Google Sheets.
• Learn how to calculate the standard deviation of a dataset in Excel and Google Sheets.
• Learn how to add standard deviation bars in Excel.
• Understand the difference between standard deviation and standard error of the mean.

What is Standard Deviation?

In statistics, standard deviation is defined as the measure of the dispersion of a dataset relative to its mean. In simple terms, it is a way to understand the amount of variation in a data set. It checks if all the data points are closer or further away from each other and from the mean value or average.

Mathematically, the standard deviation is calculated as the square root of the variance (the spread of numbers in a dataset).

Sample Standard Deviation Formula:

Population Standard Deviation Formula:

Here,

• x₁, x₂, x₃… x_n, denoted as x_i are individual values in the dataset
• x‾ is the mean of all values in the dataset
• n is the total number of values in the dataset

This formula determines the variation between each data point relative to the mean. It helps in comparing sets of data that may have the same mean but a different range.

For example, consider the following data sets:

• 15, 15, 15, 14, 16
• 2, 7, 14, 22, 30

Both have the same mean, 15, but the latter is clearly more spread out. If the data points are further from the mean, the deviation within the dataset is higher. In other words, the more spread out the data, the higher the standard deviation.

Using Standard Deviation

One of the most extensive uses of standard deviation is in finance, where it helps to find the annual return rate of an investment. The greater the variance between each price and the mean, the higher the standard deviation. This reveals a larger price range. More volatile stocks have a higher standard deviation, while reliable blue-chip stocks have a low standard deviation.

Standard deviation is hence used to strategize investing and trading because it helps measure market volatility. Analysts, portfolio managers, and advisors use standard deviation as a fundamental measure of risk. Investment firms will report the standard deviation of their mutual funds, which is easy for clients and investors to understand.

Standard Deviation Formulas in Excel

Using Excel or Sheets to calculate and display the standard deviation can be extremely valuable in terms of time management. It makes the calculations more manageable. Before we move onto a practical example, it is important to understand the six standard deviation formulas in Excel. These are:

For Sample Standard Deviation:

• STDEV.S: For numerical data, ignoring text and logical values.
• STDEVA: Includes text and logical values in the calculation along with numbers. Text and “FALSE” are read as 0, and TRUE is understood as 1.
• STDEV: Compatible with older versions of Excel (2007 or prior) but completes the same function as STDEV.S (used in any Excel software after 2007).

For Population Standard Deviation:

• STDEV.P: For calculating the standard deviation of an entire population, available in Excel 2010 and later.
• STDEVP: The older function for population standard deviation, replaced by STDEV.P in newer versions but still available for backward compatibility.
• STDEVPA: Includes text and logical values in the calculation of population standard deviation.

All these functions are also available in Google Sheets.

Want to master Excel functions? Here’s a full course on Excel that will teach you everything in just 2 hours!

Using the STDEV.S Function in Excel and Sheets

In this article, we will be focussing on the STDEV.S function in Excel and Sheets. This function uses numerical values and ignores text and logical values.

The syntax of the STDEV.S function in Excel is: STDEV.S(number1, [number2], …)

• Number1: The first number in the dataset. This can be a named range, single array, or a reference to an array. This is a mandatory argument.
• Number2: Additional numbers in the sample. This is an optional argument. You can add upto 254 additional values in this formula.

The same can be applied to Google Sheets.

Practical Example

Let’s consider a dataset for a community of people from a sample population. We will list out the range of their weights in column A, and heights in column B.

Now to find the standard deviation of this dataset, we will use the formula: =STDEV.S(range)

For finding the standard deviation of the weights, this would be =STDEV.S(A2:A13)

The standard deviation of the weights is calculated to be 27.68.

For finding the standard deviation of the heights, the formula would be =STDEV.S(B2:B13)

The standard deviation of the heights is calculated to be 7.44

As you can see, the standard deviation is higher for the weights as compared to the heights. This means that in this sample population, there is more variation (or a greater range) in people’s weights, than in their heights.

How to Add Standard Deviation Bars in Excel and Google Sheets

Now that we have learned how to calculate the standard deviation in Excel and Sheets, let’s see how to show it graphically. You can add standard deviation bars to a chart, to visually display this information. Here’s how to do it:

1. Create a graph in the usual way (Insert tab > Charts group).
2. Click anywhere on the graph to select it, then click the Chart Elements button.
3. Click the arrow next to Error Bars, and pick Standard Deviation.

This will insert the same error bars for all data points. This technique is useful while making reports as it’s easier to compare graphs than numerical values.

Also Read: 8 Charts You Must Know To Excel In The Art of Data Visualization!

Here’s the standard deviation bar graph created for our example.

Standard Deviation vs. Standard Error of the Mean

Standard error of the mean (SEM) is a related function to understand while discussing standard deviation. While the standard deviation measures the variability of a dataset from the mean, SEM estimates how far the sample mean is likely to be from the true population mean. Mathematically speaking, SEM is the ratio of the standard deviation to the square root of the sample size.

In Excel, you can calculate SEM using: =STDEV.S(range)/SQRT(COUNT(range))

The formula for the sample data we used in our earlier example would be:

=STDEV.S(A2:A13)/SQRT(COUNT(A2:A13)) for the weights, and

=STDEV.S(B2:B13)/SQRT(COUNT(B2:B13)) for the heights.

Conclusion

Hope this guide has helped you understand what standard deviation is and how you can calculate it in Excel and Google Sheets. Calculating standard deviation in Excel or Sheets enhances your data analysis and decision-making.

Whether you’re analyzing investment returns or measuring market volatility, this formula comes in very handy. Moreover, Excel and Sheets provide a variety of related functions to make your work easier and more efficient. Functions, such as STDEVPA, STDEVA, and STDEV.P, help you measure data variability accurately, whether for sample data or entire populations.

Mastering these functions saves time, reduces errors, and provides clear, reliable insights for personal, business, or client use. So go ahead, try out these functions, and see how easily they simplify your workload!

Frequently Asked Questions

K.C. Sabreena Basheer

Sabreena Basheer is an architect-turned-writer who's passionate about documenting anything that interests her. She's currently exploring the world of AI and Data Science as a Content Manager at Analytics Vidhya.