How to Plot a Normal Distribution (Bell Curve) in Excel – with Shading!

hi Enrique here and in this video I'll be walking through how to plot a normal distribution or bell curve in Excel and I'll also be showing you a trick on how you can shade certain areas under the curve for this example we'll be using a data set of MBA grades and we want to know what the distribution of these grades looks like so let's jump into Excel and let's plot this using a combination of a line chart and an area chart so the x-axis is going to be for the X values in this case the MBA grades so I wrote them out here from 60 to 100 and it's best to be as granular as possible I did it in two by two so that they do fit nicely on this screen here but you can do it one by one as well if you want to so that's the x-axis now we just need the height of the actual line across the y-axis or the probability density and this is where the false argument in the norm.dis function actually comes in handy so let's create our first Norm dot dysfunction we want to find the height of the curve at this grade for a normal distribution with this mean I'm going to fix it with F4 and this standard deviation fix that with F4 as well and this time we actually do want to use false because we want the height of the curve because we're going to plot it close that press enter and let's apply this down for the rest of the grades so now I'm going to select a blank cell here and go to insert a line chart and let's select the data that we want to plot let's add the series for the probability density select the values press OK you can already see the Bell shape start to form let's just edit the axis labels for the x-axis and select our grades press ok press ok and perfect so this is the let's change the title distribution of MBA grades and press enter in which X follows a normal distribution with a mean of 80.17 and a standard deviation of 6.17 let me add some axis titles here this is the probability density this is the X values or MBA grade let me remove these grid lines and we have our normal distribution curve so that was easy enough but adding the area underneath is the fun part and the trick is to write an if function that returns this value whenever you do want to show the area and then a zero whenever you don't so for example if we wanted to show the area you know under 74 Maybe we write if this is less than or equal to 74. then return the probability density otherwise return zero close the IF function apply it down you'll see that all the way up to 74 we do get the probability density in this case we're going to make it an area here and then everywhere else we get the zero so we're not going to show it as a shaded part and to add this we're going to use an area chart so let's select this go to chart design let's select data to add this new series these are the values okay and let's use the same x-axis labels press ok press OK and I'm going to change the chart type to a combo chart in which the probability density is a line and the cumulative probability is an area chart just press ok I'm going to edit this line right here and let me make it a smooth line and then let me change this color so that it matches this blue solid fill and let's add some transparency of 80 percent so we're almost there but the problem is that you'll notice that it goes from this height right here at 74 and then to zero at 76 in this diagonal line instead of going straight down which is what would be ideal so to fix it what we can do is actually use a negative value for this instead of zero that's so far down here that the line from here to there actually kind of passes through the 74 instead of the 76. so let me go back to my formula and change the zero to a negative 100 [Music] let's apply this down it's going to mess up our chart at first but let me just fix my y-axis so that it always starts at zero and look at that much much better we can even hide these numbers since they're not really adding much context let's just change the labels to none we can remove the probability density label as well if we want to and we can even use an and function here let me write and if we want to show the area between two values so say we want it to show between I don't know 72 and 86 so it's going to be if and then we need two conditions to be met this to be greater than or equal to 72 and this to be less than or equal to 86 close the and function press enter apply this sound and there we go pretty cool right if you enjoyed this video check out my statistics for data analysis course at mavenanalytics.io where we have plenty of other courses on Excel Tableau power bi python SQL and more hope to see you again soon foreign

Channel: Maven Analytics

Views: 81,855

Keywords: data science, big data, data analytics, learn data, learn, data, analytics, maven, career, analysis, excel, sql, power bi, python, course, Why learn data, what is data, mysql, beginner, visualization, tutorial, education, data science vs business intelligence, business intelligence, remote jobs, data career paths, data career

Id: 0tLF83IxwdY

Length: 6min 55sec (415 seconds)

Published: Wed Aug 23 2023