How to Calculate Standard Deviation in Excel

Make yourself a pro by following this tutorial to determine Standard Deviation in Excel in an instant.

In the field of statistics, Standard Deviation is used to determine how much the value of a data set differs from the average or mean value. This allows us to understand how closely each value is grouped around the average.

There are a variety of professionals that use the standard deviation to determine a primary risk gauge, including Portfolio managers, insurance analysts Statisticians, Market research Real estate agents and stock investors, among others.

But, calculating Standard Deviation in Excel can be difficult for people who are new to Excel or don’t know how to use the. This is the reason, within this post, we’re going to discuss the meaning of Standard Deviation is and how to calculate it using Excel.

What is Standard Deviation?

The value of the mean represents the value that is the average of this data set. The Standard Deviation represents the difference between the values in the set of data as well as their mean value. In other words, the standard deviation will tell you if your data is near the average or differs significantly.

For instance, if an instructor were to inform them that her average student score is 60 (mean). If you have the list of the scores her students have then you can calculate the standard deviation to determine the accuracy of her statement.

There are two kinds of Standard deviations: standard deviation and the sample standard deviation. The standard deviation of the sample (SSD) can be calculated using randomly selected samples from data or the population while the population standard deviation (PSD) can be calculated using the whole data of the population.

The greater your standard deviation is, the further dispersed the data from the average, and the smaller the standard deviation is, the closer they are mean or average. If the standard deviation is zero means that all points of data in the set are equally. A greater standard deviation indicates that the mean is not as accurate and the lower standard deviation means that the mean is more reliable.

There are two options to calculate Standard Deviation in Excel using formulas or built-in functions.

Sample vs. Sample

The use of sample calculations is common since there are times when it is not feasible to determine the whole data set. Before you begin calculating standard deviation, it is important to be aware of the type of data you have , whether the entire set of data or a sample or a portion of data. Because you will need to employ various formulas and functions to calculate samples and for the population data.

• The population data is the whole set of data. This means that the data is accessible to every member of the group. The standard deviation of the population calculates the distance of every value in a set of data from the mean of the population. A good example of data from the population is the census.
• The sample data, on the other hand is a subset of the entire population. It is a subset which is representative of the whole population. It is small portion of data that has been taken from the entire population. The data from the sample is used in situations where the whole population isn’t available or it’s enough to make a statistical calculation. An excellent illustration of a sample is an interview.

Calculate Standard Deviations using manual Calculations

Calculating standard deviations manually is an arduous procedure, but it is easily accomplished using formulas. The first step is to determine the variance of data, and then calculate what is the square root that variance.

Population Standard Deviation

The formula to calculate the standard deviation of the population is as follows:

Where

• M is the arithmetic mean
• Xi represents individual values within the data set
• N is the sum of values (X) numbers (population) within the data set

Sample Standard Deviation

The formula to calculate the standard deviation of the sample is as follows:

Where

• M is the arithmetic mean
• Xi is a set of individual values within the data sample set
• N is the total amount of values (X) numbers (sample) within the set of data

In calculating the standard deviation of a sample, only one sample from that data set will be taken into consideration as part of the entire set. Therefore, Bessel’s correction (N-1) is applied instead of N to provide an accurate estimation of standard deviation.

To determine the standard deviation of the data set below Follow these steps:

1. Calculate the Mean (Average)

The first step is to determine the mean or average (m) for all the values within this data set. To calculate this it is simply adding all the numbers and divide the total by the number of values in this data set. The average can be determined by using the manual arithmetic formula:

96+69+99+57+35+89+55+93+41+21/10=65.5

You can also utilize Excel’s AVERAGE feature in Excel to determine the mean:

=AVERAGE(B2:B11)

In this case, B2:B11 in the formula is those cell (denoted by the column’s number, followed by the row’s number) which contain the data that we will use to determine the mean. Replace these numbers with the cells on your worksheet applying the formula.

2. For Each Number, Calculate the Distance to the Mean:

Then, you have to determine the difference or distance from that mean, by subtracting the value of the Mean from every value within this data set. To calculate this, use the formula below:

=B2-$B$13

or

=(B2-AVERAGE(B2:B11))

$B$13 is the average of all the information. The formula above is entered into cell D2 and applied to the entire column by moving it downwards to determine the deviation for the entire column (D2:D13). Your cursor should be in the lower-right edge of the cell, in this case D2. A symbol with the letter ‘+’ will be displayed Drag it downwards.

3. The difference is the square

Now, you can square the difference between each value by following the formula below:

=D2^2

Apply the formula to the entire column by moving it downwards. The process of dividing the difference will transform the negative numbers into positive ones.

