Standard Deviation In Excel

What Is Standard Deviation In Excel?

Standard deviation helps users measure the dispersal (spread) of a dataset concerning its mean. In Excel, STDEV, STDEV.S, and STDEVA formulas are essential to calculate sample standard deviation, while population standard deviation calculation uses STDEVP, STDEV.P, and STDEVPA formulas.

As standard deviation helps assess market fluctuations, users find it useful for planning their future investments and trade practices. For example, the following table lists the scores of Mathematics students. Next, we want to calculate the dataset’s sample and population standard deviation.

Calculating Sample Standard Deviation:

Calculating the sample Standard Deviation requires a population subset (cells B2:B6 in this case).

We will enter the sample standard deviation in Excel formula in cell B13: =STDEV.S(B2:B6).

Thus, we will get the sample standard deviation of the chosen population subset as 12.54192968.

Calculating Population Standard Deviation

Since the entire dataset is the population, the students become the population. Therefore, we need to include the entire population’s Mathematics scores (B2:B11) to calculate Population Standard Deviation.

Next, we will enter the population standard deviation formula in cell B14.

=STDEV.P(B2:B11)

Thus, we will get the population standard deviation as 13.13773192.

Standard Deviation (SD) Excel Formulas

In excel, there are 6 unique functions to calculate Standard Deviation (SD) with formulas categorized into Sample and Population –

1. Sample Standard Deviation

It is obtained by calculating a few values from the dataset (called population subset). When we want to calculate the standard deviation of a sample or a subset of the entire dataset, we can use STDEV, STDEV.S, and STDEVA functions in Excel.

STDEV

One of the most common Excel formulas used to calculate Sample Standard Deviation. To use this function, we can either type the function in the cell or select the same from Formulas > More Functions > Compatibility.

Syntax

The STDEV function has 2 arguments –

• number1: This is a mandatory argument, referring to the first value of the sample (population subset)
• number2: This is an optional argument that denotes the corresponding value in the sample

For example, below are the marks obtained by a sixth-grader in 4 unit tests for different subjects.

We need to calculate the Sample Standard Deviation of all the 4 unit test scores in Science using cell references C4, C9, C14, and C19.

Thus, the formula of the STDEV function will be:

=STDEV(C4,C9,C14,C19).

Step 1: Enter the STDEV function in F1 and select the cell references from the table.

(Note: Each of the arguments must be separated by a comma)

Step 2: Press the ‘Enter’ key to view the output.

STDEV.S

The upgraded version of the STDEV function is the STDEV.S, where ‘S’ indicates the sample dataset. Like STDEV, this function calculated Sample SD and was introduced in Excel 2010. We can either type the STDEV.S function in the desired cell or select the same from Formulas > More Functions > Statistical.

Syntax

The STDEV.S function has 2 arguments –

• number1: The first argument is mandatory and refers to the first value of the sample in the population
• number2: This is an optional argument that denotes the corresponding value in the sample

For example, the height data of the kindergarten students are provided. But, first, we have to determine the average height of students using the STDEV.S function.

Step 1: The mean (average) of the  student height data must be calculated  by entering the AVERAGE formula in cell B9:

=AVERAGE(number1,[number2]…) .

Step 2: Here, cell references will be B2:B7. Thus, the AVERAGE excel function will be:

 =AVERAGE(B2:B7).

Step 3: Press the ‘ENTER’ key to find the dataset’s average.

Step 4: Now, enter the STDEV.S function in cell B10.

Step 5: Here, the arguments will be B2:B7. So, the formula will be:

=STDEV.S(B2:B7)

Step 6: Press ENTER key to determine the sample SD of the dataset.

From the obtained mean value of 43.16 and sample SD value of 3.7, it is clear that the height of the kindergarten students ranges between 39-46 inches. It is derived by adding/subtracting the differences, i.e., 43.16 – 3.7 = 39.46 or 43.16 + 3.7 = 46.86.

Note: The functions STDEV and STDEV.S include numbers and ignore logical values (TRUE or FALSE), text, error values, and empty cells in cell references and arrays. However, if we provide logical values and text form of numbers directly as arguments, the functions include them.

