Standard deviation is a popular term used in business, academia, manufacturing, finance, medicine, etc. It refers to a measure of data dispersion or variation in relation to the mean. This is very useful when it comes to understanding how different a given data is. You can easily calculate standard deviation in Excel with the help of inbuilt formulas.
In this article, I will guide you on how to calculate standard deviation in Excel.
What is standard Deviation?
Standard deviation is a statistical measure that quantifies the amount of variation or dispersion within a dataset. It reveals how much individual data points deviate from the mean or average value. A higher standard deviation indicates that the data points are more spread out from the mean, signifying greater variability in the dataset.
Conversely, a lower standard deviation suggests that the data points are closer to the mean, indicating less variability. Standard deviation is widely used in various fields, such as finance, research, and quality control, to understand the distribution of data and make informed decisions based on the data’s spread and reliability.
Standard Deviation Formulas in Excel
The formula for calculating Standard deviation is =STDEV. However, once you start typing the formula, Excel will give you six options to choose from. These are:
STDEV.P: It calculates the standard deviation for a given population, showing the degree of dispersion or variability of data points from the population mean.
STDEV.S: This formula calculates the standard deviation for a sample dataset, indicating the amount of variation or dispersion of data points from the sample mean.
STDEVA: Calculates the standard deviation for a sample dataset. Therefore it indicates the variation or dispersion of data points from the sample mean while considering text representations of numbers.
STDEVPA: The formula calculates the standard deviation of a population’s data, measuring variation and accounting for number representation.
STDEV: In Excel, the STDEV function calculates the standard deviation of a sample dataset. This measures the extent to which data points deviate from the sample mean. A higher value suggests more variation, while a lower value implies less variation.
STDEVP: This Formula calculates the standard deviation for a population dataset, showing the dispersion of data points from the population mean, considering all data in the dataset.
How to Calculate Standard Deviation In Excel
Here is the step-by-step guide that you can follow to help you calculate standard deviation in Excel
1. Choose a formula to use
The most common formula used to calculate standard deviation in Excel is STDEV.S. However, if you need to calculate specialized standard deviation, you can choose any other formula from the list above.
2. Type your dataset
Use the following syntax as a guide to help you calculate the standard deviation
=STDEV.S([Number1]:[Number2])
Number1: refers to the cell containing the first value from the sample population
Numert: Refers to the cell containing the last value of the sample population
For example, If the data is in cells B2 to B11, then the formula will be
=STDEV.S(B2:B11)
As you can see from the image above, the standard deviation of the student marks is 9.60613. This means most students are within the range of 64-9.60613 and 64+9.60613.
How to plot a Standard Deviation Graph in Excel
Excel allows users to present the data graphically by the use of charts and graphs. You can create a standard deviation graph using Excel. Follow these simple steps.
1. Calculate the mean of the data
To calculate the mean score in Excel, you will need to use the =average() formula.
2. Calculate Standard Deviation using the STDEV.S formula
3. Calculate Normal distribution using NORM.DIST formula
Since we have used the STDEV.S formula, which calculates the standard deviation of a sample population, then we need to calculate the normal distribution.
X refers to the data point, which is B2 while mean stands for the average we calculated, Standard_dev is the standard deviation, and finally on cumulative type false.
Not that we have made the average and standard deviation cells absolute reference so that we can apply the formula to the rest of the cells easily.
Therefore the final formula reads like this =NORM.DIST(B2,$E$2,$E$3,FALSE)
4. Apply the Norm.dist formula to the rest of the cells
You can apply the formula to the rest of the cells quickly by double-clicking or by just dragging down the fill handle.
5. Insert the standard Deviation Excel Graph
Follow these simple steps to insert the standard deviation chart
- First, highlight the marks and the normal distribution data.
- Navigate to the insert tab>Charts and select Scatter with smooth lines
That’s it, you have successfully created the standard deviation graph using Average, standard deviation and normal distribution
