We know that standard deviation is a calculation of how the values are changing with comparison or the respect to the mean or the average value. So, we represent this data in a graph. There are two deviations represented in the standard deviation graph, one positive to the mean, shown on the right-hand side of the graph. The other is negative to the mean, shown on the left-hand side of the graph. The standard deviation graph is also known as the bell curve graph in Excel.
Excel Standard Deviation Graph / Chart
The standard deviation is one of the important statistical tools which shows how the data is spread out. For example, in the stock market, how the stock price is volatile.
Typically, the standard deviation is the variation on either side of the average or means value of the data series values. We can plot the standard deviation in the Excel graph called the “bell-shaped curve.”
The bell curveBell CurveBell Curve graph portrays a normal distribution which is a type of continuous probability. It gets its name from the shape of the graph which resembles to a bell. read more is the graph commonly used to gauge the performance appraisal of company employees. All the organizations work on performance appraisal for a specific period frame. Using this performance appraisal, they reward employees in terms of salary, promotions, etc.
They gauge employees on low or non-performers, average performers, and high performers. Plating a bell-shaped graph shows the highest probability of the outcome, and the likelihood of the outcome keeps going down when the bell shapes move to either side from the center point.
For example, look at the below Excel bell shaped curve graph.
Assume you are working in a team of 50 members, and your rating is very similar to the other team members. Then, only a few employees get a higher rating, a majority will get an average rating, and a few will get a low rating. For example, if you got 8 as a rating and your team member got 7 as the rating, there would not be much difference here.
The bell-shaped curve is the best fit to gauge the employee, rate them, and then reward them accordingly to make the comparison fair.
All the higher-rating employees in the bell curve will be placed on the right-hand side of the bell curve, low-rating employees will be placed on the left-hand side of the bell curve, and average employees will be placed in the center of the bell curve.
We need two calculations to understand the Excel Standard DeviationExcel Standard DeviationThe standard deviation shows the variability of the data values from the mean (average). In Excel, the STDEV and STDEV.S calculate sample standard deviation while STDEVP and STDEV.P calculate population standard deviation. STDEV is available in Excel 2007 and the previous versions. However, STDEV.P and STDEV.S are only available in Excel 2010 and subsequent versions. read more graph or bell-shaped curve. One is the MEAN or AVERAGE of the data series, and the second is the standard deviation (SD), which shows how to spread the data series.
For example, if the average score of the students in the class is 70 and SD is 5, then students scored within either side of the mean value, i.e., 70. The first range will be 65-70, and the second range will be 70-75.
Table of contents
Standard Deviation Graph in Excel Video Explanation
How to Create a Standard Deviation Graph (Chart) in Excel?
To better understand Excel’s standard deviation graph (chart), let us consider our real-life example of a student’s marks in an examination.
We have taken 25 students’ sample data, and below are the scores of these 25 students in an examination.
The first thing we need to do is calculate the AVERAGE score of the examination. Apply the Average FormulaApply The Average FormulaAverage is the value that is used to represent the set of values of data as is the average calculated from whole data and this formula is calculated by adding all the values of the set given, denoted by summation of X and dividing it by the number of values given in set denoted by N.read more.
We got the result of 7.
Secondly, calculate the standard deviation of the data series.
The result is shown below:
Note: Since we had taken sample data, we have used STDEV.S, which is used for the sample data series.
In the B1 cell, enter the normal distribution excelNormal Distribution ExcelNORMDIST or normal distribution is an inbuilt statistical function of excel that calculates the normal distribution of a data set with mean and standard deviation provided.read more formula, i.e., NORM.DIST.
x is nothing but our data point, so select A1 cell.
MEAN is the average value we have calculated, so give a link to cell E1 and make it an absolute reference.
Next is SD to give a link to cell E2 and make it an absolute excel referenceAbsolute Excel ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more.
The next thing is cumulative. Select “FALSE” as the argument.
Now, see the result.
Drag the formula to other cells to have normal distributionNormal DistributionNormal Distribution is a bell-shaped frequency distribution curve which helps describe all the possible values a random variable can take within a given range with most of the distribution area is in the middle and few are in the tails, at the extremes. This distribution has two key parameters: the mean (µ) and the standard deviation (σ) which plays a key role in assets return calculation and in risk management strategy.read more values.
Create a standard deviation Excel graph using the below steps:
- Select the data and go to the “INSERT” tab. Then, under “Charts,” select “Scatter” chart, and prefer a “Scatter with Smooth Lines” chart.
- Now, we will have a chart like this.
- If needed, you can change the chart axis and title.
Conclusion: Our SD is 3.82, slightly higher, so our bell curve is wider. If the SD is small, we will get a slim bell curve.
Things to Remember About Standard Deviation Graph in Excel
- The MEAN, or AVG values, are always the center point of the Excel standard deviation graph.
- It will fit approximately 68.2% of the data series in the range, i.e., MEAN – SD to MEAN + SD. (65-70)
- Approximately 95.5% of the data series will be fitted in the range i.e. 2 * (MEAN –SD) + 2 * (MEAN + SD).
- Approximately 99.7% of the data series will be fitted in the range i.e. 3 * (MEAN –SD) + 3 * (MEAN + SD).
- Excel standard deviation graph shape depends on the SD value. The higher the SD value wides the bell curve, the smaller the SD value, and the slimmer the bell curve.
- The statistical person can best explain complete knowledge of the bell curve, and we are limited to the Excel sheet.
Recommended Articles
This article is a guide to the Standard Deviation Graph in Excel. Here, we discuss creating a standard deviation chart/graph in Excel, practical examples, and a downloadable Excel template. You may learn more about excel from the following articles: –
- Formula of Sample Standard DeviationFormula Of Sample Standard DeviationSample standard deviation refers to the statistical metric that is used to measure the extent by which a random variable diverges from the mean of the sample.read more
- Excel Normal Distribution GraphExcel Normal Distribution GraphIn Excel, the normal distribution graph represents the normal distribution phenomena of a given data. This graph is created after calculating the mean and standard deviation, and then the normal variation.read more
- How to Make a Graph in Excel?How To Make A Graph In Excel?In Excel, a graph or chart lets us visualize information we've gathered from our data. It allows us to visualize data in easy-to-understand pictorial ways. The following components are required to create charts or graphs in Excel: 1 - Numerical Data, 2 - Data Headings, and 3 - Data in Proper Order.read more
- How to Create a Gantt Chart in Excel
