How to Calculate Standard Deviation in Excel

Standard deviation is a key statistical measure that helps you understand how spread out the values in a dataset are. In simpler terms, it shows how much the numbers differ from the average (mean). Whether you are working in education, finance, or research, understanding standard deviation allows you to analyze trends and make informed decisions. This article explains how to calculate standard deviation in Excel for both sample and population data, with examples and practical tips.

What is Standard Deviation?

Standard Deviation is a descriptive statistic that measures the dispersion or spread of values in a dataset. It indicates how much the data points deviate from the mean (average) value. A higher standard deviation indicates that data points are more spread out from the mean.

Standard deviation can be calculated for either a sample or an entire population. A population includes all the data points of the case you are studying, whereas a sample is a subset of that population. There is a slight variation in the formula to calculate the standard deviation. The sample standard deviation formula divides by n – 1, whereas the population formula divides by n. This is done to provide an unbiased estimate of population variability.

Now that you understand what standard deviation measures, let’s explore the different Excel formulas you can use to calculate it.

Types of Standard Deviation Formulas in Excel

Excel has different formulas to calculate standard deviation depending on whether your data is sample data or the entire population. Choosing the appropriate function is important for obtaining accurate results.

Calculating Sample Standard Deviation

When working with a sample, there are several formulas provided by Excel. These include STDEV.S, STDEVA, and STDEV.

1. STDEV.S

This formula calculates the standard deviation using only the numeric values present in the data. STDEV.S ignores text, blank cells, and logical values contained in referenced ranges. Use STDEVA when logical values and text need to be evaluated.

2. STDEVA

This formula calculates the standard deviation for a sample, including the logical value and text entries representing numbers. It considers TRUE as 1 and FALSE as 0 and then calculates the standard deviation.

3. STDEV

This is a legacy formula, used in older versions to calculate the standard deviation of a sample. STDEV is available for backwards compatibility, but is not recommended in newer Excel versions. Use STDEV.S instead.

Calculating Population Standard Deviation

When working with the entire population, Excel provides two major functions to calculate the standard deviation: STDEV.P and STDEVPA.

1. STDEV.P

STDEV.P calculates the standard deviation of a population using numeric values in the referenced range. Text and logical values in referenced ranges are ignored

2. STDEVPA

This formula calculates the standard deviation of the entire population, including the logical values and text representations of the numbers. Here, too, TRUE is treated as 1 and FALSE as 0.

Examples for Calculating Standard Deviation in Excel

Let us learn and understand how to apply each of these formulas with the help of unique examples. The examples below apply to Microsoft 365, Excel 2024, and Excel 2021.

Example 1: Sample Standard Deviation

A sample dataset is used when the size of the population is so large that collecting the data from individuals is impractical. This is because the task becomes tedious, time-consuming, and prone to errors. In these situations, we make informed predictions or inferences about the entire population based on the calculations of the samples.

Example:

Consider a situation where an EdTech organization wants to calculate the standard deviation of quiz scores from a nationwide assessment conducted across India. Since the quiz was taken by students from thousands of schools across India, collecting and analyzing every student’s score would be time-consuming and difficult to manage. Instead, we can select a random sample of students from each participating school and use the sample to make inferences about the population. If schools differ substantially, a stratified random sample can provide better representation.

Formula Used: =STDEV.S(B2:B12)

Output:

Explanation: Using the formula, the standard deviation is approximately 16.5304.

Example 2: Population Standard Deviation

A population dataset contains every observation in the group you want to study. The population can be large or small. When you have data for every member of the population, use STDEV.P to calculate the population standard deviation. For example, a factory may need to calculate the standard deviation of the production times for all the units it produces in a day. This will help the executives to understand the efficiency of the factory’s production unit. They must include data from even a single unit, or else they may come to the wrong conclusion about the performance of the production unit.

Example Scenario:

Consider a scenario where an EdTech organization needs to assess the performance ratings of all its instructors for compliance reporting or internal audits. They will consider all the ratings to determine an accurate rating of the instructor and assess whether to continue the contract or terminate it.

Note: Here, for simplicity, we are using a small dataset with just a few ratings as opposed to population data containing hundreds or thousands of entries.

Formula Used: =STDEV.P(B2:F4)

Output:

Explanation: Here, we input the whole matrix of values from the range B2:F4 in the formula. The calculated result is approximately 0.196.

Example 3: Handling Logical Values

When you are dealing with data about real-life scenarios, it might have columns with logical values (TRUE and FALSE), like the purchase column in real estate datasets. STDEVA evaluates TRUE as 1 and FALSE as 0 when logical values are included in the referenced data. Including TRUE/FALSE values in your data can impact the result significantly, especially in educational data where course completion or participation is often logged as Boolean values.

Example Scenario:

An EdTech organization offers a new online course, and they want to calculate the sample standard deviation of the course-completion status from students who participated in the course. The students who completed the course are marked as TRUE, and students who did not are marked as FALSE.

Formula Used: =STDEVA(B2:B7)

