This article is an elaboration on the meaning of standard deviation graph or bell curve, its advantages, uses, creation and design in Microsoft Excel.
Standard Deviation Graphs
A standard deviation graph is a graphical presentation that illustrates the distribution of categorical data with regards to standard deviation. It is also familiar by the name ‘bell curve’, as its structure resembles a bell and its associated curve portrays the data spread.
It has been noticed that the change in the distribution of data across the graph alters the smoothness of the curve. To initiate the creation of this graph, one needs to garner an understanding of standard deviation, how it is measured and what it is likely to project.
Standard Deviation (SD) is one of the most vital statistical parameters that tells how a set of data is dispersed mainly around the mean and average of all the values in that data set.
In simpler terms, this key parameter gives an idea of how far or close is a data value from the mean and average. It is measured with the help of the mean value corresponding to the data set whose SD needs to be determined.
Best Charts, Graphs, and Diagram Tools
- 10 Best Online Chart Maker of 2023
- 10 Best Microsoft Visio Alternatives 2023
- 10 Best Org Chart Maker of 2023
- 10 Best UML Diagram Tools 2023
- 10 Best Entity Relationship Diagram (ERD) Tools 2023
Standard Deviation graphs are extensively adopted across corporate organizations, educational institutes and some other establishments to track the performance of the employees and students as these charts give a precise understanding of their level of progress.
While there a lot of options available from an array of tools and applications, Microsoft Excel has been the most sought after and predominately used platform to curate the standard deviation or say the bell curve.
Microsoft Excel
MS Excel is a multi-tasking spreadsheet application, excessively employed by a million users from across the world that range from business professionals to starters, students to the teaching staff, bank staff and so on.
The data compilation reports obtained from this application can be regarded as important assets for a business. Also, the software can be addressed as an efficient chart maker as it offers the creation and customization of over dozens of graphs that are used to cater to various analysis purposes.
The Excel application is also configured and enriched with an optimum number of inbuilt formulas and functions, making it of ultimate importance and use for statistical operations.
Steps involved in generating a Standard Deviation Graph in Microsoft Excel
To create a standard deviation or a bell curve chart in MS Excel, let us take into consideration the scores obtained by a group of students in a test. The final graph derived at the end of the procedure will give an understanding pertaining to the performance level of the students and their shortcomings.
Available Data:
We have with us the scores garnered by 25 students in an English examination that was worth a total of 100 marks. The foremost thing would be to enter these values in the spreadsheet specifically in a tabular form for a better arrangement of the data values as shown below.
Step 1:
Post the values are entered in the spreadsheet, it is essential to determine the value for the mean i.e. the average and the Standard Deviation value for the available data.
These values are crafted by using the inbuilt functions offered by the application. More precisely, we use AVERAGE to calculate the mean value as illustrated below.
To estimate the Standard Deviation, Excel is configured with 2 options, one based for a sample set in a lot and the other for the entire data.
Here, as we are considering the scores of only 25 students in a class with a 70+ strength, the standard deviation value will be calculated with the help of the function STDEV.S i.e. the one for the sample of students.
Step 2:
Following the estimations above, the most important step is the derivation of the normal distribution data for every score jotted down in the spreadsheet.
This value is obtained by using the inbuilt function, NORM.DIST from the list of formulas provided in the application. Firstly, arrange the scores in the increasing order of their values using the Sort & Filter option for ease in finding the distribution.
The Normal Distribution function employs the value of the mean and standard deviation and is represented as follows:
NORM.DIST(x, mean, standard_dev, cumulative)
Here, x is the score whose distribution is to be determined and the cumulative parameter is true in case of an ascending graph or false otherwise. As we do not need a unidirectional increasing curve, we will choose false. This can be illustrated in the snapshot below.
Once done finding the value for the first score, copy the function for all the scores till you get the distribution values up to the 25th score.
Step 3:
The values obtained in the process above can be modified for decimals through the option present in the menu bar. Once we get hold of the accurate data values, it now time to represent them in a graphical structure.
For this, select the score and distribution column and click on the insert option on the bar and navigate to the charts. From the options provided, choose the Scatter chart to represent the bell curve on the workspace.
Step 4:
The final task would be to customize the chart for enhancement of text and color fonts, as well as chart and axis titles. The graph can also be personalized with a change in the color, layout and various data elements like gridlines, error bars and legends. The final graph derived post the beautification in this instance appears as in the picture below.
Best Charts, Graphs, and Diagram Tools
- 10 Best Online Chart Maker of 2023
- 10 Best Microsoft Visio Alternatives 2023
- 10 Best Org Chart Maker of 2023
- 10 Best UML Diagram Tools 2023
- 10 Best Entity Relationship Diagram (ERD) Tools 2023
Rendering a brief overview of the versatility of the standard deviation graph and its creation in MS Excel, it is needless to say that the application is the most opted tool for generating the graph.
The inbuilt functions integrated into this tool make it extremely effective for use to save a lot of time and yield accurate results.
Hence, employing the Excel application to curate the standard deviation graph, or say bell curve to depict the normal distribution can be rightfully endorsed for adoption across the various and diverse comparative analysis.