How to Calculate Standard Deviation in Excel

-

|

Introduction

Standard Deviation is a measure of how dispersed values are from their mean (average). You can calculate standard deviation in Excel with STDEV, STDEVP, STDEV.S or STDEV.P Functions.

In this guide you will learn how to calculate standard deviation in Excel based on a sample of a population. The guide also covers how to calculate standard deviation in Excel based on an entire population.

How to Calculate Sample Standard Deviation in Excel

STDEV and STDEV.S (newer version of STDEV) functions are used to calculate standard deviation based on sample of a population.

When you use any of these functions, Excel assumes that its inputs are a sample of the population, not the entire population.

To calculate standard deviation in Excel based on the entire population it is better to use the STDEVP function.

This section covers how to use STDEV, STDEV.S STDEVP and STDEV.P functions.

Advertisement

How to Calculate Sample Standard Deviation in Excel with STDEV and STDEV.S Functions

How to Calculate Sample Standard Deviation in Excel with STDEV Function

The syntax of STDEV and STDEV.S functions are:

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

Number1 (required) – represents the first number in a sample of a population
Number2 (optional) – represents a number between 2 to 255 in a sample of a population

Important Notes

  1. The formulas assume that the data supplied are samples of the population.
  2. If your data represents an entire population use STDEVP and STDEV.P functions instead. These are covered in the next section
  3. STDEV and STDEV.S use the “n-1” method to calculate the standard deviation
  4. Number1, number2, etc can either be numbers or names, arrays, or references that contain numbers.
  5. If any of the values included in STDEV or STDEV.S formula arguments reference a cell that contains text, those cells are ignored.
  6. If your sample data include logical values and text representations of numbers, use the STDEVA function instead.
  7. STDEV and STDEV.S use the formula below to calculate standard deviation in Excel:
Excel Standard Deviation Formula
image from Microsoft

The formula above is the square root (SQRT) of the variance (VAR) of the sample.

In the formula, x is the sample Mean AVERAGE(number1,number2,…) while n is the sample size.

To calculate Sample standard deviation for the data shown in cells A2 to A11 use the formula

=STDEV(A2:A11)

The result is 27.46391572 (cell B14).

To use the newer function, STDEV.S use this formula

STDEV.S(A2:A11)

How to Calculate Standard Deviation in Excel with STDEVP and STDEV.S Functions

As I mentioned in the last section STDEV and STDEV.S functions assume that the input data are samples. To calculate standard deviation for an entire population use STDEVP and STDEV.S.

Using the data shown in the last section, to calculate standard deviation for the whole population, use the formulas below:

=STDEVP(A2:A11)
=STDEV.P(A2:A11)

The result of both formula is 26.05455814.

How to Calculate Mean and Standard Deviation in Excel

I said earlier that the standard deviation functions use the formula below:

Excel Standard Deviation Formula

In this section I will demonstrate how to compute standard deviation from the mean (Average) of a sample data using the formula shown above.

This section will reference the worksheet shown below. Column A contains the sample data.

Advertisement
Excel Standard Deviation Formula

To calculate standard deviation using the formula above, follow the steps below:

  • Calculate the Mean of the sample with the formula below:
=AVERAGE(A2:A11)
  • Next, calculate the dispersion of each sample data in column A from the above Mean. This is shown in cells B2 to B11 in the image below:
  • Then square each of the dispersion values. This was done in column C (See the the image above).
  • The squared values are then summed in cell C14.
  • Next, count the sample data and subtract 1 from the total. The formula used is:
=COUNT(A2:A11)-1

and the result is 9, shown in Cell C15.

  • Finally, take the square root of C14/C15 (C14 divided C15) as shown below:
=SQRT(C14/C15)

The result is 27.46392. The exact value returned by the STDEV and STDEV.S functions.

This last section will be useful for college students. If you are required to calculate standard deviation but show the steps, use the steps in this section.

Conclusion

Standard deviation calculation using Excel is very straight forward. In this guide I showed various ways you can calculate standard deviation in Excel.

In the last section you saw how to perform the same calculation from the Mean of a sample.

I hope this guide has been helpful. Have any question or comment? Use the “Leave a Reply” form at the end of this page.

Other Helpful Guides

Additional Resources and References

LEAVE A REPLY

Please enter your comment!
Please enter your name here

FEATURED POSTS