STDEVA

STDEVA is yet another function used to calculate standard deviation in Excel. Similar to STDEV and STDEV.S, the STDEVA function returns the sample SD value based on the population sample. We can either type the STDEVA function in the desired cell or select the same from Formulas > More Functions > Statistical.

However, compared to functions STDEV and STDEV.S, the STDEVA function features logical and text values as arguments differently. It accepts logical values (TRUE as 1 and FALSE as 0) present in arrays and references and can be directly included as an argument. The function counts text values in arrays or references as 0 and reads the textual representation of a number as the number itself. STDEVA function ignores empty cells present in the dataset.

Syntax

The STDEVA function has 2 arguments –

• value1: This is a mandatory argument and denotes a sample from the entire population.
• value2: The second argument is optional and refers to the second value in the dataset.

2. Population Standard Deviation

A fixed value is obtained by calculating the entire dataset (also called population). When the dataset has limited values, we can calculate SD for the whole population using Population SD.

Functions STDEVP, STDEV.P, and STDEVPA are available in Excel to calculate population SD.

Note: For the population SD functions:

• The function can accept 1-255 numeric arguments, such as numbers, named ranges or references containing numbers, and arrays.
• The arguments provided should contain at least two numeric values. Otherwise, the function will return #DIV/0!
• The functions STDEV, S, STDEVP, and STDEV.P return #VALUE! Error if any of the values input directly as arguments are not numeric values. On the other hand, the functions STDEVA and STDEVPA return this error if the data directly supplied as an argument is a text value that does not interpret as a numeric value.

STDEVP

The function calculates and returns the SD value for the entire dataset or the total population. This is one of the oldest SD formulas used in Excel. It is available in the Formulas > More Functions > Compatibility

Syntax

The arguments are:

1. number1: The first reference or value in the population data. It is a mandatory input.
2. number1: It is an optional argument and is the second value or reference in the population data.

STDEV.P

It works like the STDEVP function, but returns a more accurate SD value for the given population dataset. You can apply the formula from Formulas > More Functions > Statistical.

Syntax

The arguments are:

1. number1: The first value or reference in the population. It is a mandatory input.
2. number2: It is optional, another value or reference in the population.

Note: The Functions STDEVP and STDEV.P count only numeric values in reference or array arguments. But, if you directly provide logical values and text representation of numeric values in the arguments list, then the functions will count them.

STDEVPA

The function returns the SD of an entire population, where the dataset can include numeric, text, and logical values. You can find the STDEVPA function in Formulas > More Functions > Statistical.

Syntax

The arguments are:

1. value1: It is a mandatory argument and corresponds to the first value or reference in the population dataset.
2. value2: It is optional and another reference in the population.

For non-numeric values, the function works the way STDEVA() handles them.

Standard Deviation Calculation Examples

It can be quite challenging to determine the standard deviation of n number of data in Excel. However, calculating Standard Deviation for a population sample can easily estimate population Standard Deviation.

Let us look at the following examples to understand how we can find standard deviation in Excel for a sample and entire population.

Example 1 – Sample SD

Consider the following data showing quantities of a commodity (in kg) purchased from 2015 to 2020.

Assume it is the entire data or population. Data available under 2015 and 2016 are taken as the sample datasets. We can calculate SD for cell ranges B3:B14 and D3:D14 separately, which will be the sample SD values. This will help us evaluate and compare the variance for each year in the sample from the mean value, given the mean value is the same throughout the data (same every year).

Step 1: Select cell A18 to enter the STDEV.S() function.  Using cell ranges B3:B14 as  the argument, the formula in cell A18 will be: =STDEV.S(B3:B14)

Step 2: Press ‘Enter’ to view the output.

Step 3: Similarly, select cell B18 and enter the STDEV.S formula, with cell reference D3:D14 as the argument. The formula in cell B18 will be:=STDEV.S(D3:D14)

Step 4: Press the ‘Enter’ key to obtain the result.

