Standard deviation for a sample is one of two major standard deviation functions MS Excel lets you calculate for your charts. It represents the standard deviation from the mean for a selected sample of data. By using this function, you can easily calculate how much a certain subset of data deviates from the mean value. Compute the mean of your data sample. The mean is the average of the sample values. For example, if weather observations in a four-day period during the year are 52, 60, 55 and 65 degrees Fahrenheit, then the mean is 58 degrees Fahrenheit: (52 + 60 + 55 + 65)/4.
When you create a graph in Excel and your data are means, it’s a good idea to include the standard error of each mean in your graph. This gives the viewer an idea of the spread of scores around each mean.
Here’s an example of a situation where this arises. The data are (fictional) test scores for four groups of people. Each column header indicates the amount of preparation time for the eight people within the group. You can use Excel’s graphics capabilities to draw the graph. Because the independent variable is quantitative, a line graph is appropriate.
For each group, you can use AVERAGE
to calculate the mean and STDEV.S
to calculate the standard deviation. You can calculate the standard error of each mean. Select cell B12, so the formula box shows you that you calculated the standard error for Column B via this formula:
=B11/SQRT(COUNT(B2:B9))
The trick is to get each standard error into the graph. In Excel 2016 this is easy to do, and it’s different from earlier versions of Excel. Begin by selecting the graph. This causes the Design and Format tabs to appear. Select
Design | Add Chart Element | Error Bars | More Error Bars Options
In the Error Bars menu, you have to be careful. One selection is Standard Error. Avoid it. If you think this selection tells Excel to put the standard error of each mean on the graph, rest assured that Excel has absolutely no idea of what you’re talking about. For this selection, Excel calculates the standard error of the set of four means — not the standard error within each group.
More Error Bar Options is the appropriate choice. This opens the Format Error Bars panel.
In the Direction area of the panel, select the radio button next to Both, and in the End Style area, select the radio button next to Cap.
One selection in the Error Amount area is Standard Error. Avoid this one, too. It does not tell Excel to put the standard error of each mean on the graph.
Scroll down to the Error Amount area and select the radio button next to Custom. This activates the Specify Value button. Click that button to open the Custom Error Bars dialog box. With the cursor in the Positive Error Value box, select the cell range that holds the standard errors ($B$12:$E$12). Tab to the Negative Error Value box and do the same.
That Negative Error Value box might give you a little trouble. Make sure that it’s cleared of any default values before you enter the cell range.
Click OK in the Custom Error Bars dialog box and close the Format Error Bars dialog box, and the graph looks like this.
Standard error or standard deviation is an extremely handy tool when you want to gain a deeper understanding of the data that’s in front of you. It tells you how much values in a particular data set deviate from the mean value.
Also see our article How To Calculate Days Between Two Dates in Excel
There two main varieties – standard deviation for a sample and standard deviation for a population, and they’re both included in Excel. Let’s see how to go about calculating standard deviation in Excel.
Standard Deviation for a Sample
Standard deviation for a sample is one of two major standard deviation functions MS Excel lets you calculate for your charts. It represents the standard deviation from the mean for a selected sample of data.
By using this function, you can easily calculate how much a certain subset of data deviates from the mean value. Let’s say that you have a chart with the salaries of all employees in a company and you only want the data on the salaries in the IT sector. You’ll use the standard deviation for a sample or the STDEV.S function.
Standard Deviation for a Population
Standard deviation for a population is the other major standard deviation function you can calculate through MS Excel. As opposed to the standard deviation for a sample, standard deviation for a population shows the average deviation for all entries in a table. It is marked as STDEV.P in MS Excel.
So, using the same example from the previous section, you would use the STDEV.P function to calculate the deviation for all employees. Excel also lets you calculate other types of standard deviations, though these two are most commonly used. It is worth noting that STDEV.P is the same as the STDEV function.
How to Calculate Standard Deviation with Excel
Calculating standard deviation in Excel is easy and can be done in three different ways. Let’s take a closer look at each of the methods.
Method 1
This is the fastest way to calculate the standard deviation value. You can use it to get both the sample and population deviations. However, you need to know the formulas to make this method work, which is why so many people tend to avoid it.
In this case, we’re working with a ten-column chart. Here’s how to do it:
- Create or open a table in MS Excel.
- Click on the cell where you’d like the standard deviation value to be displayed.
- Next, type “=STDEV.P(C2:C11)” or “=STDEV.S(C4:C7)”. The values in the brackets denote the range of cells for which you want to calculate the standard deviation value. In this example, you want to calculate STDEV.P for cells C2 to C11 and STDEV.S for cells C4 to C7.
- Press “Enter”.
- If you want to round the result to two decimals, click on the “Home” tab.
- Click the arrow next to “General” to open the dropdown menu.
- Choose the “Number” option.
Method 2
The next method is almost as fast as the first one and doesn’t require in-depth Excel knowledge. It is great when you’re in a crunch but don’t want to mess with the formulas. Let’s see how to get the deviations without typing the formulas.
- Create or open a table in MS Excel.
- Click on the cell where the deviation result will appear.
- Next, click the “Formulas” header in the Main Menu.
- After that, click the “Insert Function” button. It is located on the left side.
- Click the arrow next to “Or select category” to open the dropdown menu.
- Select “Statistical”.
- Browse the list below and select either STDEV.P or STDEV.S
- Next, in the “Function Arguments” window, enter the range for which you want to calculate the standard deviation into the text box next to “Number1”. Going back to the Method 1 example where we were calculating the standard deviation for cells C2 to C11, you should write C2:C11.
When you calculate the standard deviation this way, you won’t need to trim the number, as it will automatically be trimmed to two decimals.
Method 3
There is also a third method, the one that involves the use of Excel’s Data Analysis toolkit. If you don’t have it, here’s how to install it.
- Click on “File”.
- Next, click on “Options”.
- Click the “Add-Ins” tab on the left side of the window.
- Click the “Go” button near the bottom of the window.
- Check the “Analysis ToolPak” box.
- Click “OK”.
With the installation complete, let’s see how to use Data Analysis to calculate Standard Deviation.
- Create or open a table in MS Excel.
- Click on the “Data” tab.
- Select “Data Analysis”.
- Select “Descriptive Statistics”.
- In the “Input Range” field, insert the range of cells you wish to include.
- Choose between the “Columns” and “Rows” radio buttons.
- Check “Labels in First Row” if there are column headers
- Pick where you want the result to appear.
- Check the “Summary Statistics” box.
- Click the “OK” button.
You will find the standard deviation for population in the output summary.
Summary
The standard error or standard deviation value can be calculated in a number of ways. Choose the method that suits you best and follow the steps laid out in this article.