How to create a bell curve in excel | Normal Distribution Guide
In this article we’ll learn what is a bell curve graph also known as normal distribution curve, how to make a bell curve in excel and much more.
Bell curve in excel is a method to plot data in the shape of a bell curve.
What is a bell curve?
A bell curve is type of distribution for a variable. In the bell curve graph, the top / highest point represents most probable event and all other possible events are symmetrically distributed around the mean of the data.
The width of the bell curve is known as standard deviation. This is what a bell curve looks like:
Image Credit: Investopedia
You can read more about bell curve here.
Bell curve in Excel
Normal distribution in excel is used to plot data in a shape of bell curve in excel. Let’s see a couple of examples to understand the use of bell curve calculation:
- Generally in companies employee’s performance is measured across a bell curve. That is related to other’s performance. So even if all the employees are top performers only few will get a top rating, few will get a low rating but most of the people will get an average rating.
Image Credit: Empxtrack
- Let’s say you’re a teacher and you give a test. You created a grading system will gives grade A to anyone who scored above 80 marks and grade B to who scored in range of 60-80 and so on.
But after the test you find out than all the students scored above 80 getting A grade. Now it’s difficult for you to differentiate between student who scored 95 and one who scored 81.
So to keep things fair you have to use a bell curve to evaluate students. When calculating bell curve the marks are converted to percentiles. Same as before, students with high marks are on the right side of the bell curve, low scorers are on left and most of students are in the middle of the excel bell curve.
Image Credit: Teacherhead
Now to create a bell curve in excel, we need to understand 2 main concepts:
- Mean: This is the average value of all data.
- Standard Deviation: This gives an understanding of how much variation is there between values & the mean. A low standard deviation tells us that there isn’t much difference between the mean and the data value, while a high deviation means that the variance/difference is much more.
Let’s say you have a data set which is normally distributed then the excel bell curve will follow certain rules:
- The centre of this excel distribution curve will be the mean of the data. And the highest point of this excel bell curve.
- 2% data/students will be in range of Mean – Standard deviation to Mean + Standard Deviation
- 5% data/students will be in range of Mean – 2*Standard deviation to Mean + 2*Standard Deviation
- 7% data /students will lie in range of Mean – 3*Standard deviation to Mean + 3*Standard Deviation
The 68.2%, 95.5% and 99.7% are just bell curve percentages. Now let’s create bell curve in excel.
Make a bell curve in excel
We’ll use the same dataset of student’s marks for a given test.
Now using AVERAGE function in excel and STDEV Function we have calculated the average marks and the standard deviation.
The average marks is 62.5 and standard deviation is 20.91.
- First select the cells B2:B15 and go to Data tab and sort them in ascending order.
- In cell C2 type =NORM.DIST(B2,$B$17,$B$18,FALSE) hit enter and drag this formula to the rest of the cells.
DIST calculates cumulative normal distribution in excel.
- Select range C2:C15 and go to Insert tab and select ‘Scatter with Smooth Lines’ chart
- You just created distribution curve in excel or bell curve.
Now you can change the chart title, adjust axis or play with the chart settings as you wish.
If you get a slim excel bell curve then it means the standard deviation is low. When the bell curves is wide and covers most part of the chart it means that the standard deviation is high.
Here we just covered the process of creating a bell curve in excel but bell curve has so many more elements. I would recommend you read the below shared articles if you want to understand more about bell curve.
That was all about bell curve calculation.
I hope this article was helpful to you and explained to you what is excel normal distribution, calculating bell curve and understanding bell curve graph.
If you have any questions or tips, I’d love to hear them in the comment section below.
Advanced Excel Course
Learn Vlookup, Pivot tables, What IF, Macro Automation, Power Query and much more.
- 10+Hrs of Learning Content
- 50+ Practical Exercises
- Doubt Resolution