Comparing the SD obtained for the samples (2015 and 2016), concerning the sample dataset mean, the 2016 data appears to be more dispersed than the 2015 data.

Let us look at how the standard deviation in Excel calculation occurs using STDEV.S.

The mathematical representation of the sample SD formula is:

Sample Standard Deviation = √[∑(X_i – X_m)² / (n-1)]

• X_i – the i^th value of the sample dataset
• X_m – mean value of the sample dataset
• n – Number of data points

Following are the steps to solve the equation using the sample datasets from the above table.

Step 1:  Find the mean value for cell ranges B3:B14 and D3:D14, using the AVERAGE formulas mentioned in cells G3 and H3 as shown below:

Step 2: Determine the deviation, which is the difference between the data points in the two columns and the mean value. The formula for calculating 2015 data points will be: =J3-$G$4

Step 3: With the cursor on cell K3, drag and copy the expression in cells K4:K14.

Step 4: Similarly, the difference between the 2016 data points and the mean value is calculated in cell M3 using the formula =L3-$H$4

Note: We use absolute reference for cells G4 and H4 as they have to remain constant while calculating the deviations for columns J and L from their respective mean values.

Step 5: With the cursor on cell M3, drag and copy the formula in cells M4:M14

Step 6:  Determine the square of the values in columns K and N and enter the values in columns L and O, respectively, to find the square of the difference, just like the previous step

Step 7: Apply the SD mathematical formula in cell K20 as displayed in cell K19. The sample SD for 2015 will be =SQRT(SUM(L3:L14)/(COUNT(J3:J14)-1))

The SUM() adds the square values listed in cells M3:M14, and the COUNT() counts the cells with numeric values in cells K3:K14. Finally, the SQRT() function finds the square root of the entire expression to return the sample standard deviation in Excel.

Step 8: Similarly, enter the formula in cell M20, as displayed in cell M19.

=SQRT(SUM(P3:P14)/(COUNT(N3:N14)-1))

Note: The sample SDs for 2015 and 2016 are the same when applying the STDEV.S() and mathematical formula of sample standard deviation in Excel.

Example 2 – Population SD

The function STDEV.P helps users determine standard deviation in Excel for the entire population or the dataset.

The population SD tends to be accurate compared to the sample SD formula. Therefore, we use the whole dataset to calculate the population standard deviation and population subset (sample) to determine sample SD.

The sample SD formula includes Bessel’s correction to compensate for the bias when calculating sample SD. Bessel’s correction (n-1) is found in the denominator, where ‘n’ is the total data points. This correction ensures the sample SD determined using STDEV.S() is the closest estimate of the population SD.

On the other hand, the STDEV.P() does not include Bessel’s correction, and the denominator has the term ‘n,’ which is the total of data points.

So, the formula for population standard deviation in Excel, STDEV.P() is –

Population Standard Deviation = √[Σ(X_i – X_m)² / n]

Here, the variables have the same interpretation as the sample SD formula.

Consider the below table with a list of the US Army applicants and their height (in feet).

Let us assume that this is the entire dataset. Since the data is not enormous, we calculate population SD instead of the sample SD.

The entire dataset is calculated in population SD, and thus, the cell references used in the STDEV.P formula will be B2:B21.

Thus, the formula of the STDEV function will be =STDEV.P(B2:B21)

Step 1: Select cell F3 and enter the STDEV.P formula using the cell range B2:B21 with the determined formula.

Step 2: Press the ‘Enter’ key to view the output.

Let us calculate population SD using the STDEV.P function

Step 1: Find the mean values given in column B using the AVERAGE function.

Since the mean is calculated using the whole table, the cell range B2:B21 is chosen.

Therefore, the formula is: =AVERAGE(B2:B21)

Step 2: Press the ‘Enter’ key to view the mean value.

Step 3: We need to insert two more columns, as shown below. The first column will show the difference between the height values, the mean, and the deviation. The second column will list the square of the difference for each applicant.

