In Microsoft Excel, the Standard Deviation function is one that can help you quickly calculate and figure out the dispersion of your dataset. It’s a formula that will be tedious to do for every single spreadsheet column of data, so we’ve got this guide prepared for you just in case.
What is Standard Deviation?
Standard Deviation is a statistic that measures how a dataset disperses in relation to its mean. You’re actually able to achieve this by calculating the square root of the variance within a dataset.
Standard Deviation is popularly found within the finance industry. A good use case is the annual return on investment for a company. When there’s a high standard deviation, there’s a good tendency to see some variance between the price and mean.
How to Calculate Standard Deviation using a Formula in Microsoft Excel
Microsoft Excel is a pretty resourceful way to calculate standard deviation on a worksheet without dealing with all the complicated aspects of the process. To use it correctly on your worksheets, you’ll first need to be familiar with the six standard deviation formulas present in MS Excel.
Luckily, these spreadsheet formulas can be more easily accessible when divided into two separate groups:
- Calculating the standard deviation of a sample: The formulas for this category are STDEV.S, STDEVA, and STDEV
- Calculating the standard deviation for a population: The formulas here are STDEV.P, STDEVPA, and STDEVP
Most of the time, you’ll be using Standard Deviation for a sample size. Calculating an entire population set is usually not a scenario one may encounter. Instead, you can pick a sample from within a population and calculate the standard deviation from within that sample, then infer.
Using the STDEV.S and STDEV Function in your Excel Worksheet
This MS Excel function uses numerical values but ignores text and logical values. The syntax of STDEV and STDEV.S is as follows:
STDEV (number1, [number2])
Number 1: This number can be an actual number, cell references to the location of data in your worksheet or a named range. If you opt for cell references, empty cells, Boolean values, text data and error values within the range of the cell references will be ignored.
Number 2: These arguments correspond with a sample of the population. You can also choose to use a single array or a reference to an array rather than number arguments with comma separations.
STDEV.S (number1, [number2])
Number1: This is a mandatory argument within the formula. The first number here is the same as the first element of the sample. Rather than using arguments separated by commas, a single array or named range can be used instead.
Number2: This is an optional argument within your formula. It refers to a data point, single array, reference to an array or named range. You can have up to 254 additional arguments in excel.
Using it in Practice
If there’s a dataset for the range of weights, you can take a sample from the larger population to calculate its standard deviation. You begin by using the number listed within column A. The formula should appear in this format when applied =STDEV.S(A2:A10).
After getting the applied formula, Excel provides its standard deviation and average.