4. Sum the Squared Differences

Then, add all squares of the variance from what is called the average (Xi-m) ². Here’s the formula to add all the squared differences:

=SUM(E2:E11)

If you have a large data sets You can determine the number of values using:

=B14/10

5. Calculate the Variance

In order to calculate variance, you must divide the squared variance by the number of values.

The steps to calculate the sample and standard deviation of the population were identical. In this phase the formulas are expected be modified slightly to calculate both standard deviations, as previously explained.

To determine the samples of variance it is necessary to divide the squared difference in the amount of numbers less 1.

=B14/(10-1)

or

=SUM(E2:E11)/(COUNT(B2:B11)-1)

You can apply one of these formulas to calculate the variance of your sample.

To calculate the variance of a population You must divide the squared difference by the sum of values:

=B14/10

or

=SUM(E2:E11)/(COUNT(B2:B11)

6. Get the Square Root of the Variance

Then, you must calculate an integral from the variance above to calculate what is known as the Standard Deviation.

Sample Standard deviation:

To determine the standard deviation, calculate your square root from the variance in the sample:

=SQRT(B15)

or

=SQRT(SUM(E2:E11)/(COUNT(B2:B11)-1))

Population Standard Deviation:

To determine the standard deviation of the population Calculate your square root for the variance of the population:

=SQRT(B16)

or

=SQRT(SUM(E2:E11)/COUNT(B2:B11))

Calculate the Standard Error in Excel

Standard error or mean, or the standard error is a different measure of variation, which is very like standard deviation, however, it is distinct. It’s a measure of how much different a population’s will likely to differ from the mean of the sample.

The main difference in standard deviation and the Standard Deviation is the fact that the standard error utilizes statistical data (sample information) and standard deviation utilizes variables (population information). Standard Deviation is a more general term for population data. Standard Deviation usually measures the variation within one sample, while Standard Error measures variability across several samples of a population. Here’s how to determine Standard Error (SE):

The most common formula for Standard Error is to divide the deviation by square roots of the total number of values contained in the set of data.

is Sample Standard Deviation/Square root of the number of numbers (n)

To determine Standard Error (SE), use the formula below:

=STDEV(B2:B11)/SQRT(COUNT(B2:B11))

In this case, STDEV(B2:B11) determines that the average deviation for the data set (B2:B11) and the SD divides by square roots of number (n) found in the B2:B11. In lieu of COUNT(B2:B11) it is possible to directly input numbers of the values within B2:B11’s data set (10).

Determine the Standard Deviation by using Excel Built-in Functions

Microsoft Excel includes six different functions to calculate standard deviation. This makes it very simple to calculate the Standard Deviation in Excel, decreasing the amount of time needed doing the calculations. The only issue is that you must be aware of which function you should use for the task at hand. The function you should apply is based on the information you have , whether it is a the sample or the population.

If you type the word "STDEV" in the blank Excel cell it will display the six different versions of standard deviation formulas:

• STDEV The function is used to calculate the standard deviation of samples of data. This is the oldest Excel feature (before Excel 2007) for finding the standard deviation. It is still available in most recent Excel versions to allow compatibility.
• STDEVP It is an old version of standard deviation. It is compatible to Excel 2007 and older. It calculates the standard deviation based on population data.
• STDEV.S: This is a more recent version of STEDV function (available from Excel 2010.). It can be used to calculate the standard deviation of samples of data.
• STDEV.P: This is a more recent version of the function STEDVP in Excel (available from Excel 2010.). It can be used to calculate the standard deviation of all population information.
• STDEVA: The formula computes the sample standard deviation of a data set by incorporating the text as well as logical values. It is quite like STDEV.S. The Text and all FALSE values are interpreted as ‘0’, while TRUE is taken to be 1.
• STDEVPA STDEVPA: This formula calculates the population standard deviation, which includes the text as well as logical values. It is like STDEV.P. The Text values are interpreted as ‘0’, while TRUE is taken to be 1.

STDEV, STDEVP, STDEV.S, STDEV.P ignores the text and the logical (TRUE OR FALSE) values within the data. In the majority of cases you’ll only require STDEV.P or STDEV.S to carry out the standard deviation calculation.

To help you gain an understanding of the process the six functions, here’s a brief summary of the six roles:

Excel STDEV.P Function for Population Standard Deviation

If your data set is representative of the entirety of the population, you can utilize STDEV.P function to calculate the standard deviation of your population. STDEV.P method to determine the population standard deviation.

The syntax of STDEV.P function is: STDEV.P functions is as follows:

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

Where

• number1 is the first argument which corresponds to the initial data point in populace data.
• [number2] ],.. 2] is the second argument for numbers that is the data point in the population data , and the list goes on.

