How to Calculate Population Standard Deviation in Excel

In this article, we will learn to calculate population standard deviation in Excel. We can do it very quickly by following the methods discussed in this tutorial. Here, we will demonstrate the steps with screenshots so that you can understand them easily. Let’s see the methods below.

Download Practice Workbook

Download the practice workbook from here.

Introduction to Standard Deviation

Standard deviation calculates the variance of a list of numbers from their mean. It can be classified into two categories, sample and population standard deviations. There is a slight difference between these two types of standard deviations.

Sample Standard Deviation: It does not include all the numbers in the list. The basic formula for determining sample standard deviation is:

S = √Σ (x – ⲙ)2 / (n - 1)

Here,

x denotes any number or value in the list.

ⲙ is the mean or average of the numbers.

n denotes the total number of values in the list.

Population Standard Deviation: It includes all the numbers in the calculation. For example, a teacher always uses population standard deviation to summarize the mark of the students. The following formula is used to calculate the population standard deviation:

S = √Σ (x – ⲙ)2 / n

Here,

x indicates any number or value in the list.

ⲙ denotes the mean or average of the numbers.

n is the total number of values in the list.

Built-In Excel Functions for Population Standard Deviation

There are 3 built-in functions in Excel which calculate the population standard deviation at once. These functions also use the basic formula for determining the standard deviation. But here the basic formula is in-built into these functions. So, we do not need to insert the long formula if we use the built-in functions. The functions are below.

STDEVP Function: This is the oldest function for calculating population standard deviation. Though it is now replaced with some new functions, it is available in the modern versions of Microsoft Excel. The syntax of this function is:

STDEV.P Function: It is the new version of the STDEVP function that provides higher precision. We can use this formula in the modern version of Microsoft Excel (2010 and later versions). The syntax is below:

The STDEVP and STDEV.P functions do not evaluate the text and logical values in references. But, these functions evaluate the text or logical values if they are in the argument list.

STDEVPA Function: This function computes the population standard deviation considering text and logical values. The syntax of this function is:

2 Easy Ways to Calculate Population Standard Deviation in Excel

Suppose, we have a dataset (B4:D9) in Excel that contains the Name of some Students, their student IDs, and their Marks in the Exam. Let’s say, we need to calculate the population standard deviation of the Marks (D5:D9). Here, we will discuss 2 easy ways to do so. So, without further delay, let’s begin.

1. Calculate Population Standard Deviation Manually in Excel

In this method, we will calculate the population standard deviation of the Marks (D5:D9) in Excel using the basic formula. We have already discussed the basic formula in the introduction part. Here, we will use the dataset (B4:D9) below where we have denoted the Mark column as x in the formula. The steps are below.

Steps:

• First of all, we need to calculate the Average Mark.
• To calculate the Average Mark, insert the following formula in cell D11:

• After that, press the Enter key.
• Therefore, we will find the Average Mark in cell D11 by using the AVERAGE function.

• Afterward, create a column (E4:E9) with (x – ⲙ) as the column heading.

• At this time, to find the difference between the Mark of the first student (cell D5) and the Average Mark (cell D11), type the following formula in cell E5:

• Next, hit the Enter key.
• Consequently, you will find the difference in cell E5 (see the screenshot below).

• In order to autofill the rest of the cells (E6:E9), move your cursor to the bottom-right corner of cell E5.
• Momentarily, you will see a plus sign (+) that is called the fill handle.

• Thereupon, double-click on the fill handle.
•  Hence, you will get all the values of (x – ⲙ).
• See the picture below.

• Later, add another column (F4:F9) for determining (x – ⲙ)².

• Calculate (x – ⲙ)² for the first student by entering the formula below in cell F5:

• Hit Enter to get the output.

• Eventually, double-click on the plus sign to autofill the rest of the cells (F6:F9).

• Accordingly, use the SUM function to get the summation of the range F5:F9, in cell D12.
• See the formula below.

• After pressing the Enter button, you will find the output.

• Finally, find the Population Std. Deviation by applying the formula with the SQRT function and the COUNT function.
• We used the following formula in cell D13:

• Lastly, hit the Enter button to get the final output.
• See the final result in cell D13 of the figure below.

🔎 How Does the Formula Work?

Read More: How to Calculate Average and Standard Deviation in Excel

2. Use Built-In Excel Functions to Determine Population Standard Deviation

In this approach, we will show you the use of three built-in Excel functions for determining population standard deviation. We already discussed these functions at the beginning of this tutorial. So, let’s see the process below to apply the functions.

2.1 Insert STDEVP Function

Here, we will use the STDEVP function in Excel to find the population standard deviation. For this, we will use the same dataset (B4:D9) that we used in the previous method. Here, we want to keep the result in cell D11. See the following steps.

Steps:

• Firstly, go to cell D11.
• Then, determine the population standard deviation of the Marks by entering the formula below in cell D11:

• After that, hit the Enter button.
• In this way, you will get the desired result in cell D11 (see screenshot).

2.2 Assign STDEV.P Function

Now, we will apply the STDEV.P function in Excel to calculate the population standard deviation of the Marks in cell D11. Here, you will use the same dataset as the previous methods. The steps are below.

Steps:

• First, activate cell D11 by clicking on it.

• Secondly, type the following formula in the cell to determine the population standard deviation:

• Thirdly, press Enter.
• Thus, you will find the result in cell D11.
• See the following figure.

2.3 Enter STDEVPA Function

This approach will guide you to use the STDEVPA function for calculating the population standard deviation. For this, we will use the same dataset as the previous methods. See the following steps:

Steps:

• To begin, select cell D11.

• Next, find the population standard deviation of the Marks by using the formula below in cell D11:

• In the end, press the Enter button on the keyboard.
• Consequently, you will find the desired output in cell D11 (see screenshot).

Read More: How to Calculate Mean Variance and Standard Deviation in Excel

Conclusion

I hope the above tutorial will be helpful for you to calculate the population standard deviation in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.

Related Articles

• How to Include Standard Deviation in Excel Radar Chart
• Plot Normal Distribution in Excel with Mean and Standard Deviation
• How to Find Outliers with Standard Deviation in Excel (with Quick Steps)
• Create a Bell Curve with Mean and Standard Deviation in Excel
• How to Calculate Standard Deviation of a Frequency Distribution in Excel
• Generate Random Number with Mean and Standard Deviation in Excel