A step-by-step guide on how to calculate standard deviation in excel.
Microsoft has always made sure that users find their products easy to use and engage in. One such product widely used around the globe is Microsoft excel. It is software that allows users to create spreadsheets and graphs, perform complex calculations, and efficiently analyze huge datasets.
We can organize spreadsheets with the help of a huge variety of in-built formulae and functions on here.
While many, many of us use only the simplest of its features, figuring out how to use some advanced features will make your life a lot simpler!
Here is one such guide for you, explaining how to calculate standard deviation in excel.
Other Uses of Microsoft Excel:
Microsoft Excel is most widely known as one of the best accounting tools. Of course, it is true, and of course, Excel has earned praise. But there is so much more to it!
You can use excel for data entry and data management. You can plan your schedule with excel easily, so it makes time management and task management a lot easier.
Excel is the most popular tool in the corporate world at this moment. Companies from small scale to huge business adventures use excel to keep track of office work. Finance is one such domain where the entire process of planning budgets, making finance-based decisions, approximations, and predictions are based on spreadsheets.
Excel is used in most workspaces for business management. The tool is so versatile that it also allows programming. What an excel sheet can do is truly known when some of the most repetitive and annoying tasks are made simple, with just one click.
The best part of Microsoft excel is, without any doubt, its gigantic amount of shortcuts. These shortcuts are the reason behind its top-of-its-class functionality. Some people even use it to calculate and predict future daily household expenses, create reports for fun, and learn the software.
Now that you are familiar with the broad domain in which Microsoft excel works let us take you through some simple formulae and tricks.
Calculate standard deviation:
Unlike Pythagoras’ theorem, the standard deviation is used in real life. The formula is used in almost all fields that deal with data points.
Standard deviation provides us with a broader understanding of datasets. It is used in healthcare, marketing, weather forecasting, and grading test scores on a curve.
What is the Standard deviation:
Standard deviation is a simple term used to describe how dispersed the data points are in accordance with the mean of the data.
For example, when we find the standard deviation below, we can say that data points are very close to the mean value of the data set. And when we say that the standard deviation is high, we mean that the data points are more widely spread in the domain.
Standard deviation is also used in psychology to explain the deviation of a set of values from the mean scores. The best way of understanding what standard deviation is through graphs.
How to calculate standard deviation:
First of all, we need to find the data set’s mean. To find the mean, you must add all the scores in the sample data and divide it by the total number of scores.
Now, you need to subtract the mean value we calculated from each score to calculate its deviation from the mean.
Now, multiply each deviation from the mean value by itself to get the square of the deviation.
Your next step would be to add up all the squared terms.
The variance:
For calculating the variance, divide the sum of squares by n-1.
Standard deviation is the square root of the variance in the given data. SO, take the square root of the variance, and you have successfully calculated the standard deviation!
A solved example:
Suppose we wish to calculate the standard deviation for the data 10, 12, 23, 23, 16,23, 21, 16.
We assume that the given data points are of a population standard deviation and start the calculations by finding the sum of all data points.
Here, the sum is 144, and there are 8 entries in the given data sets. It follows that the average value is 18.
Then, we try to find the deviation by subtracting the mean from each value.
Now, the variance is 24. We find the standard deviation by taking the square root of the variance.
We hope you have now understood the process of calculating standard deviation.
What does standard deviation tell you:
In statistics, variability is measured mainly in range, variance, and standard deviation. Out of these, the standard deviation is really important because it depicts how far a particular score lies from the mean value.
How to calculate standard deviation in excel:
Standard deviation calculation is straightforward in excel, and you need to know the right command. An inbuilt standard deviation formula will provide you with a ready-made answer.
For calculating the sample standard deviation, we use STDEV.S, STDEVA and STDEV functions. And for calculating the standard deviation for an entire population, we use STDEV.P, STDEVPA and STDEVP functions.
Use of STDEV.S function:
We should keep in mind that the STDEV.S function considers only numerical values. All text and logical values are ignored to find the answer.
The function’s syntax is as follows: STDEV.S(number 1, number 2,…..). In this format, excel calculates the standard deviation for you.
Different STDEV functions:
STDEV.S function is used when the data that we are entering is numeric. This function is built such that it ignores the text and logical values.
You want to use the STDEVA function when you want to include text and logical values in the data set as well. The software will interpret the text as zero. As far as the logical values are concerned, TRUE is assigned the value of 1, and FALSE is assigned the value of 0.
STDEV function is the function that was used in Microsoft excel versions before 2010. They have replaced it with the STDEV.S function now that it is outdated. You will still find it included in newer versions for compatibility.
You are free to assume that you will find STDEV.S function easily. However, don’t mind if the STDEV function appears somewhere out of the blue! It is, in its essence, the twin of STDEV.S function.
Steps to follow:
1) Open Microsoft Excel on your computer.
2) Input your data, enter each value in a single cell.
3) In the cell where you want the standard deviation to be calculated, enter an ‘= ‘ sign.
4) You will see a list of functions used in Excel.
5) Now, enter the STDEV function and select the range of cells you wish to calculate standard deviation in Excel.
6) When you press enter, it will show the result in the formula bar.
A step by step video guide:
For better reference, here is a step-by-step video guide that will help you calculate standard deviation in Excel.
Some other excel related tips you might find useful:
To keep learning excel and keep your career growth, you must be up to date with the latest technology trends and innovations.
We think these resources will be beneficial for that. Check them out right now!
- How to Spell Check in Excel: Best Guide {2022}
- How to Install Microsoft Office for Mac: Best 101 Guide
- How to Speed Up Computer for Better Performance: A Detailed 101 Guide
The bottom line:
We are sure that you have got the hang of it by now. If you have any queries, run into unexpected problems or face errors, please let us know. We will get back to you with easy solutions as soon as possible.
After reading this article, do you feel like you are getting a bit tech-savvy? Do you think you can now use Microsoft Excel to its full potential? Would you like more articles like this from us?
Please let us know in the comment section below!