The function should include two or more values in the arguments , and the function is able to accept as many as 255 arguments. You can enter numbers, arrays, or cells as argument.

Example:

=STDEV.P(B2:B11)

In this case, B2:B11 is the cell range which contain the data for the population. The formula above will give the standard deviation of the specified population. As you can see, we obtain precisely the exact same results 26.58289 (population average deviation) using the manual method. This program automatically does every step of the manual method above in the background, and then gives you the results.

If your data includes any boolean value (TRUE OR FALSE) or textual values, this program ignores these values and calculates the standard deviation using the other values.

As you will see below the formula above yielded a different result due to the fact that it did not consider the values in cell B8 as well as B11.

Excel STDEV.S Function for Sample Standard Deviation

In the event that your set of data is the population of your sample and you want to use STDEV.S to calculate the sample standard deviation. STDEV.S function to determine the standard deviation of the sample. For instance, you’ve taken a test with many students, but only have an average test score of 10 students. Therefore, you could use STDEV.S to calculate the standard deviation of your sample and then apply it to the whole sample.

The syntax of STDEV.S function is: STDEV.S functions is as follows:

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

Where

• number1 is the first argument that corresponds to the data point that is the initial one in this sample.
• [number2] ],… 2] is the second argument for numbers that is the data point in the sample data, and the list goes on.

You can enter numbers, arrays, or reference to cells as argument.

Example:

=STDEV.S(B2:B11)

The formula above sums all squares of the variance from the mean, and then divides it by the number plus 1. (n-1) on the back, and then returns the following result.

STDEV.S function also does not consider the logic and textual values when they are present within the set of data, as illustrated below.

Excel STDEVA Function for Sample Standard Deviation

The STDEVA function is another one that calculates the standard deviation of an individual sample, however it differs from the STDEV.S only in the manner it deals with textual and logical values.

In all of the above functions, logical values as well as text values are not considered however this function transforms these value into 1s as well as zeros.

• The logical value TRUE is counted as ‘1’ while False is counted as ‘0’. The values may be placed in arrays, cells or directly entered in the program as argument.
• Text strings, including blank strings (“”), representing numbers in text, as well as any other text is considered to be ‘0’..

To determine the standard deviation of an entire sample, which includes texts and logical values using this formula:

=STDEVA(B2:B11)

If there aren’t any text or logical values within the set of data it will return the standard deviation.

Excel STDEVPA Function for Population Standard Deviation

Excel also includes an option called STDEVPA that allows the calculation of standard deviations for an entire population, by incorporating the text as well as logical values. The function is similar to the STDEVA function in the handling of the text and boolean values.

• The logic values TRUE are counted as ‘1’ while FALSE is counted as ‘0’.
• Text strings, including blank strings (“”), representations of text numbers, as well as any other text is considered to be ‘0’..

To determine the standard deviation of an entire population, which includes texts and logical values Use the following formula:

=STDEVPA(B2:B11)

Excel STDEV Function

Excel’s STDEV Function is very like the STDEV.S function. It can determine the standard deviation of samples of data. If you’re using Excel 2007 or an earlier version it is necessary to utilize the STDEV function in order to calculate the standard deviation.

Syntax for the STDEV function:

=STDEV(number1, [number 2],...)

Example:

=STDEV(B2:B11)

The STDEV function is present in latest versions of Excel to allow compatibility, which implies that it will be removed in the near future. Therefore, Microsoft recommends users to make use of STDEV.S rather than STDEV.

Excel STDEVP Function

Excel’s STDEVP Function functions exactly in the same manner as it does with the STDEV.P function. If you’re using Excel 2007 or an earlier version it is necessary to make use of this STDEVP function in order to determine the average deviation of the population data.

The Syntax for the STDEVP function:

=STDEVP(number1, [number 2],...)

Example:

=STDEVP(B2:B11)

STDEVP could be removed from in the future Excel version.

Calculate Standard Deviation in Excel Using Insert Function

If these functions are difficult to remember, you could make use of an Insert Function option to calculate quickly your standard deviation. Additionally, you can make sure you don’t miss any mistakes when formulating the formula by making use of the Insert function feature to insert the desired formula into the desired result cell. Here’s how to accomplish this:

The first step is to select the cell in which you would like to output to output to be. Next, open the tab ‘Formulas’ and click the “Insert Function’ button on the ribbon.

