How to Calculate Standard Deviation in Excel
How to Find Standard Deviation in Excel – Quick Steps
1. Open Excel Sheet
2. Enter the data
3. Apply Formula
4. Save
The Standard Deviation is a statistical measure that helps us to understand the amount of variance in a dataset. This statistic can be quite helpful in determining how similar or distinct your data is. Standard deviation may be calculated in Microsoft Excel quickly if you know the formulas and commands to use.
In this post, we examine the functions of standard deviation, clarify how to compute standard deviation in Excel and give an illustration.
What is Standard Deviation?
A statistic called standard deviation assesses a dataset’s dispersion in relation to its mean. It is determined as the variance’s square root (the spread of numbers in a dataset). When comparing data sets that may have the same mean but a distinct range, knowing the variation between each data point relative to the mean is helpful.
In real-life scenarios, we have populations like marks of students in a particular subject, and salaries of multiple employees in a company. Let us consider, in a company named ABC, we have 5 employees.
NameÂ | Salary(USD) |
---|---|
a | 20k |
b | 50k |
c | 40k |
d | 10k |
e | 30k |
Now, it will tell us how these salaries are dispersed across this company.
Formula for Standard Deviation
The formula to calculate Standard Deviation in Excel:
Formula: \sigma = Â \sqrt{ \sum_1^n (x_{i} – Â \mu Â )^{2} Â /N}Â
It will be shown as
- Where x represents the value, and Î¼ is the mean of the population.Â
- Mean = 30
- N = 5
- Std = 14.14
Why Use Standard Deviation Formula
The standard Deviation can be very helpful in many ways. Below are are the reasons that tell us that why finding Standard Deviation in Excel can be of great help.
- Because it may be used to gauge market volatility, the standard deviation is often utilized to plan trading and investing strategies.
- Standard deviation is used by analysts, portfolio managers, and advisors as a fundamental risk indicator.
- Even the standard deviation of their mutual funds will be disclosed by investment firms.
The statistics are often simple to comprehend, which makes it useful to present them to clients and investors. Excel may be quite helpful in terms of time management by calculating and displaying the standard deviation.
How to Calculate Standard Deviation in Excel
Now, let’s learn how to use it in Excel. In Excel, we have formulas for everything and as you might have guessed for Sandard Deviation also. You can calculate standard deviation with the help of six functions in Excel. On the basis of the data whether sample or population you have to decide which function to apply.
Let’s learn about these six functions below:
Functions to calculate sample standard deviation in Excel
STDEV.S FunctionÂ
It calculates the standard deviation assuming that the sample of the population is its argument. It returns an approximate value and is used with a large population.
Syntax: =STDEV.S([Number1]:[Number2])
- Number 1: Choose the cell in your spreadsheet that holds the first data point from the sample population for this input.
- Number 2: Choose the cell in your spreadsheet that has the final data point from the sample population for this input.
STDEV.S uses numerical values and ignores text and logical values.
STDEVÂ Function
It is the original standard deviation function and is supported by newer versions for just backward compatibility. In Excel 2007 and later, STDEV can accept up to 255 arguments. It is advised to use STDEV.S in new versions.
STDEVA Function
It includes text and logical symbols, whereas the above 2 functions don’t include text and logical symbols. It is used for a sample of the population. Empty cells are ignored. It Uses the “n-1” formula.
Functions to calculate population standard deviation in Excel
STDEV.PÂ Function
It calculates the standard deviation assuming that the entire population is its argument. It returns an approximate value and is used with a large population. It uses the formula: STDEV.P(number1,[number2],â€¦)
STDEVP function
It is the old Excel function to find standard deviation of a population. Its formula is STDEVP(number1,[number2],â€¦) .
STDEVPAÂ Function
It includes text and logical symbols. It is used for the whole population. Uses the “n” formula.
Note – STDEV and STDEVP are for Excel 2007 or earlier. DSTDEV and DSTDEVP are used for fetching data from Database.
No matter which Excel standard deviation Formula you choose, it’s crucial to understand that if any argument contains error values from other functions or text that can not be interpreted as a number, the formula will return an error. Double-check your data and ensure that all values are numeric and error-free before using the standard deviation formula in Excel.
Which Excel Standard Deviation Function to Use
Choosing the right Excel Standard deviation function can be a little confusing task, especially for beginners. You can consider the following points to select the appropriate formula for your specific task :
- Are you looking to calculate the standard deviation for a sample or a population?
- Which version of Excel are you using?
- Does your dataset contain only numbers, or does it include logical values and text as well?
If you want to work with a numeric sample, use the STVE.S Function in Excel 2010 and later versions, or the STDEV Function in Excel 2007 and earlier versions.
If you want to include logic or text values in the calculations, you can use either the STDEVA Function for sample standard deviation or the STDEVPA Function for population standard deviation. While these Functions may not be commonly used on their own, they can be helpful in larger formulas where arguments are returned as logical values or text representations of numbers.
Refer to the below table to decide which Excel deviation Function suits your need:
Function | Excel Version | Sample | Population | Logical Values | Text |
---|---|---|---|---|---|
STDEV |
2003-2019 | Yes | Ignored | ||
STDEV.S |
2010-2019 | Yes | Ignored | ||
STDEVP |
2003-2019 |
| Yes | Ignored | |
STDEV.P |
2010-2019 |
| Yes | Ignored | |
STDEVA |
2003-2019 | Yes | Evaluated | Evaluated as Zero | |
STDEVPA |
2002-2019 |
| Yes | Evaluated | Evaluated as Zero |
(True =1, False=0) | |||||
Empty cells | Ignored |
Excel Standard Deviation Formula Examples
Once you have chosen the Function for your data type, writing the formula is straightforward and error-free. The syntax is simple and transparent, making it easy to apply Excel’s standard deviation formulas. Let’s discuss a few examples:
Calculating Standard Deviation of a Sample and Population
When calculating the standard deviation in Excel, you have different formulas to use depending on whether you are working with a sample or the entire population:
For population standard deviation, which applies to the entire list of values, you can use the STEDV.P Function.
For example: If your data is in the range A3:A30, the formula would be:
=STDEV.S(A2:A14)
It is mandatory to notice the results from these formulas may slightly differ, mainly while working with smaller sample sizes.
If you are using Excel 2007 or earlier versions, you would use the STDEVP and STDEV functions
Example of How to Calculate Standard Deviation in Excel
Step 1: Open Excel Sheet
Open Excel Sheet with the Data
Step 2: Enter Formula
Apply the Formula in the formula bar. Here we are applying STDEV.S formula. Enter the first and last argument
Step 3: Press Enter and Check Result
You can see the output after pressing the enter key on your keyboard.
How to Add Standard Deviation Bars in Excel
If you want to visually represent the margin of errors with standard deviation, you can easily add standard deviation bars to your Excel chart.
Follow the steps to add standard deviation bars :
Step 1: Create your Chart
Create your chart by selecting the data and choosing the appropriate chart type from the Insert tab in the Charts group.
Step 2: Select the Chart and Chart Element button
After creating the chart, click anywhere on the chart to activate the chart Elements and a dropdown menu, click the arrow next to” Error bars” to explore the options. Click on the chart element button, to open a drop drop list.
Step 3: Click on the arrow next to “Error Bars”.
In the drop-down list, click on the arrow next to Error bars, to expand the options.
Step 4: Select the Standard Deviation
From the options, select “Standard deviation“
You can now see the standard deviation bars for all the data points in your chart.
The Difference between Standard Deviation and Standard Error of the Mean
When it comes to estimating the variability in data, there is another statistical measure called the standard error of the mean” also known as “standard error”.
The standard deviation measures the dispersion or variability of a dataset around the mean. It tells you how spread out the individual data points are from the average. On the other hand, the standard error of the mean provides an estimate of how much the sample mean is expected to deviate from the true population mean. In simpler words, if we were to take multiple samples from the same population, the standard error of the mean would show us the range of variability between those sample means.
Since we calculate a single mean from a dataset rather than multiple means, the standard error of the mean is an estimation rather than a direct measurement. It is worth noting that the standard deviation is always larger than the standard error of the mean.
To calculate the standard error of the mean in your Excel worksheets, you can use STDEV.S Function in combination with COUNT and SORT. The below formula can be used:
=STDEV.S(range)/SQRT(COUNT(range))
For example, if your sample data is located in cells A2 to A14 you can use the following formula to calculate the SEM:
=STDEV.S(A2:A14)/SQRT(COUNT(A2:A14))
The result will provide an estimation of the standard error of the mean.
Conclusions
In the above article, we discussed about calculating standard deviation of both sample data and population. This can be done using different functions. All functions have their own usage. Further we discussed this by taking an example and learning the procedure in detail. We also learned about standard error bars and standard error of mean.
FAQs
What is Standard Deviation?
A statistic called standard deviation assesses a dataset’s dispersion in relation to its mean. It is determined as the variance’s square root (the spread of numbers in a dataset). When comparing data sets that may have the same mean but a distinct range, knowing the variation between each data point relative to the mean is helpful.
How we can calculate Standard Deviation in Excel?
By using STDEV Function you can calculate the standard deviation. You can use the below formula:
=STDEV(range), where “range” represents the range of cells containing the dataset.
Can the Standard deviation be negative?
No, the Standard deviation cannot be negative. It is always a non- negative value, as it represents the measure of dispersion.
What is the difference between standard deviation and variance?
Standard Deviation and variance are related but distinct measures. Variance measures the average squared deviation from the mean and standard deviation is the square root of variance. Standard deviation is often preferred as it is in the same unit as the original data and provides a more intuitive measure of variability.
How we can use STDEV.P or STDEV.S?
- The STDEV. P function is used to determine the Standard Deviation for the entire population
- The STDEV.S function is used to determine the Standard Deviation of a sample population