Standard deviation in Excel and sheets

Introduction

If you've been working with data, I'm sure you use Microsoft Excel or Google Sheets on a daily basis. These tools make storing and organizing data so easy that they have become indispensable for data analysts, finance professionals, and even students. The best part of using these programs is the built-in features they have, which save a lot of time and manual work. It's about knowing what to use and when. This article will teach you how to use one of those important formulas in Excel and Google Sheets (STDEV) to calculate the standard deviation of a data set.

General description

• Learn what standard deviation is and where to use it.
• Learn how to mathematically calculate the standard deviation of a data set.
• 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 data set 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 set of data relative to its mean. In simple terms, it is a way of understanding the amount of variation in a data set. Checks whether all data points are closer or farther from each other and from the mean or average value.

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

Standard deviation formula example:

Population standard deviation formula:

Here,

• x₁x₂x₃…x_northdenoted as x_Yo are individual values ​​in the data set
• x‾ is the mean of all values ​​in the data set
• n is the total number of values ​​in the data set

This formula determines the variation between each data point relative to the mean. It helps compare data sets 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 average, 15, but the latter is clearly more distributed. If the data points are further from the mean, the deviation within the data set is greater. In other words, the more spread out the data is, the larger the standard deviation.

Using the standard deviation

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

Therefore, standard deviation is used to design investment and trading strategies because it helps measure market volatility. Analysts, portfolio managers and advisors use standard deviation as a fundamental measure of risk. Investment companies 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 Spreadsheets to calculate and display standard deviation can be extremely valuable in terms of time management. Makes calculations more manageable. Before moving on to a practical example, it is important to understand the six standard deviation formulas in Excel. These are:

For the sample standard deviation:

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

For the population standard deviation:

• UNDRESTS.P: To calculate the standard deviation of an entire population, available in Excel 2010 and later.
• STDEVP: The former function for population standard deviation, replaced by STDEV.P in newer versions, but still available for backward compatibility.
• DISVESTPA: Includes text and logical values ​​in the calculation of population standard deviation.

All of these features are also available in Google Sheets.

Do you want to master Excel functions? Here is a complete course in excel That will teach you everything in just 2 hours!

Using the STDEV.S Function in Excel and Sheets

In this article, we will focus on the STDEV.S function in Excel and Sheets. This function uses numeric values ​​and ignores text and logical values.

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

• Number 1– The first number in the data set. It can be a named range, a single array, or a reference to an array. This is a required argument.
• Number 2: Additional numbers in the sample. This is an optional argument. You can add up to 254 additional values ​​in this formula.

The same can be applied to Google Sheets.

Practical example

Let's consider a data set for a community of people from a sample population. We will list the range of their weights in column A and the heights in column B.

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

To find 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.

To find 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 larger for weights than for heights. This means that in this sample population, there is more variation (or a greater range) in people's weight than in their height.

How to Add Standard Deviation Bars in Excel and Google Sheets

Now that we've learned how to calculate standard deviation in Excel and Sheets, let's see how to display 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 chart as usual (Insert tab > Charts group).
2. Click anywhere on the chart to select it, and then click the Chart Elements button.
3. Click the arrow next to Error Bars and select Standard Deviation.

This will insert the same error bars for all data points. This technique is useful when reporting because it is easier to compare graphs than numerical values.

Also Read: 8 Charts You Should Know to Excel in the Art of Data Visualization!

Here is the standard deviation bar chart created for our example.

Standard deviation versus standard error of the mean

The standard error of the mean (SEM) is a related function that must be understood when analyzing standard deviation. While standard deviation measures the variability of a data set with respect to the mean, SEM estimates how far the sample mean is likely to be from the true population mean. Mathematically speaking, SEM is the ratio between the standard deviation and 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 that we used in our previous 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

I hope this guide helped you understand what standard deviation is and how to calculate it in Excel and Google Sheets. Calculating standard deviation in Excel or Spreadsheets improves data analysis and decision making.

Whether you are analyzing investment returns or measuring market volatility, this formula comes in handy. Additionally, Excel and Sheets provide a variety of related features to make your work easier and more efficient. Features like STDEVPA, STDEV, and STDEV.P help you measure data variability accurately, whether for sample data or entire populations.

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

Frequent questions

P1. How do you calculate standard deviation in Excel?

A. You can calculate the standard deviation in Microsoft Excel and Google Sheets using the STDEV.S function. The syntax for it is STDEV.S(number1, (number2), …) either STDEV.S(range).

P2. Should I use STDEV or STDEV p in Excel?

TO. STDEV.S in Excel is used to calculate the sample standard deviation, while STDEV.P It is used to calculate the standard deviation of an entire population.

P3. How to calculate SE in Excel?

A. The standard error (SE) or standard error of the mean (SEM) is calculated in Excel using the formula: =STDEV.S(range)/SQRT(COUNT(range)).