Output:

Explanation: In this example, we used logical values to calculate the standard deviation; the formula treated TRUE as 1 and FALSE as 0. The result was equal to 0.547722558.

Similarly, for the population dataset, you can replace the =STDEVA() with =STDEVPA().

Example 4: Population Standard Deviation with Mixed Data Types

Example Scenario:

An EdTech organization stores the number of students enrolled in different online courses the organization offers. Some entries may contain text (e.g., “Pending”) instead of numeric values. These non-numeric entries are ignored when calculating the population standard deviation with STDEV.P. STDEVPA handles text and logical values differently. In the case of STDEVA and STDEVPA, non-numeric text and FALSE are treated as 0. On the other hand, blank cells are ignored.

Output:

Explanation: We used two formulas to calculate the standard deviation. STDEV.P ignores the textual value, so it calculates the SD from three numeric values, and the standard deviation came out to be 16.32993. STDEVPA treats ‘Pending’ as 0, so it calculates the SD using four values. The value was equal to 87.74964.

How to Manage Outliers in Standard Deviation Calculations

When calculating the standard deviation in Excel, it calculates the result using all the values in the given range. Sometimes, there might exist some outliers in this range of values. Outliers are values in your dataset that are significantly higher or lower than the rest of the data. These values can greatly skew the result, leading to misleading conclusions, especially in fields such as education, healthcare, or finance, where variability can materially affect decisions.

It is important to handle outliers before calculating the standard deviation.

1. Use Conditional Formatting to Spot Them

You can highlight and filter out extreme values using Conditional Formatting. You can set custom thresholds to visually flag outliers.

2. Manually Remove or Correct Outliers

After highlighting the outliers, check each point individually based on the context of your research and analysis. If a data point is an error (e.g., typing 900 instead of 90), correct or remove it before calculation.

Caution: Don’t remove outliers without understanding their context—they might represent critical edge cases.

3. Use Filters or Helper Columns

You can use Excel’s filter option to temporarily exclude outliers and compute the standard deviation on the remaining dataset.

4. Interquartile Range (IQR) Method

There is a way to mathematically calculate and find the outliers. You can do this in the following steps:

• Calculate Q1 (25th percentile) and Q3 (75th percentile)
• Find IQR = Q3 – Q1
• Any value less than Q1 – 1.5 × IQR or greater than Q3 + 1.5 × IQR is commonly flagged as a potential outlier

Use =QUARTILE.INC(range,1) for Q1 and =QUARTILE.INC(range,3) for Q3 in Excel. Use =QUARTILE.INC(range, 1) for Q1 and =QUARTILE.INC(range, 3) for Q3 in Excel.

Note: Before removing any value, understand whether it’s truly an outlier or a valid but rare data point. Sometimes, outliers that carry important meaning must be retained in the dataset while calculating statistical measures.

Understanding the Empirical Rule in Standard Deviation

The empirical rule, also known as the 68-95-99.7 rule, helps you understand how data is distributed around the mean in a normal distribution. This rule says that

• Approximately 68% of the data lies within 1 standard deviation of the mean.
• Approximately 95% of the data lies within 2 standard deviations.
• Approximately 99.7% of the data lies within 3 standard deviations.

Example:

If the average test score is 70 and the standard deviation is 5, this means that

• 68% of students will score between 65 and 75
• 95% will score between 60 and 80
• 99.7% will score between 55 and 85

This rule provides a clear visualization of data spread. The rule is appropriate when the distribution is approximately normal or bell-shaped.

Steps to Add Standard Deviation Bars in Excel Charts

Adding standard deviation bars to your charts helps visually represent the spread of data points and variability within your dataset. This makes your presentation easy to understand, even to those who don’t have any technical knowledge of standard deviation.

Step 1: Add Standard Deviation Error Bars

After creating your chart, Excel lets you add standard deviation error bars in two ways:

Option 1: Use Excel’s built-in Standard Deviation option

1. Select the chart.
2. Click the Chart Elements (+) button.
3. Select Error Bars → Standard Deviation.
4. Excel automatically calculates and displays the standard deviation error bars.

Option 2: Use Custom Error Bars

Use Custom Error Bars when you want to specify particular error values or use values stored in specific cells.

1. Select the chart and click Chart Elements (+).
2. Select Error Bars → More Options.
3. In the Format Error Bars pane, select Custom.
4. Click Specify Value.
5. Enter the appropriate values for Positive Error Value and Negative Error Value, or select a cell range containing the error values.
6. Click OK to apply the error bars.

Note: Use Excel’s built-in Standard Deviation option when you want Excel to calculate the error bars automatically. Use Custom Error Bars when you need to control the error values manually or reference specific cells.

Step 2: Create a Chart

• Select the data range
• Go to the Insert tab in the Excel ribbon.
• Choose a Chart Type (such as Column or Line Chart) from the Charts section.
• In the above example, a line chart would be best to showcase the ups and downs of the data.

Step 3: Add Error Bars

