Excel Tool That Draws a Normal Distribution Graph

This tutorial will show how to create a normal distribution bell curve altogether versions of Excel: 2007, 2010, 2013, 2022, and 2022.

Bell Cut – Disembarrass Template Download

Download our free Bell Curve Guide for Excel.

Download Instantly

In statistics, a bell curve (also called a accepted normal dispersion surgery Gaussian shape) is a interchangeable graphical record that illustrates the tendency of information to clustering around a center measure, or mean, in a precondition dataset.

The y-axis represents the relative probability of a bestowed apprais occurring in the dataset while the x-axis plots the values themselves on the chart to make up a Gaussian shape, therefore the name.

The graph helps us psychoanalyse whether a particular value is part of the expected variation or is statistically substantial and, therefore, has to be examined more closely.

Since Excel doesn't have any intrinsical solutions to fling, you will get to plot information technology yourself. That's why we developed the Chart God Almighty Add-in, a tool that allows you to build advanced Excel charts in just a a couple of clicks.

In this step-by-mistreat tutorial, you will learn how to create a normal dispersion bell trend in Excel from the ground rising:

To secret plan a Gaussian curve, you need to know 2 things:

• The mean (a.k.a. the standard measurement). This determines the center of the curve—which, in turn, characterizes the position of the breaking ball.
• The standard diversion (SD) of the measurements. This defines the open of your data in the Gaussian distribution—operating theater in chaste English, how wide the curve should embody. For example, in the gong curve shown above, combined standard digression of the tight represents the range between exam lashing of 53 and 85.

The lower the SD, the taller the curve and the less your data will be disseminate out, and vice versa.

It's Worth mentioning the 68-95-99.7 rule that can Be applied to any mean distribution curve, meaning roughly 68% of your information is going to be placed within one SD away from the awful, 95% within 2 Coyote State, and 99.7% inside trio Coyote State.

Now that you bed the essentials, let's go down from possibility to pattern.

Acquiring Started

For illustration purposes, let's assume you have the test scores of 200 students and want to grade them "on a twist," pregnant the students' grades will be supported their relative carrying out to the rest of the class:

Step #1: Determine the mean.

Typically, you are given the signify and SD values from the start, but if that's not the case, you can easily calculate these values in antimonopoly a few simple stairs. Let's take on the mean starting time.

Since the mean indicates the average value of a sample or population of data, you john find your standard measurement using the AVERAGE function.

Character the following pattern into whatever pillaged cellular telephone (F1 in that example) next to your actual data (columns A and B) to calculate the average of the exam lots in the dataset:

A quick greenbac: more frequently than not, you may call for to one shot up the formula outturn. To get along that, simply wrap it in the ROUND function as follows:

=ROUND ( AVERAGE ( B2 : B201 ) , 0 )

Step #2: Uncovering the standard deviation.

Peerless down, i to go. Fortunately, Excel has a special function to do all the dirty workplace of finding the acceptable deviation for you:

Again, the formula picks all the values from the specified cell range (B2:B201) and computes its standard deviance—right Don't forget to flesh out up the output besides.

=ROUND ( STDEV . P ( B2 : B201 ) , 0 )

Step #3: Rear the x-axis vertebra values for the swerve.

Basically, the chart constitutes a massive count of intervals (think of them As stairs) married conjointly with a line to create a smooth curve.

In our case, the x-axis values will be used to illustrate a exceptional exam score while the y-axis values will tell the States the chance of a student getting that score on the test.

Technically, you derriere include equally many intervals As you want—you can effortlessly erase the superfluous data late away modifying the horizontal axis scale. But make sure you pick a kitchen range that will incorporate the three standard deviations.

Let's bulge a count out at one (arsenic there is none way a student can suffer a electronegative exam score) and go all the way adequate to 150—it doesn't really weigh whether it's 150 Oregon 1500—to set up another helper table.

1. Pick any empty-bellied cadre below the chart data (so much as E4) and eccentric "1," the value that defines the first time interval.
2. Sail to the Home plate tab.
3. In the Editing group, pick out "Fill."
4. Under "Series in," choose "Chromatography column."
5. For "Step note value," type "1." This value determines the increments that will be automatically added up until Surpass reaches the last interval.
6. For "Plosive consonant value," type "150," the value that stands for the last interval, and cluck "OK."

Miraculously, 149 cells in column E (E5:E153) have been filled with the values going from 2 to 150.

NOTE: Do not hide the fresh data cells as shown on the screenshots. Otherwise, the technique will not work.

Step #4: Compute the normal distribution values for every x-axis appreciate.

Right away, find the normal statistical distribution values—the probability of a student getting a certain exam score represented by a especial x-bloc value—for each of the intervals. Fortunately for you, Excel has the workhorse to do all these calculations for you: the NORM.DIST function.

Type the following formula into the cell to the right (F4) of your first interval (E4):

=Average . DIST ( E4 , $ F $ 1 , $ F $ 2 , FALSE )

Here is the decoded version to help you adjust accordingly:

