EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Standard Deviation in Excel

By Pradeep SPradeep S

Home » Excel » Blog » Financial Functions in Excel » Standard Deviation in Excel

Standard Deviation in Excel

Standard Deviation in Excel (Table of Contents)

  • Standard Deviation in Excel
  • Standard Deviation Formula in Excel
  • How to Use Standard Deviation in Excel?

Standard Deviation in Excel

Standard Deviation shows how the entire population from the selected area differs from the mean point of the selected values. This helps in finding out and categorizing the values from the mean. We can calculate the Standard deviation in excel with three functions which are STD, STD.P, and STD.S where STD is not available in the latest version of excel, STD.P is used when we want to consider the entire population, and STD.S is used when we want to consider the sample data only.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Sample (STDEV.S) Standard Deviation in Excel

  • Standard Deviation function can be used as a worksheet function & can also be applied by using VBA code.
  • Investors most commonly use it to measure the risk of a stock (a measure of stock volatility over a period of time). Financial analyst often uses it for measuring and managing risk for a specific portfolio or fund.
  • It is also used in election polls and survey results (i.e. who is going to win an election) & weather prediction.

Definition

Standard deviation is a calculation that determines how much your values or datasets deviate (spread out) from the AVERAGE or MEAN value.

This Excel shows whether your data is near or close to the average (mean) value or not.

Three possible scenarios with Standard deviation equation is

  1. If there is a higher standard deviation, then there is more variation in the data, and It indicates the mean or average value is less accurate.
  2. If the standard deviation is equal to 0, then it indicates that every value in the dataset is exactly equal to the mean or average value.
  3. If the standard deviation is close to zero, then there is lower data variability, and the mean or average value is more reliable.

Standard Deviation Formula in Excel

Below is the Standard Deviation Formula in Excel :

Standard Deviation Formula

The Standard deviation formula in excel has the below-mentioned arguments:

  • number1: (Compulsory or mandatory argument) It is the first element of a population sample.
  • [number2]: (Optional argument): There are a number of arguments from 2 to 254 corresponding to a population sample.

Note: If you have already covered the entire sample data through the range in the number1 argument, then no need to enter this argument.

Note: The function in excel ignores logical values and text data in the sample.

Popular Course in this category
Sale
Excel for Finance Training (18 Courses, 7+ Projects)18 Online Courses | 7 Hands-on Projects | 85+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (4,152 ratings)
Course Price

View Course

Related Courses
Excel Data Analysis Training (15 Courses, 8+ Projects)Excel for Marketing Training (8 Courses, 13+ Projects)

Excel STDEV function can accept up to 255 arguments where it can be represented by either named ranges or numbers or arrays or references to cells containing numbers.

In Excel 2016, if we type =std or =dstd, 8 types of Standard Deviation Formulas appear.

Standard Deviation Formulas appear

Here, the 8 types of Standard Deviation are categorized under two groups.

a) S, STDEVA, STDEV, DSTDEV will come under Sample.

Whereas b) STDEV.P, STDEVP, STDEVPA, DSTDEVP will come under Population.

The main difference between sample and population is

Population (STDEV.P): Where “P” stands for “Population”, It includes all the elements from a data set in Population (N).

Sample (STDEV.S): Where “S” stands for “Sample”, Only the sample of the data set is considered from an entire data set (N-1).

Note: Here, Sample means only a few elements are taken out from a large population.

The selection of standard deviation formula for a particular task is based on the logical or text values present in the datasets. The below-mentioned table will help you out.

Standard Deviation will help you out

How to Use Standard Deviation in Excel?

It is very simple and easy to use. Let us understand the working of Standard Deviation in Excel by some Standard Deviation Formula example.

You can download this Standard Deviation Excel Template here – Standard Deviation Excel Template

Example #1 – Calculation of Standard Deviation for Height Data

In the below-mentioned table, it contains three columns, Serial number in column B (B8 to B20), Name in column C (C8 to C20) & Height of person in column D (D8 to D20).

Standard Deviation Example 1-1

I need to find out the Standard deviation of the Height of a person.

Prior to the calculation of Standard deviation in excel, we need to calculate the sum & mean (Average) values for the datasets.

Where the sum value is calculated with the help of the sum formula, i.e. =SUM (D8:D20) in cell G10.

SUM (D8:D20)

Result is :

SUM (D8:D20) Result

And Mean (Average) is calculated with the help of the Average formula, i.e. =AVERAGE (D8:D20) in cell G11.

AVERAGE (D8:D20)

Result is :

AVERAGE (D8:D20) Result

Let’s apply the Standard deviation function in cell “G14”. Select the cell “G14” where the Standard deviation function needs to be applied.

Standard Deviation Example 1-6

Click the insert function button (fx) under the formula toolbar; a dialog box will appear, type the keyword “Standard deviation” in the search for a function box; 6 types of Standard Deviation Formulas will appear in select a function box.

Standard Deviation Example 1-7

Here we are calculating Standard deviation only for the sample of the data set, which is taken out from a large population; therefore, we need to select either of them, i.e. STDEV.S or STDEV. Let’s STDEV.S (for a sample) from the Statistical category. Double click on STDEV.S in excel.

STDEV.S in excel

A dialog box appears where arguments for the Standard deviation function need to be filled or entered, i.e. =STDEV.S (number1, [number2], …)

STDEV.S in excel result

=STDEV.S(D8:D20) Here, the Height data is present in the range D8:D20.

To enter the Number 1 argument, click inside cell D8 and you’ll see the cell selected, then Select the cells till D20. So that column range will get selected, i.e. D8:D20.

[number2] Here, we have already covered the entire sample data through a range in the number1 argument, therefore no need to enter this argument. Click ok after entering Standard deviation arguments.

Standard Deviation Example 1-10

=STDEV.S(D8:D20), i.e. returns the Standard deviation value 1.12 as a result.

Standard Deviation Example 1-11

A standard deviation value of 1.12 indicates that most of the people in the group would be within the height range of 174.61 (with the standard deviation of +1.12 or -1.12)

Here, the standard deviation is close to zero; therefore, it indicates lower data variability and a more reliable mean or average value.

Note: When we apply the formula to larger datasets, we will see the bigger difference.

Things to Remember

  • Number Arguments must contain at least two or more numeric values to calculate Standard Deviation in excel.
  • In most of the cases, we use the S formula to calculate standard deviation in excel because we only consider the sample of the data set from an entire data set (N-1).
  • #DIV/0! error Occurs if less than two numeric values in the number argument of Standard Deviation (S) function.

Standard Deviation Example Error

  • #VALUE error occurs if any of the given values in number argument are text values in S & STDEV.P. (Note: If Standard Deviation function is not able to interpret text value as numeric values, then #VALUE error)
  • S & STDEV.P function can be applied to multiple ranges or groups.

Recommended Articles

This has been a guide to Standard Deviation in Excel. Here we discuss the Standard Deviation Formula in excel and how to use the Standard Deviation in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. SUM Function in Excel
  2. Excel AVERAGE Function
  3. SUMIF in Excel
  4. Excel LARGE Function

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel for Finance Course Certification
  • Excel Data Analysis Training
  • Excel for Marketing Training
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

Download Standard Deviation Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - Excel for Finance Course Certification Learn More