You can use the following formula to calculate a standard deviation of a range in Excel and ignore any cells that have a value of zero:
=STDEV(IF(A2:A13>0,A2:A13))
This particular formula will calculate the standard deviation of values in the range A2:A13 and ignore any cells that have a value of zero.
The following example shows how to use this formula in practice.
Related: Why is Standard Deviation Important?
Example: Calculate Standard Deviation and Ignore Zero in Excel
Suppose we have the following list of values in Excel:
Suppose we type the following formula into cell C2 to calculate the standard deviation of values in column A:
=STDEV(A2:A13)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 4.097.
This represents the standard deviation of all values in column A.
However, we could instead use the following formula to only calculate the standard deviation of values in column A for the cells that are not equal to zero:
=STDEV(IF(A2:A13>0,A2:A13))
The following screenshot shows how to use this formula in practice:
The formula returns a value of 2.976.
This represents the standard deviation of all values in column A that are not equal to zero.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate a Five Number Summary in Excel
How to Calculate the Mean and Standard Deviation in Excel
How to Calculate the Interquartile Range (IQR) in Excel
