Microsoft Excel Standard Deviation Functions

If you’re reading this you probably already know that the standard deviation is a measurement of confidence, variability or diversity in the average (be it the mean or another expected value) used in statistics and probability theory.

I’m not going to go into any more detail on what the standard deviation is, but I will show you how you can calculate it in Excel quickly and easily.

We use it in my book club to indicate the polarity of our scores for each book. Whilst some books might score high overall they may also polarise the group, with some LOVING the book and others HATING it. A high standard deviation indicates there is a wide range of scores, vs a low standard deviation indicating most were in agreement.

Here’s an example of how we use the Standard Deviation function to analyse our book scores:

Note: These are hypothetical scores generated using the RAND function, for hypothetical book club members.

Excel Standard Deviation Formulas

For a sample population use:

=STDEV(number1,[number2]….)

For an entire population use:

=STDEVP(number1,[number2]….)

You can either enter all the numbers separated by commas or you can enter a cell range (e.g. $D$3:$D$500) or range name.

Using row 4 in the image above our formula in cell L4 would read:

=STDEVP(B4:J4)

What can we get from this information?

We can see that on average the top scoring book was The Kite Runner with a score of 6.22, but it was also the most polarising book with a standard deviation of 3.76.

Plainsong tied for second, and with a standard deviation of just 1.20 most of the readers were in agreement. You could even argue that this was the best book since it pleased most of the book club members!

Shutter Island was one of the lowest scoring books with an average of just 4.89, and with a standard deviation of 0.99 most were in agreement.

Other tricks used in this table

In the Average and Std Dev columns (K&L) I have used conditional formatting to help with the readability of the table.

Excel shapes were used to create the Book/Scorer labels in cell A3.

A table was inserted and then converted to a range to create the shaded bands formatting.