It will bring up an Insert Function Dialog box. In the dialog box, type in Standard Deviation within the “Search for Function field’ or select the category ‘Statistical’ from the ‘Or choose the category’ drop-down and then click Go.

Scroll down to the function list in the ‘Select a Function window, select the standard deviation function (STDEV.P, STDEV.S, STDEVA, or STDEPA) then select ‘OK’.

It will open Function Arguments dialog window with two fields: Number 1 and 2. The Number 1 field, type in the range you would like to determine your standard deviation. You can also click the upward-facing arrow within the text field, and highlight the range on the worksheet. Each number argument can be used up to 255 cells. When the total number of cells is greater than the 255th, you can make use of numbers 2, 3 and so on.

Then, click ‘OK’.

After you click OK after which it will calculate the Standard Deviation using the chosen function, and display the results in the cell you initially selected.

Get Standard Deviation using the Data Analysis Tool within Excel

Additionally, you can get standard deviation in your Descriptive Statistics summary of your data by using the data analysis tool. Excel’s Data Analysis tool can automatically create various important statistical numbers such as mean median, variance Standard Deviation, standard error, and more.

For the data set below we’re looking to calculate descriptive statistics. Here’s how to accomplish that:

To obtain Descriptive Statistics, go to the tab ‘Data’ and then click on the “Data Analysis Tool’ from within the Analysis section.

Within the Data Analysis dialog window, choose the option ‘Descriptive Statistics’ in the section Analysis Tools and then click “OK”.

This will bring up you to the Descriptive Statistics dialog box in which you will need to set the input and output options.

Input Options

Then, type in the variables/values that you wish to study in the “Input Range Field.

You can enter manually the range into the field, or click the upward-facing arrow at the bottom of the field to select the range.

Then, choose the range on the sheet, and then click the downward arrow button for confirmation of the selection.

Then, select the way you’d like to arrange the variable (rows or columns). In this case, we’re choosing “Columns” because the input range we are using is columns.

If you added the selection (in the input Range) using headers make sure you select the ‘Labels in the first row’ option.

Output Options:

In the field ‘Output Range in the ‘Output Range’ field, type in the range that you would like to display the result of your statistical study. If you wish to display the results on the worksheet you are currently working on or in another worksheet within the current workbook, select the Output Range radio button and enter the range in the field that is next to it.

If you wish to display the results in a brand new spreadsheet, just click the “New Worksheet Ply” radio button. If you wish to show the results in the new workbook, choose the option ‘New Workbook.

Then, select the option ‘Summary statistics’ and then click “OK”.

You will also receive all the information you need, including Standard Error Standard Deviation, Standard Error, Sample Variance, etc.

How do you calculate Standard Deviation using an IF Criteria

In addition to the Six Standard Deviation functions, Excel includes two additional functions known as DSTDEV and DSTDEVP that are used to calculate standard deviation using the IF-condition.

• The DSTDEV function is used to calculate the average deviation for data that is taken from the data set sample that meet the requirements.
• DSTDEVP function is utilized to calculate what is the average deviation for data that is taken from the population data set that meets the specified requirements.

DSTDEV Function for Sample Standard Deviation

The syntax of the DSTDEV functionis:

=DSTDEV(database, field, criteria)

• Database – The cell range (table) in which the entries in your data with values that you wish to calculate the standard deviation come from. The range should include headers on the first row.
• Field: It specifies the column or field where the numbers that you need to use for calculating the standard deviation are situated. It is necessary to indicate the field’s name (i.e. the column’s label, or head) with double quotation marks or field numbers (i.e. the column’s number) in the table.
• Criteria The cell range that contains the criteria you want to meet. The criteria range should contain at minimum one column label that matches the headers of your database and one cell beneath the column label which specifies the criteria from the column. It may contain multiple rows that specify different circumstances.

Example:

If you have the following data set, and you want to determine the standard deviation using the following conditions:

Example:

For instance, to determine the standard deviation of scores achieved in the math subject for all students, use one of the formulas below:

=DSTDEV(A1:E14,"Score",G1:H2)

or

=DSTDEV(A1:E14,5,G1:H2)

The formula above determines all scores that are correlated to Math and calculates the standard deviation of the scores. It is necessary to create an individual selection criteria (G1:H2) as illustrated below. Enter the formula into an empty cell.

DSTDEVP Function for Population Standard Deviation

The syntax of DSTDEVP:

=DSTDEVP(database, field, criteria)

Where

• Database – The cell range (table) in which the entries in your data with values you wish to calculate the standard deviation come from.
• Field: It specifies which column or field in which the values you need for the calculation of standard deviation are placed.
• Criteria The set of cells which contains the location the criteria you want to meet.

