By Indeed Editorial Team
Published October 8, 2021
Standard deviation is the measurement of variation in a dataset. This can be a very useful statistic for understanding how similar or dissimilar your data is. Microsoft Excel can calculate standard deviation in seconds, as long as you know which formulas and commands to use. In this article, we explore the uses of standard deviation, explain how to use Excel to calculate standard deviation and provide an example.
Related: How To Include Excel Skills on Your Resume
Why is standard deviation useful in spreadsheets?
Standard deviation can provide useful insight into the concentration of the data in a spreadsheet. Specifically, it shows whether numbers in a dataset are clustered together or spread out. A low standard deviation suggests that the data points are all closer to the average, while a high standard deviation suggests that the data points are spread over a wider range. You can calculate standard deviation by taking the square root of the variance for a dataset. This might be a time-consuming process to perform manually, but by using the formula function in Microsoft Excel, you can perform it quickly and efficiently.
If your dataset already exists in an Excel spreadsheet, you can link it to the function formula to calculate for standard deviation without re-entering the data. If you change a data point in the spreadsheet, the function formula will automatically update to reflect the correct standard deviation for the new data. This is a good way to streamline the data analysis process and keep track of data and statistical analysis in spreadsheets.
Read more: What Is Standard Deviation? How It Works and Other FAQs
Uses for standard deviation
Many types of professionals use standard deviation to better understand the variance in particular datasets. For example, a marketer might calculate the standard deviation for money earned on an advertisement to better predict the range of revenues they might experience in the future. Weather forecasters also commonly use standard deviation to explain the margin of error that the public can expect in their forecasts.
Professionals working in finance also often use standard deviation to analyze and manage investments and portfolios. Calculating standard deviation can reveal the volatility or stability of a market or investment opportunity. This information may help these professionals make better educated financial predictions.
Other professionals who commonly use standard deviation include:
Statisticians
Market researchers
Insurance analysts
College professors
Human resource managers
Real estate agents
Related: How To Calculate Standard Deviation: What It Is and How To Use It
How to calculate standard deviation in Excel
To calculate standard deviation in Excel, follow these steps:
1. Choose your formula in Excel
If you type =STDEV into a blank cell in an Excel spreadsheet, six versions of the standard deviation formula appear. They are:
STDEV.S: This formula calculates the sample standard deviation based on numeric information alone. It ignores text and logical (TRUE or FALSE) values in the spreadsheet.
STDEV.P: This formula calculates the standard deviation for an entire population based on numeric information alone. It ignores text and logical values in the spreadsheet.
STDEVA: This formula calculates the sample standard deviation of a dataset and includes text and logical values in the calculation. All FALSE valses are represented by 0, and TRUE values are represented by 1.
STDEVPA: This formula calculates the standard deviation for an entire population and includes text and logical values in the calculation. Like STDEVA, all FALSE valses are represented by 0, and TRUE values are represented by 1.
STDEV: This is an older version of the STDEV.S formula that Excel used to calculate sample standard deviation before 2007. It still exists for compatibility purposes.
STDEVP: This is an older version of the STDEV.P formula that still exists for compatibility.
Most users can use STDEV.S to accomplish standard deviation calculations. If you need to calculate standard deviation for an entire population or want to include TRUE or FALSE values in your analysis, choose the standard deviation formula that most closely matches your needs.
Related: Basic Excel Formulas and How To Use Them
2. Input the dataset
The syntax of the Excel standard deviation formula using STDEV.S looks like this:
=STDEV.S([Number1]:[Number2])
Here's how you can fill in "Number1" and "Number2":
Number1: For this input, select the cell containing the first data point from the sample population in your spreadsheet.
Number2: For this input, select the cell containing the last data point from the sample population in your spreadsheet.
You may use commas to separate input values instead of a colon if you prefer to list individual data points manually rather than providing Excel with the beginning and end cell markers for the dataset. You may also use the STDEV.S formula to calculate standard deviation by inputting your values directly into the syntax and separating them with commas, rather than inputting linked cells. For example, to find the standard deviation for the data set "1, 2, 3, 4" without referring to a spreadsheet, enter the syntax "=STDEV.S(1,2,3,4)" into Excel.
3. Calculate
Once you have selected your inputs, press Enter or click away from the cell. Excel will automatically calculate the standard deviation for the inputted data. If you need to edit the formula or inputs at any time, double click on the cell to reveal your syntax. Clicking on a cell containing any formula will also cause that syntax to appear in the formula bar at the top of the page where you can make adjustments.
Related: How To Calculate Mean in Excel and Why It's Important
Example
A college professor is reviewing a sample of 10 scores from the last exam. The average score for the exam is 80%. They decide to calculate standard deviation to better understand how their students are doing. A low standard deviation would show that most of the scores are close to the average. This would suggest that the students have an adequate understanding of the material overall. If the standard deviation is high, that would mean that there is a wide range of scores. This would suggest that some students understand the material well, while others may need extra help.
The professor enters the 10 sample scores in a Microsoft Excel spreadsheet as follows: A1:69, A2:80, A3:91, A4:82, A5:78, A6:85, A7:81, A8:75, A9:80, A10:79. They then select a blank cell underneath and enter the formula =STDEV.S(A1:A10). Alternatively, the professor could bypass the spreadsheet altogether and enter the scores directly into the formula with the syntax =STDEV.S(69,80,91,82,78,85,81,75,80,79). The standard deviation that Excel calculates is 5.79. This is a relatively low standard deviation, suggesting that the students have a similar understanding of the material to each other and that the average score of 80% reflects the understanding of the class well.
Please note that none of the organizations mentioned in this article are affiliated with Indeed.