Microsoft 365 Excel Tutorial - Plot a Bell Curve with 2 Formulas in Excel.
Download the finished spreadsheet from: Excel Plot A Bell Curve in Excel - Episode 2596 Sample Files - MrExcel Publishing
If you don't have the SEQUENCE function, use this older video:
Download the finished spreadsheet from: Excel Plot A Bell Curve in Excel - Episode 2596 Sample Files - MrExcel Publishing
If you don't have the SEQUENCE function, use this older video:
Transcript of the video:
How to draw a bell curve in Excel with just two formulas.
This is an update on an old video.
And before you try this new method, you need to make sure that you have this formula right here, =SEQUENCE.
Make sure that that works.
Just put it in SEQUENCE of five or something like that. And you should see the numbers one to five.
That tells you that you have the new dynamic arrays that we need for this method.
If you're getting a #NAME?
error, then just come out here to 1663 and go through the slightly longer steps.
Okay, so to get started, somewhere at the top of your spreadsheet.
Add a cell for the mean and the standard deviation.
And then we're going to plot a bell curve here using two formulas. The first formula is the SEQUENCE function.
How many rows do we want? We want 61.
Why 61? Because it gives us a nice smooth line.
You could probably do it with less, but you'd have little jagged edges.
One column. And where are we going to start?
We're going to start at the mean minus three times standard deviation.
So we just point at the mean cell minus three times the standard deviation cell.
Comma. And then what's the step?
We want these 60 points to encompass three standard deviations below the mean and three above the mean.
So it's essentially six standard deviations divided by 60. Or three standard deviations divided by 30.
Or just one standard deviation divided by 10.
So point to the standard deviation, divided by 10 like that.
When we press enter, we get a nice array of 61 answers here. Wth the mean, 50, right in the middle like that.
Okay.
Now to get the points for the curve itself, you use equal NORM.DIST. The normal distribution.
For the X, we're going to point to A7, but type hash.
That says not just A7, but the entire array that starts at A7.
They want to know the mean. That's in this case cell B3.
They want to know the standard deviation. That's B4.
Now, in the old video, we had to lock these down using F4.
But we're not copying this formula anymore. So no dollar signs needed.
And then comma False at the end. All right, so we have this whole range.
Control asterisk to select the range. On the Insert tab, I'm going to use a line chart.
In the old video, I used a Scatter chart.
But it turns out that if we want to draw a vertical line at the mean. The line chart works much better.
All right, now there's our bell curve. Now check this out.
What we can do is it's completely flexible.
If we just wanted a standard normal with a mean of zero and standard deviation of one. That works great.
If we want to have something with a mean of 117 and a standard deviation of 21, that works.
You could even have a crazy standard deviation of 210 and it will update automatically.
Now, what about adding that vertical line in?
So that is going to be made with a drop line in Excel. And we're going to add a second series.
And this series needs exactly one point. At the mean, right here, type zero.
And then check this out. This is so easy.
Click on the chart.
And I want to grab that blue handle in the top right-hand corner and drag to include one more series.
Nothing changes on the chart. Go to Chart Design.
Add Chart Element. Lines, and a high-low line.
And there is our line, vertical line right at the mean.
Hey, all right, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
This is an update on an old video.
And before you try this new method, you need to make sure that you have this formula right here, =SEQUENCE.
Make sure that that works.
Just put it in SEQUENCE of five or something like that. And you should see the numbers one to five.
That tells you that you have the new dynamic arrays that we need for this method.
If you're getting a #NAME?
error, then just come out here to 1663 and go through the slightly longer steps.
Okay, so to get started, somewhere at the top of your spreadsheet.
Add a cell for the mean and the standard deviation.
And then we're going to plot a bell curve here using two formulas. The first formula is the SEQUENCE function.
How many rows do we want? We want 61.
Why 61? Because it gives us a nice smooth line.
You could probably do it with less, but you'd have little jagged edges.
One column. And where are we going to start?
We're going to start at the mean minus three times standard deviation.
So we just point at the mean cell minus three times the standard deviation cell.
Comma. And then what's the step?
We want these 60 points to encompass three standard deviations below the mean and three above the mean.
So it's essentially six standard deviations divided by 60. Or three standard deviations divided by 30.
Or just one standard deviation divided by 10.
So point to the standard deviation, divided by 10 like that.
When we press enter, we get a nice array of 61 answers here. Wth the mean, 50, right in the middle like that.
Okay.
Now to get the points for the curve itself, you use equal NORM.DIST. The normal distribution.
For the X, we're going to point to A7, but type hash.
That says not just A7, but the entire array that starts at A7.
They want to know the mean. That's in this case cell B3.
They want to know the standard deviation. That's B4.
Now, in the old video, we had to lock these down using F4.
But we're not copying this formula anymore. So no dollar signs needed.
And then comma False at the end. All right, so we have this whole range.
Control asterisk to select the range. On the Insert tab, I'm going to use a line chart.
In the old video, I used a Scatter chart.
But it turns out that if we want to draw a vertical line at the mean. The line chart works much better.
All right, now there's our bell curve. Now check this out.
What we can do is it's completely flexible.
If we just wanted a standard normal with a mean of zero and standard deviation of one. That works great.
If we want to have something with a mean of 117 and a standard deviation of 21, that works.
You could even have a crazy standard deviation of 210 and it will update automatically.
Now, what about adding that vertical line in?
So that is going to be made with a drop line in Excel. And we're going to add a second series.
And this series needs exactly one point. At the mean, right here, type zero.
And then check this out. This is so easy.
Click on the chart.
And I want to grab that blue handle in the top right-hand corner and drag to include one more series.
Nothing changes on the chart. Go to Chart Design.
Add Chart Element. Lines, and a high-low line.
And there is our line, vertical line right at the mean.
Hey, all right, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
