This article describes how to determine the standard deviation of a record in Microsoft Excel.
What is the Standard deviation?
The standard deviation is a measure of the extent of variation or spread of a set of values. A low standard deviation indicates that the values tend to be close to the mean (also known as the expected value) of the whole. In contrast, a high standard deviation indicates that the values are spread over a broader range.
It can be abbreviated as SD and is more than standard cases; it is represented in mathematical texts formula and equations by the lowercase Greek letter sigma σ in for the standard deviation of a population or the Latin letter s for the standard deviation sample.
How to Calculate Standard Deviation in Excel
Follow the steps below to calculate the standard deviation in your Microsoft excel sheet.
- Open Microsoft Excel.
- Click or double-click the Microsoft Excel application icon, which looks like a white “X” on a dark green background. The Excel start page opens.
- Click Empty Workbook. It’s in the top left corner of the Excel homepage.
- Enter the values you want to use.
- Select a column in which you want to enter your data, and then enter each data value in individual cells in that column.
- For example, if you select the column “A” as the field for entering your data, you can enter a number in cell A1, cell A2, cell A3, and so on.
- Click an empty cell. It must be the cell in which you want to display the standard deviation value. This selects the cell.
- Enter the standard deviation formula. The formula you enter in the blank cell is = STDEV.P (), where “P” means “population.” The population standard deviation takes into account all of your data points (N).
- If you want to find the standard deviation “Sample,” you must enter = STDEV.S () here. The standard deviation of the sample takes into account a value that is smaller than the number of your data points (N-1).
- Add your range of values. In parentheses, type the letter and number of the cell that contains your first data, enter a colon (:), and type the letter and number of the last data cell.
For example, if you enter your data in column “A” of lines 1 to 10, = STDEV.P (A1: A10) is entered here.
- Press ↵ Enter. This prompts Excel to run the formula, which shows the standard deviation of your selected cells in the formula cell.
Multiple Formulas in Excel, why?
As you may have noticed, Excel has several functions for calculating the standard deviation. I used Excel 2007 for this tutorial. Microsoft engineers have asked some meaningful statisticians to help improve the speed of the functions and the accuracy for larger amounts of Data in excel 2010.
- STDEV.P calculates the standard deviation based on the total population (N). This function replaces the old STDEVP function.
- STDEV.S calculates the standard deviation using a sample (N-1). This function replaces the old STDEV function.
The difference between the two calculation methods concerns the sample and thus the divisor. If you calculate the standard deviation with the entire population, the divisor is N (with N, number of elements). When you calculate for a sample, the divisor is N-1.
Finally, the Mean and Standard Deviation
Follow the procedures below when you are looking for the average in your data.
- Enter the results in one of the columns in the Excel spreadsheet.
- After entering the data, place the cursor where you want the average to appear and click the mouse.
- On the FORMULAS tab, choose Insert Function (fx).
- A dialog box appears. In the Statistics category, select the AVERAGE option and click OK.
- (Note: If you want the median, choose MEDIAN.
- If you want the mode, choose MODE.SNGL. Excel offers only one mode. If a record had more than one mode, Excel would only display ‘a’.)
- Enter the range of cells for your number list in the Number 1 field. For example, if your data is in column A of rows 1 through 13, enter A1: A13.
- Instead of entering the range, you can also move the cursor to the beginning of the partition set you want to use and click and drag the cursor over it.
- After entering the range for your list, click OK at the bottom of the dialog box.
- The average (average) from the list is displayed in the cell you selected.
Finding the Standard Deviation
Using the above formulae in finding the standard device, follow the procedures below.
- Place the cursor where you want the standard deviation to appear and click the mouse.
- On the FORMULAS tab, choose Insert Function (fx).
- A dialog box appears.
- Select STDEV.S (for an example) in the Statistics category. (Note: If your data comes from a population, click on STDEV.P).
- After making your selection, click OK at the bottom of the dialog box.
- Enter the range of cells for your number list in the Number 1 field. For example, if your data is in column A of rows 1 through 13, enter A1: A13.
- Instead of entering the range, you can also move the cursor to the beginning of the partition set you want to use and click and drag the cursor over it.
- After entering the range for your list, click OK at the bottom of the dialog box.
- The standard deviation of the list is displayed in the cell you selected.