Select cell C2 and enter the formula to find the difference between cell B2 and cell B23 with absolute cell reference, as we need to have the mean value constant throughout. So the formula in cell C2 becomes =B2-$B$23.

Step 4: Press the ‘Enter’ key.

Step 5: With the cursor on cell C2, drag and copy the formula throughout cells C3:C21.

Step 6: Select cell D2. Enter the formula to calculate the square of the number in cell C2. The formula becomes =C2*C2.

Step 7: Press the ‘Enter’ key to obtain the square value in cell D2.

Step 8: With the cursor on cell D2, drag and copy the formula through cell D3:D21.

Step 9: Select cell G3 and enter the mathematical population SD formula as shown at the beginning of the illustration.

The value obtained using the STDEV.P() function and the value derived by mathematical formulas for calculating population SD are the same.

Note: The denominator has the total number of data points, ‘n,’ unlike the term ‘n-1’ in the sample SD formula.

Relative Standard Deviation In Excel

Relative standard deviation (RSD) is a special form of Standard Deviation (SD). It measures the dispersion of data concerning the dataset’s mean value. It returns values either as a percentage or a number. Higher RSD shows that the data is dispersed (spread) more from the mean. In contrast, lower RSD indicates precise and accurate measurement.

The formula to determine relative standard deviation in Excel is

Relative Standard Deviation = sx100 / x_mean

where,

• s – Standard Deviation
• x_mean – Mean of the dataset
• x1000 – Multiplied by 100

Steps To Find RSD In Excel:

1. Determine the SD for the required dataset using the appropriate formula of standard deviation in Excel.
2. Calculate the mean of the dataset using the AVERAGE
3. Divide the SD value by the mean value and multiply the obtained value with 100 to find the RSD of the dataset.

RSD is a useful analytical tool to assess stock prices, analyze how it affects market growth, and interpret statistical data across different sections.

Add Standard Deviation Bars In Excel

1. Open the spreadsheet with the required data and bar graph.
2. Click anywhere on the graph. Excel provides 3 options next to the selected graph.
3. The Chart Elements icon is indicated with a ‘+‘ button. Click on the icon and check the box against the Error Bars
4. Select the arrow adjacent to Error Bars and click on Standard Deviation.
5. Now, Excel shows the graph with standard deviation bars with the data.

For example:

Below is a table of weekly hen_stock, along with a bar graph representing the data. The X-axis indicates the weeks, and Y-axis shows the hen_stock in numbers.

Step 1: Click anywhere on the graph, and Excel will provide three options next to it. Click on the Chart Elements ‘+’ icon.

Step 2: Click on the Chart Elements icon, and check the box adjacent to the Error Bars as shown below.

Step 3: Click the arrow and select Standard Deviation from the drop-down list.

Note: Alternatively, we can navigate the Chart Group in the Insert tab and click the Add Chart Element option to select Standard Deviation from the Error Bars drop-down options.

Step 4: After choosing the Standard Deviation option, the graph will show SD bars below.

Step 5: The SD bars can also be formatted. Double-click anywhere on the graph to format and select the SD bars. The window Format Error Bars appears on the right side with options where we can make the appropriate changes.

Note: The standard deviation bars in Excel represent how the data points are dispersed from the mean value. We can check the extent of dispersion based on the overlapping error bars’ data points and evaluate the difference between the mean value and dispersion level within the dataset.

Important Things To Note

• Standard deviation in Excel shows how a set of numbers are dispersed from the mean value of the dataset.
• The entire dataset is called the population, and a subset of the whole population is referred to as a sample.
• STDEV.S and STDEVA are used to determine Sample Standard Deviation (SD).
• STDEVP, P, and STDEVPA calculate Population Standard Deviation (SD).
• Relative SD helps determine the dispersion levels of data points from the dataset mean value. By adding SD bars, we can graphically analyze the SD of the database.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the standard deviation in Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Standard Deviation in Excel. Here we learn standard deviation types with formulas, calculations, examples, & downloadable template. You can learn more from the following articles – 

• Data Validation In Excel
• INDIRECT Excel Function
• SUMIFS Excel Function