How to Create Standard Deviation Graph in Excel

The bell curve or standard deviation graph is used to visualise the spread of data. Excel is powerful tool to create graphs and visualise data and it can be used to create the bell graph.

The standard deviation tells how much the data is clustered around the mean of the data. Lower standard deviation tells that the data is less spread and higher standard deviation tells that data is more spread.
002
Example :
Let’s say, I have productivity data of my 77 employees. Now to make the Standard Deviation graph, we will need probability distribution of each number in data. To calculate probability distribution in excel, we will need mean and standard deviation.
005
Preparing Data For Standard Deviation Chart (Graph) or say Bell Curve

Now, to plot a bell graph or say standard deviation chart of this, we first need to calculated the Mean of data, and standard deviation in excel.

To calculate mean, use AVERAGE function.

=AVERAGE(A2:A78)

To calculate standard deviation of entire population use STDEV.P

=STDEV.P(A2:A78)

006
Now, since we have, mean and Standard Deviation, we can calculate Normal distribution.
Use NORM.DIST function.

=NORM.DIST(A2,$D$2,$E$2,0)

Copy this formula down the cells.
Since we need to calculate probability distribution of each employee, we give absolute reference of A2 and we lock MEAN and STANDARD DEVIATION.
007
Plotting Standard Deviation Chart in Excel
Now since we have every ingredient for preparing our Deviation curve, we will use excel charts to get the bell curve.
Follow these steps:

  1. Make sure the data is sorted ascendingly.
  2. Select all data, productivity and probability distribution.
  3. Go to Insert-> Charts->Scattered Charts->Scattered Chart with Smooth Lines

And you will have the bell curve or say standard deviation chart.
008
Since we have a large standard deviation, the standard deviation is wider. If we reduce the standard deviation, the bell curve will be more lean toward the mean of data. You can see that, when we reduce the standard deviation, the curve get more lean.
009
So yeah guys, using this method, you can easily create a bell curve or standard deviation graph/chart in excel. The standard deviation curve will be slim if SD is low, higher standard deviation value will lead to wider bell curve.

Related Data:

How to Use STDEV Function in Excel

How to Use STDEV.P Function in Excel

Regressions Analysis in Excel

How To Calculate MODE

How To Calculate Mean

 

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube