How to Calculate Standard Deviation in Excel

Standard deviation tells us how spread out the numbers are in a given dataset. Several important statistical concepts and methods, such as variance, coefficient of variation, and confidence intervals, depend on standard deviation as a foundational measure of variability, thus making it one of the most important tools in statistics for data professionals to master.

This article will introduce you to standard deviation, guide you through calculating standard deviation in Excel, explain the differences between the available built-in functions, and demonstrate how to interpret the results to make informed decisions based on your analysis. If, as you read this article, you discover that you are interested in statistics more generally, take our Introduction to Statistics course, which covers many foundational topics with no coding required.

The Quick Answer: How to Calculate Standard Deviation in Excel

To calculate standard deviation in Excel, enter your data into a column, then use the STDEV.P() function for a population or the STDEV.S() function for a sample. The main difference between these functions is that STDEV.P() divides by the total number of data points while STDEV.S() divides by the number of data points minus one (n-1) to account for sample variability.

Quick calculation of standard deviation in Excel. Image by Author.

For example, if your data is in cells B2 to B31, type =STDEV.P(B2:B31) to calculate the population standard deviation or =STDEV.S(B2:B31) for the sample standard deviation. Excel will automatically compute and display the standard deviation for your dataset.

What is Standard Deviation?

Standard deviation is a statistical measure that quantifies the amount of variation or dispersion in a set of numerical values. It indicates how much the values in a dataset deviate from the mean (average) of the dataset. 

It helps to understand the extent of variability within data—a low standard deviation indicates that the values are close to the mean, suggesting consistency, while a high standard deviation indicates greater variability, suggesting more spread-out data.

Formulas for standard deviation

The formula for standard deviation depends on whether we are dealing with a population or a sample. The formula for population standard deviation (σ) is as follows:

Population standard deviation. Image by Author.

Where:

• N is the total number of data points in the population.

• x_i​ represents each individual data point.

• μ is the mean (average) of the population.

This formula calculates the average of the squared deviations from the mean and then takes the square root of this average.

The formula for sample standard deviation (s) is as follows. Note that for sample standard deviation, we use a lowercase n for convention.

Sample standard deviation equation. Image by Author.

Where:

• n is the total number of data points in the sample.

• x_i​ represents each individual data point.

• xˉ is the mean (average) of the sample.

The formula is similar to that of the population standard deviation. Still, it uses n - 1 (Bessel’s correction) in the denominator to account for the fact that a sample is being used to estimate the population parameter. This correction makes the sample standard deviation an unbiased estimator of the population standard deviation.​​

Practical applications of standard deviation

Standard deviation is a commonly used technique across fields. It provides valuable insights into data variability and consistency and can be applied across domains, including:

• Risk Assessment: Standard deviation can measure the volatility of asset returns. A higher standard deviation indicates greater risk and potential return variability. Investors use standard deviation to compare the risk levels of different investments and construct diversified portfolios that balance risk and return.
• Quality Control: Manufacturing and production industries use standard deviation to monitor and control the quality of products and processes. By measuring the variation in product dimensions, weight, or other attributes, companies can ensure consistency and adherence to quality standards.
• Healthcare and Medicine: Standard deviation is used in various statistical analyses to understand the spread of data such as patient blood pressure, cholesterol levels, and other health indicators. It helps identify outliers and understand the normal range of health metrics.
• Education & Research: Standard deviation helps determine the distribution of test scores and sets appropriate grading curves. Researchers use standard deviation to study the effectiveness of educational interventions and compare performance across different groups of students.
• Weather and Climate Studies: Meteorologists use standard deviation to analyze weather data, such as temperature, precipitation, and wind speed. This helps them understand climate variability and predict weather patterns.

These examples can continue since standard deviation is fundamental in statistics and data analytics and can be extended across domains beyond the examples above.

Types of Standard Deviation

Like the definitions, Microsoft Excel has two different functions to calculate standard deviation, depending on whether you are working with an entire population or a sample. 

STDEV.P() for population standard deviation

The STDEV.P() function in Excel calculates the standard deviation for an entire population. This function assumes that the provided arguments include the entire population. The syntax for the STDEV.P() function is as follows:

STDEV.P(number1, [number2], ...)

The parameters are:

• number1: The first number argument corresponds to a population.

• [number2], ...: Optional additional numbers or references that correspond to a population, up to 254 arguments.

STDEV.P() calculates the standard deviation using the population standard deviation formula we learned earlier. This ensures that the calculation reflects the characteristics of the entire population.

We should use STDEV.P() when we have data representing the entire population. For example, when analyzing the test scores of all students in a specific school, STDEV.P() is appropriate because the dataset includes the whole population of interest.

STDEV.S() for sample standard deviation

The STDEV.S() function in Excel calculates the standard deviation for a sample of the population. This function assumes that the provided arguments are a sample of the population, not the entire population. The syntax for the STDEV.S() function is as follows:

STDEV.S(number1, [number2], ...)

The parameters are:

• number1: The first number argument corresponds to a sample of the population.

