## 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.

## Table of contents

###### Key Takeaways

**Standard deviation in Excel**measures the variance of a dataset relative to its mean. The dataset can be the entire population or a sample from the dataset (population subset)- Excel has six functions to calculate Standard Deviation (SD).
**STDEV**,**STDEV.S**, and**STDEVA**determine sample SD, whereas**STDEVP**,**STDEV.P**, and**STDEVPA**calculate population SD. - Relative Standard Deviation (RSD) determines the dispersion of the dataset concerning the mean value.
- We can add SD bars to determine the difference between the dataset mean value and the variance level within the dataset.

### Standard Deviation (SD) Excel Formulas

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

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

**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:

**number1**: The first reference or value in the population data. It is a mandatory input.**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:

**number1**: The first value or reference in the population. It is a mandatory input.**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:

**value1**: It is a mandatory argument and corresponds to the first value or reference in the population dataset.**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})^{2} / (n-1)]**

**X**– the i_{i}^{th}value of the sample dataset**X**– mean value of the sample dataset_{m}**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})^{2} / 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 of the dataset_{mean}- x1000 – Multiplied by 100

**Steps To Find RSD In Excel:**

- Determine the SD for the required dataset using the appropriate formula of
**standard deviation in Excel**. - Calculate the mean of the dataset using the
**AVERAGE** - 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**

- Open the spreadsheet with the required data and bar graph.
- Click anywhere on the graph. Excel provides 3 options next to the selected graph.
- The
**Chart Elements**icon is indicated with a ‘**+**‘ button. Click on the icon and check the box against the**Error Bars** - Select the arrow adjacent to
**Error Bars**and click on**Standard Deviation**. - 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)

**How to get standard deviation in Excel?**

Excel has six built-in functions to determine standard deviation. Functions **STDEV**, **STDEV.S**, and **STDEVA **calculate sample SD, and **STDEVP**, **STDEV.P**, and **STDEVPA **functions return population SD. Refer to the examples above.

**How to calculate the standard deviation in Excel from the mean?**

1. We can find the difference between each data point and the mean value, called the deviation with the dataset mean value.

2. The square of the deviation for each data point has to be calculated, and all the values must be added.

3. For determining sample SD, the obtained result must be divided by **(n-1)**, where **‘n’** is the total number of data points in the given sample.

4. We need to determine the square root of the value obtained using the values found

5. For calculating population SD, we can follow the same process but here, we divide the sum of square values by **n**, where **‘n’** is the total data points in the population.

**How do you find standard deviation in Excel?**

1. Select the desired cell to display the SD for a dataset.

2. Enter the **STDEV** function with the arguments (numeric values, arrays, or cell references containing numbers).

3. Press the ‘**Enter’** key to get the SD value.**Note: **For a more accurate SD value, it is recommended to use **STDEV.S** and **STDEV.P** functions for calculating sample SD and population SD, respectively.

**What happens if the standard deviation is 0?**

If the SD value is 0, it indicates that all the values in the dataset are the same and thus equal to the mean value of the data. Hence, there is 0 variance in the dataset values relative to its mean.

**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 –

## Leave a Reply