• Once your chart is selected, go to the Chart Tools section and click on the “Chart Design” tab.
• Click Add Chart Element > Error Bars > More Error Bars Options.

Step 4: Customize the Error Bar

• In the Format Error Bars pane, select Custom under the Error Amount section.
• Click Specify Value to enter the custom values for the error bars.
• In the Positive Error Value and Negative Error Value boxes, input the standard deviation value that you calculated in step 1. Enter the appropriate values in the Positive Error Value and Negative Error Value boxes, or select a cell range containing the error values.
• You can further format the line, style, width, etc., of the error bar.

Step 5: Interpret the Standard Deviation Bars

The standard deviation bars represent how much the quiz scores deviate from the mean. When comparing different datasets or series, larger standard deviation values indicate greater variability, while smaller values indicate less variability. In this example, if the same SD is applied to every point, the error bars will have the same magnitude.

How to Interpret Standard Deviation Results

Standard deviation can help you interpret a dataset in several ways

1. Understanding the spread of data

• Values required: Mean of the data and Standard deviation of the data
• While there’s no fixed threshold, a common guideline is:

Note: There’s no universal threshold for what constitutes a high or low standard deviation. Interpret SD based on the units, distribution, and context of the dataset, and compare it with similar datasets when appropriate.

2. Identifying Outliers

• Values required: mean ± 3 SDs
• For approximately normal data, observations outside the mean ± 3 SDs may be flagged for further investigation. This rule should not be treated as a universal outlier test.
• If adding a data point significantly changes the standard deviation, it may indicate an influential or extreme observation that warrants further investigation.

Difference between Standard Deviation and Standard Error

The table below summarises the key differences between Standard Deviation (SD) and Standard Error (SE).

Understanding these differences can prevent mistakes while calculating standard deviation in Excel.

Common Mistakes to Avoid While Calculating the Standard Deviation in Excel

1. Using Non-Boolean Categorical Values in =STDEVA

In Excel, the STDEVA function treats logical values TRUE and FALSE as 1 and 0, respectively. However, non-Boolean text entries such as “Yes” or “Pending” are treated as 0 by STDEVA, which can materially change the result. To avoid this problem, make sure the dataset contains TRUE or FALSE statements when using =STDEVA. On the other hand, if ‘Yes’ and ‘No’ are used, then these should be transformed into TRUE and FALSE for accurate calculations.

2. Mixing Data Types in Standard Deviation Calculations

Ensure that your data range includes consistent data types (e.g., just numbers or only Boolean values) when doing the standard deviation calculation in Excel. Incorrect results may arise from combining different data types, such as text or logical values with numbers. Excel may disregard text values or empty cells if you include them in the range for =STDEV.S or =STDEV.P. This can distort your result, especially if there are a lot of non-numeric entries. Clean your data at all times to prevent this problem.

3. Ignoring Empty Cells

Keep in mind that standard deviation formulas, such as =STDEV.S or =STDEVA, don’t count empty cells; they skip them. If your data range has any errors (like #DIV/0!), the formula will return an error. Blank cells are ignored by these functions. However, error values such as #DIV/0!can cause the formula to return an error and should be corrected or handled before calculating SD.

Practical Applications of Standard Deviation

Standard deviation is a widely used statistical measure for analyzing data, from student projects to professional research. Some of the practical applications of standard deviation are as follows:

1. In engineering projects, precise measurements are crucial. Standard deviation can be used to assess the consistency of measurements in experiments. For example, if you’re measuring the dimensions of a product, a low standard deviation indicates that the measurements are consistent, while a high standard deviation may signal measurement errors or inconsistencies in the manufacturing process.
2. In manufacturing industries, engineers and manufacturers use standard deviation to monitor product quality. When producing parts like nuts and bolts, the standard deviation of their sizes helps ensure that all products meet the required specifications. A low SD indicates that all the products are of the same size.
3. In data science and machine learning, standard deviation helps evaluate the spread of data points. When training a machine learning model, to understand how widely data points vary around the mean, data scientists use the measure of standard deviation. This helps in making more accurate predictions or understanding the behavior of the system being modeled.

Start Your Excel Journey – 100% Free

Learn the essentials of Excel through interactive lessons and practical examples.

Explore Program

Conclusion

In this article, you explored the various methods for calculating standard deviation in Excel. You explored the two main types of datasets, sample and population, and learned how to calculate the standard deviation for each. You also learned how outliers affect the calculation and how to visualize standard deviation. Finally, you reviewed the difference between standard deviation and standard error and common mistakes to avoid. You are now aware of the difference between standard deviation and standard error. For beginners, we also highlighted common mistakes to avoid when calculating standard deviation in your projects. This article summarises how to calculate standard deviation in Excel from your data.

To take your Excel skills to the next level, explore this comprehensive Excel training course and get hands-on practice. Also, get interview-ready with Excel interview questions curated by industry professionals.

Useful Resources:

• Understand VBA in Excel
• Top Excel Formulas
• MIS Report in Excel

Frequently Asked Questions