Example 1:

For instance, to determine the standard deviation of math scores achieved in the subject for all students, use the formulas below:

=DSTDEVP(A1:E14,"Score",G1:H2)

Example 2:

To determine the standard deviation of scores achieved in math by students aged 14 or older, use the formula below:

=DSTDEVP(A1:E14,"Score",G1:I2)

Wildcards

You may also apply the following wildcards within the text-related criteria to calculate the standard deviation:

• * Matches any character, in any amount
• ? – Matches any single character
• ~ – Finds * or ? characters in search.

Multiple-row Criteria

It is possible to include more than one column or row within the criteria range to calculate the standard deviation with OR and AND logic. If you include multiple rows within the range of criteria, programs will employ OR logic (TRUE when at minimum one of the conditions is true) to determine the average deviation. If you include multiple columns within the criteria range the function will apply an AND logic (TRUE when all conditions are true) to determine.

=DSTDEV(A1:E14,"Score",G1:G3)

Calculate Weighted Standard Deviation in Excel

When we determine a standard deviation of the dataset, we assume that all the values of the data set are equally weighted or have equal importance. In some instances there are instances where each value is given an individual weighting within the set, and certain values are more important than others due to the fact that certain values are more significant than other values.

In these cases it is not possible to utilize the built-in functions above to determine the standard deviation. Therefore, you must employ the other SUM, SUMPRODUCT, and SQRT functions to calculate the standard deviation that is weighted manually. Let’s look at how to calculate the weighted standard deviation using Excel.

Imagine you have a data set that contains the first column of data values, while the second column is a weighted list of each value:

The first step is to determine the weighted mean or the average of the data you have provided. This can be done using this formula

=SUMPRODUCT(value_range, weight_range)/SUM(weight_range)

Where,

• value_range range (or array) of number.
• weight_range – Range cells that have weights

Example:

=SUMPRODUCT(A2:A10, B2:B10)/SUM(B2:B10)

In the formula above, it is the SUMPRODUCT function multiplies each value (column A) by the value (column B) and then sums the results. The SUMPRODUCT output will be divided into the weights of the data to create the weighted mean.

Once you have the weighted average then you can determine the standard deviation based on weight.

To determine the standard deviation of the population information use the following formula:

=SQRT(SUMPRODUCT((value_range - weighted_mean)^2, weight_range)/SUM( weight_range))

Example:

=SQRT(SUMPRODUCT((A2:A10-B12)^2, B2:B10)/SUM(B2:B10))

In the formula above, the weighted mean calculated is subtracted from every value of data (column A) and then each result is then squared. Then, every results is then multiplied by the data weight (column B) by using the SUMPRODUCT function.

The SUMPRODUCT result is then divided into the SUM of the weights. The results are calculated by using the function SQRT to calculate the standard deviation value.

To determine the standard deviation of the data sample use the following formula:

=SQRT(SUMPRODUCT((value_range - weighted_mean)^2, weight_range)/SUM( weight_range)-1)

Example:

=SQRT(SUMPRODUCT((A2:A10-B12)^2, B2:B10)/SUM(B2:B10)-1)

The only difference from the formula above is the addition of “-1” to the sum of the weights for the population information.

Add Standard Deviation Bars In Excel

You can also include standard deviation bars to show the range that your normal deviation is by using Excel bar charts for error. Excel errors are one of the chart’s elements that allow you to represent the variability of data and measurement. To include standard deviation bars in your chart Follow these steps:

Create an graph or chart for the data you have. To create a graph, simply select the cells you want to graph and then click on the tab ‘Insert’ and select a graph option in the Charts category.

After the chart has been added then select the chart by clicking any area on the graph, then click on the “Chart Elements’ (+) button.

After that, select the “Error Bars option, and then select Standard Deviation..

In the end that the bars of standard deviation will be added to every data point as in the following.

Calculating Variance in Excel

In order to calculate what is the standard deviation for your information, you will often have to consider variance too. Variance refers to the variation in the data, which is the square of the standard deviation. Like standard deviation, it also comes with six built-in functions you can utilize to determine the variation for your information. However, Microsoft recommends users to make use of VAR.S as well as VAR.P to determine the variance of samples and the population data , respectively.

To calculate the variance using the data from the sample Use the following formula:

=VAR.S(B2:B15)

The formula above calculates the variation in the sample for the B2:B15 range and displays the results in cell E2.

To calculate the variance using population information Use the following formula:

=VAR.P(B2:B15)

That’s it. This is the only crash course you’ll need to learn for the calculation of Standard Deviation in Excel. So, now go ahead and showcase your new skills!