network discovery keeps turning off server 2016

How to Fix Network Discovery If it Keeps Turning Off in Server 2016

Does network discovery keep turning off in your Windows server 2016? It is likely that one of its dependent services is not...
how to install windows 10 1903 update manually

How to Install Windows 10 1903 Update Manually

Windows 10 1903 Update was released in May, 2019. But some users are not yet offered the update via automatic update. The...
spotify web player not working

Spotify Web Player Not Working [Fixed]

Introduction Spotify Web Player may stop working for you with the following error messages: "Spotify Web Player an Error...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
DHCP Relay agent

DHCP Relay Agent: Configuration in Windows Server 2016

What is a DHCP Relay Agent? A DHCP Relay Agent allows DHCP clients in a different network subnet to...

TRENDING POSTS

Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...
Spotify No Longer Supports this Version of Microsoft Edge

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...
Windows 10 Won't Boot

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

BEST OF ITECHGUIDES

Hotmail email (outlook.live.com)

Hotmail Email (Outlook.com Email): Your Definitive Guide

Introduction I suppose the first question on your mind is, "what happened to Hotmail email?" This is because when...
powershell help

Get-Help Command: Syntax, Parameters, Examples

Introduction Don't know how to use a PowerShell cmdlet? PowerShell Get-Help cmdlet will give you information about any powerShell...
RAID 50

RAID 50 vs RAID 10: Benefits and Disadvantages Compared

What is RAID 50 vs RAID 10? RAID 50 stripes two RAID 5 arrays while RAID 10 stripes two...

Spotify Web Player: Your Definitive Guide

What is Spotify Web Player? Spotify Web Player is a browser-based player that allows you to stream Spotify via...
dism /online vs /image

The Difference Between DISM /Online and DISM /Image Commands

What is DISM /Online vs /Image? In comparing DISM /online vs /image, while DISM /online targets the running operating...

RECENT POSTS

disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
bootrec /fixboot access is denied

How to Fix BootRec /FixBoot Access is Denied Error in Windows 10

Introduction BootRec /FixBoot Access is Denied Error in Windows 10? This error is likely caused by corrupt EFI directory.
Reset Windows 10

How to Reset Windows 10 (2 Methods)

Introduction If your Windows 10 is broken, one available way to fix it is to use Windows 10 reset....
system restore windows 10

How to Enable and Use System Restore in Windows 10

Introduction System restore in windows 10 is a very important tool that is probably ignored by most users. But...
windows has stopped this device code 43

How to Fix Windows Has Stopped this Device Code 43 Error

Introduction Sometimes you may receive "Windows has stopped this device code 43" error. This error is likely to come...
Windows could not automatically detect this network's proxy settings

How to Fix “Windows Could not Automatically Detect this Network’s Proxy Settings”

Introduction You suddenly lose the ability to connect to the internet. Then you run network troubleshooter and it returns...
printer offline

5 Ways to Restore Your Printer Online If Status is Offline

Introduction Is your printer offline? Setting it online is very simple but sometimes it is more than just setting...

How to Fix “BootMgr is Missing” Error in Windows 10

Introduction If you receive Fix "BootMgr is missing" Error in Windows 10, the default response is panic! But you...
windows 10 search featured

How to Fix Windows 10 Search If it Stops Working

Introduction Are you having troubles with Windows 10 search? Apparently, it is a fairly common problem with a number...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...

MUST READ

spotify subscription

How to Subscribe to Spotify Premium

Introduction Paid Spotify subscription (premium) has a lot more benefit compared to the free one. Some of the benefits...

Powershell If Else Explained: Syntax and Examples

Introduction PowerShell If Else statement runs a command if a specified condition is met. The Else part of PowerShell...
Dual WhatsApp account in one phone

How to Use Dual WhatsApp Accounts in One Android Phone

Introduction Most Android phones now support dual SIM. If your Android phone has 2 SIM cards you can configure...
powershell.exe -command

Powershell.exe Command: Syntax, Parameters and Examples

Introduction You may be wondering why write on Powershell.exe Command. Are there special commands for Powershell.exe? Yes! When you...

How to Alphabetize (Sort Lists or Tables) in Microsoft Word

Introduction You can alphabetize (sort) a list or table in Microsoft word by number, date or text. You can...

By using this website you agree to accept our Privacy Policy and Terms & Conditions