Calculating Standard Deviation (STD) Using Microsoft ExcelSimple Guide

Written By:
Adiste Mae

Microsoft Excel is well-celebrated for making our work easier in data inventory and management.

Its usefulness is not confined to basic Excel rows and column organization only, as many people think.

But when it gets right down to its functionalities, it can help users save a lot of time by making time-consuming work simple and easy. 

The formulas used in Excel will be able to save you a considerable amount of time as well as manual work.

What formula to use and when to use it is vital to remember.

If your work with Excel spreadsheets is taking you hours, it is likely that using formulas will speed things up.

Standard Deviation (STD) Defined

In statistics, it is the measurement of how a particular dataset is dispersed in relation to its mean.

It is measured as the variance’s square root, (variance is the quality of it being different or in Excel the way the numbers are spread throughout a dataset). 

Finding the differences between data points in relation to their average is important for comparing datasets that may show the same average or mean, yet different ranges.

For instance, we can use the set of data below. Means are no different, but the latter obviously has values that are more scattered.

  • 22, 22, 22, 21, 23
  • 1, 12, 22, 28, 35

If the values are more spread out from the average or mean, the deviation in the dataset may result to be higher.

If the values of the samples are more dispersed, a higher STD can be expected.

In finance, one of the often-used statistical measures is STD.

It is usually used for the yearly rate computation of ROI (Return on Investment).

High STD correlates to greater variance between every price and its mean, which reveals a larger range of prices.

High STD is observable in volatile stocks, while blue-chip stocks (huge companies with more stable finances and excellent reputations) on the other hand, have a low STD.

Standard Deviation (STD) in use

Strategized investment and trading use STD to help them with market volatility measurement.

STD is being used by analysts, advisors, and portfolio managers as a measure of risk. Even investment firms use STDs in their mutual fund report.

Statistics can be tricky to understand sometimes.  For that same reason, it is useful to share with clients as well as investors this information through the use of Excel to compute and show that the  STD can be of great value in terms of managing time.

Calculating Standard Deviation (STD) in Excel

Microsoft Excel makes computations of STDs a lot more manageable and presentable.

But before anything else, it is important to consider the six STD formulas to use in Excel.

  1. In calculating the sample or specimen standard deviation, you must use these formulas: STDEV.S, STDEV & STDEVA.
  2. In calculating the standard deviation of a population you must use the formulas as follows: STDEV.P, STDEVP & STDEVPA.

The use of the terminology population signifies that all datasets in a population are considered. In cases where the population is impossible or unrealistic, you can use any sample from a population (sample STD) and it will work. Usually, the STD can be found with the use of data samples to get the STD of a represented population.

As opposed to using the entire population, it is a more conventional practice to use the sample of data, and here are three formulas you can use:

  • STDEV.S. Use this formula if data happens to be numeric since it will automatically ignore texts and empirical values
  • STEDVA. Use this formula when texts and empirical values are added to the calculation together with numbers. For example, “FALSE” is read as zero (0), and “TRUE” as one (1).
  • STDEV. The old versions of Excel (e.g., 2007 and former) are compatible with STDEV. Yet, STDEV performs the same functions no different than STDEV.S (used in versions of Excel software following the 2007 version).

STDEV.S Function

To put emphasis on it, STDEV.S only recognizes numerical values while ignoring texts and empirical values.

The STDEV.S function’s syntax in Microsoft Excel:

STDEV.S (number1,[number 2],…).

  • Number1. The number is an imperative argument and cannot be removed from the formula. This number represents the first sample element. A provided range, a single array, and/or reference to a range can be of use here instead of arguments separated by commas.
  • Number 2. Unlike the first, this can be an optional argument. This can denote a specific data point, an appointed range, a single range or array, and/or reference to a range that can reach to 254 arguments.

In practice

Let us say that there is a set of data for a certain weight range, from a sample group or population.

Using the same values, when applied, the formula looks like this: STDEV.S(A2:A10).

As a substitute, Excel provides the STD of the input data together with the average.

For example, if the average weight of Filipino women is 55 (kg), and their standard deviation (STD) is 3 (kg), it would only mean that the majority of the Filipina women in the sample group or population have an average weight of 55 kg with a range of 55-2 or 55+2 (55∓).

Take Note: Any formula input to a cell will appear as well in the Formula Bar of Excel. It is important to remember to always put the equal sign if placing a formula.