• [number2], ...: Optional additional numbers or references that correspond to a sample, up to 254 arguments.

STDEV.S() calculates the standard deviation by using the formula for the sample standard deviation, accounting for Bessel's correction, as we learned earlier.

In practice, when we have a sample of data, we use STDEV.S() to estimate the standard deviation of the entire population. Suppose we analyze the test scores of a subset of students randomly selected from a school. STDEV.S() is appropriate because our dataset is only a sample of the total student population.

Now that we’ve learned about the functions available in Excel for calculating standard deviation let’s put all our knowledge into practice by using an example.

Calculating Standard Deviation in Excel 

Imagine a scenario where, as a data analyst, you’re required to analyze the sales performance of a small retail store. The store owner wants to understand the variability in daily sales over the past month to better manage inventory and staffing levels. 

You have collected the daily sales data for 30 days and must calculate the standard deviation to quantify this variability. 

Let’s start by tabulating the records in a column, let’s say B2 to B31, in an Excel spreadsheet.

Stores sales dataset. Image by Author.

We can calculate the population standard deviation in the cell E6 by typing the following equation:

=STDEV.P(B2:B31)

You will see the calculated population standard deviation in cell E6 as follows:

Calculating population standard deviation. Image by Author.

We used the STDEV.P() function because, in our scenario, our entire population for the analysis is the sales data for the last month.

Suppose we’re performing an analysis for an entire year using the last 30 days' data as a sample; then we’ll be using the STDEV.S() function as shown using the equation below:

=STDEV.S(B2:B31)

And we’ll be seeing the calculated sample standard deviation below:

Calculating sample standard deviation. Image by Author.

After calculating the standard deviation, the next step is interpreting the result in a business context. 

Interpreting Standard Deviation Values

For a better interpretation of standard deviation values and to supplement our analysis, we can calculate the mean value of our sales data using the equation below:

=AVERAGE(B2:B31)

The mean value can be calculated as $975 on cell E7, as seen below:

Calculating the average of the dataset. Image by Author.

Along with the average value obtained, we can interpret the standard deviation values as:

• Population Standard Deviation: The population standard deviation of approximately 432.77 indicates that, on average, daily sales vary by about $432.77 from the mean sales value of $975. This figure represents the spread of sales data, as we considered the entire month a complete dataset.
• Sample Standard Deviation: The sample standard deviation of approximately 440.17 is slightly higher than the population standard deviation. This value is relevant if we’re treating the 30 days of data as a sample from a larger population (e.g., all possible future months.)
• Higher Sample Standard Deviation: The slightly higher value in sample standard deviation accounts for the uncertainty in using a sample to estimate the overall variability in sales. It suggests that if we sample another month, we might expect a similar variability in daily sales.
• High Variability in Sales: Both standard deviation values indicate high variability in daily sales, meaning that sales figures tend to fluctuate significantly from the average of $975. This level of variability could impact inventory management and staffing. On some days, sales could be much higher or lower than average, leading to overstocking or understocking if not carefully managed.

Since the standard deviation values suggest that daily sales are inconsistent and vary widely, further analysis or a machine learning model can be built to estimate the sales on a particular day.

The store owner should consider this variability when planning inventory and staffing to avoid potential issues related to overstocking or understaffing.

How to Add Standard Deviation Bars In Excel

Adding standard deviation bars to our charts in Excel is a useful way to visually represent our data's variability. 

Select the data range, B2 to B31, from our example and go to the Insert tab. Choose your desired chart type as shown in the image below (e.g., line chart) and insert the chart.

Inserting a chart for the store sales data. Image by Author.

You’ll see a simple line chart below:

Line chart for store sales data. Image by Author.

Click on the data series in your chart to highlight it. This step is necessary to add error bars to the selected data series. Go to the Chart Design tab that appears when you select the chart. Click on Add Chart Element (found in the Chart Layouts group).

Next, hover over Error Bars and select Standard Deviation to add the standard deviation bars to the line chart.

Adding standard deviation bars to the line chart. Image by Author.

You may also hover Error Bars and then click on More Error Bars Options to further format the error bars and adjust the color, width, and line style to match your chart’s design preferences. This can be done in the same panel under the Format Error Bars options. Feel free to play with the chart until you get the plot you’d like to visualize.

Formatting the standard deviation bars. Image by Author.

Adding standard deviation bars to our charts in Excel provides a clear visual representation of the variability in the data, which helps in effective data analysis and decision-making.

Conclusion

This article introduced you to standard deviation and its practical applications. It laid out steps to calculate standard deviation in Excel. We also learned how to interpret the standard deviation values and add standard deviation bars to a line chart.

Standard deviation is a fundamental and valuable tool that helps us understand a dataset’s variability. Further, it’s used in several other analyses, such as coefficient of variation, confidence intervals, and ANOVA, making it a must-learn technique for all data professionals.

We encourage you to check out our comprehensive hands-on Excel Fundamentals skill track to further expand your Excel and data analytical skills.