=NORM . DIST ( [ the first interval ] , [ the mean ( absolute reference ) ] , [ the standard deviation ( absolute character reference ) , FALSE )

You lock the mean and SD values and then that you can effortlessly execute the rul for the remaining intervals (E5:E153).

Now, doubly-click along the fill handle to copy the formula into the rest of the cells (F5:F153).

Step #5: Create a scatter plat with smooth lines.

Finally, the time to build the bell curve has come:

1. Select any evaluate in the helper table containing the x- and y-bloc values (E4:F153).
2. Crack to the Insert tab.
3. Click the "Insert Scatter (X, Y) operating theatre Burble Graph" button.
4. Choose "Scatter with Smooth Lines."

Step #6: Set apart upfield the label table.

Technically, you have your bell curve. But it would constitute hard to read as information technology lacks any data describing it.

Let's make the normal distribution more informative by adding the labels illustrating complete the accepted deviation values below and above the mean (you can also use them for display the z-dozens instead).

For that, lay out functioning however another helper table as follows:

First, copy the Mean value (F1) next to the corresponding cell in column X-Value (I5).

Following, compute the monetary standard deviation values below the mean by entering this acerose formula into electric cell I4:

Simply put, the formula subtracts the sum of the preceding standard deviation values from the mean. Now, drag the fill handle ascending to transcript the formula into the remaining 2 cells (I2:I3).

Repeat the same process for the standard deviations above the mean victimisation the mirror formula:

In the homophonic way, execute the expression for the other two cells (I7:I8).

Finally, replete the y-axis judge values (J2:J8) with zeros Eastern Samoa you want the data markers placed on the horizontal axis.

Stride #7: Tuck the label data into the chart.

Immediately, add totally the data you have got disposed. Redress-click on the chart plot and pick out "Select Information."

In the panel that pops up, choice "Add."

Play up the respective cells ranges from the helper table—I2:I8 for "Series X values" and J2:J8 for "Series Y values"—and click "OK."

Step #8: Change the chart type of the label series.

Our next step is to change the chart type of the fresh-added series to make the data markers appear as dots. To do that, right-click on the chart plot and select "Change Chart Type."

Next, design a jazz band chart:

1. Navigate to the Combo tab.
2. For Serial "Series2," transfer "Chart Type" to "Scatter."
   • Note: Make a point "Series1" remains A "Scatter with Satiny Lines." Sometimes Excel will change information technology when you make a Combo Also make a point "Series1" is not pushed to the Secondary Axis—the hold in box next to the chart type should not be marked.
3. Click "Very well."

Footstep #9: Modify the swimming axis scale.

Center the chart connected the bell swerve by adjusting the horizontal axis scale. Right-click along the horizontal axis and pick "Format Axis" from the menu.

Erstwhile the task Ze appears, do the following:

• Hold up to the Axis Options pill.
• Set the Minimum Bounds value to "15."
• Set the Maximum Bounds value to "125."

You can tweak the bloc scale range however you see fit, but since you know the regular deflexion ranges, set the Bounds values a trifle away from each of your third normal deviations to show up the "tail" of the trend.

Step #10: Insert and position the custom data labels.

As you polish up your chart, represent sure to add the custom data labels. First, right-click on whatever constellate representing Serial publication "Series2" and select "Add Information Labels."

Next, replace the default labels with the ones you antecedently set ahead and post them above the data markers.

1. Right-click on any Series "Series2" data label.
2. Select "Format Data Labels."
3. In the task pane, swap to the Label Options tablet.
4. Check the "X Esteem" box.
5. Uncheck the "Y Value" box.
6. Under "Label Position," choose "Above."

Too, you crapper now remove the gridlines (right-click on them > Edit).

Step #11: Recolor the data markers (optional).

In conclusion, recolor the dots to help them healthy into your chart style.

1. Right-fall into place along any Series "Series2" data label.
2. Click the "Fill" push.
3. Pick your color from the palette that appears.

Also, remove the borders around the dots:

1. Right-click on the same data marker once more and prize "Outline."
2. Pick out "No Outline."

Step #12: Add stand-up lines (nonmandatory).

As a final adjustment, you can add vertical lines to the graph to help emphasize the SD values.

• Select the chart plot (that way, the lines will be inserted now into the graph).
• Croak to the Insert tab.
• Click the "Shapes" button.
• Choose "Line."

Accommodate bolt down the "SHIFT" key piece dragging the mouse to lot perfectly fastigiate lines from to each one Transportation to where each line meets the doorbell curve.

Change the chart title, and your improved bell curve is ready—showing your valuable distribution data.

And that's how you do it. You can now pick any dataset and create a normal distribution bell curve following these simpleton steps!

Download Normal Distribution Ship's bell Curve Guide

Download our disengage Normal Distribution Alexander Melville Bell Kink Template for Stand out.

Download Now

Excel Tool That Draws a Normal Distribution Graph

Source: https://www.automateexcel.com/charts/bell-curve-plot-template/

Share this post