# How to Create a Normal Distribution Bell Curve in Excel - Automate Excel (2023)

This tutorial will demonstrate how to create a normal distribution bell curve in all versions of Excel: 2007, 2010, 2013, 2016, and 2019.

• Getting Started
• Step #1: Find the mean.
• Step #2: Find the standard deviation.
• Step #3: Set up the x-axis values for the curve.
• Step #4: Compute the normal distribution values for every x-axis value.
• Step #5: Create a scatter plot with smooth lines.
• Step #6: Set up the label table.
• Step #7: Insert the label data into the chart.
• Step #8: Change the chart type of the label series.
• Step #9: Modify the horizontal axis scale.
• Step #10: Insert and position the custom data labels.
• Step #11: Recolor the data markers (optional).
• Step #12: Add vertical lines (optional).

In statistics, a bell curve (also known as a standard normal distribution or Gaussian curve) is a symmetrical graph that illustrates the tendency of data to cluster around a center value, or mean, in a given dataset.

The y-axis represents the relative probability of a given value occurring in the dataset while the x-axis plots the values themselves on the chart to create a bell-shaped curve, hence the name.

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

Since Excel doesn’t have any built-in solutions to offer, you will have to plot it yourself. That’s why we developed the Chart Creator Add-in, a tool that allows you to build advanced Excel charts in just a few clicks.

In this step-by-step tutorial, you will learn how to create a normal distribution bell curve in Excel from the ground up:

To plot a Gaussian curve, you need to know two things:

• The mean (also known as the standard measurement). This determines the center of the curve—which, in turn, characterizes the position of the curve.
• The standard deviation (SD) of the measurements. This defines the spread of your data in the normal distribution—or in plain English, how wide the curve should be. For instance, in the bell curve shown above, one standard deviation of the mean represents the range between exam scores of 53 and 85.

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

It’s worth mentioning the 68-95-99.7 rule that can be applied to any normal distribution curve, meaning roughly 68% of your data is going to be placed within one SD away from the mean, 95% within two SD, and 99.7% within three SD.

Now that you know the essentials, let’s move from theory to practice.

## Getting Started

For illustration purposes, let’s assume you have the test scores of 200 students and want to grade them “on a curve,” meaning the students’ grades will be based on their relative performance to the rest of the class:

(Video) How to create a bell curve in Excel using your own data

## Step #1: Find the mean.

Typically, you are given the mean and SD values from the start, but if that’s not the case, you can easily compute these values in just a few simple steps. Let’s tackle the mean first.

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

Type the following formula into any empty cell (F1 in this example) next to your actual data (columns A and B) to calculate the average of the exam scores in the dataset:

``=AVERAGE(B2:B201)``

A quick note: more often than not, you may need to round up the formula output. To do that, simply wrap it in the ROUND function as follows:

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

## Step #2: Find the standard deviation.

One down, one to go. Fortunately, Excel has a special function to do all the dirty work of finding the standard deviation for you:

``=STDEV.P(B2:B201)``

Again, the formula picks all the values from the specified cell range (B2:B201) and computes its standard deviation—just don’t forget to round up the output as well.

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

## Step #3: Set up the x-axis values for the curve.

Basically, the chart constitutes a massive number of intervals (think of them as steps) joined together with a line to create a smooth curve.

In our case, the x-axis values will be used to illustrate a particular exam score while the y-axis values will tell us the probability of a student getting that score on the exam.

Technically, you can include as many intervals as you want—you can effortlessly erase the redundant data later by modifying the horizontal axis scale. Just make sure you pick a range that will incorporate the three standard deviations.

Let’s start a count at one (as there is no way a student can get a negative exam score) and go all the way up to 150—it doesn’t really matter whether it’s 150 or 1500—to set up another helper table.

1. Pick any empty cell below the chart data (such as E4) and type “1,” the value that defines the first interval.
2. Navigate to the Home tab.
3. In the Editing group, choose “Fill.
4. Under “Series in,” select “Column.
5. For “Step value,” type “1.” This value determines the increments that will be automatically added up until Excel reaches the last interval.
6. For “Stop value,” type “150,” the value that stands for the last interval, and click “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 original data cells as shown on the screenshots. Otherwise, the technique will not work.

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

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

(Video) How to Create a Bell Curve In Microsoft Excel

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

``=NORM.DIST(E4,\$F\$1,\$F\$2,FALSE)``

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

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

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

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

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

1. Select any value in the helper table containing the x- and y-axis values (E4:F153).
2. Go to the Insert tab.
3. Click the “Insert Scatter (X, Y) or Bubble Chart” button.
4. Choose “Scatter with Smooth Lines.”

## Step #6: Set up the label table.

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

Let’s make the normal distribution more informative by adding the labels illustrating all the standard deviation values below and above the mean (you can also use them for showing the z-scores instead).

For that, set up yet another helper table as follows:

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

Next, compute the standard deviation values below the mean by entering this simple formula into cell I4:

``=I5-\$F\$2``

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

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

``=I5+\$F\$2``

In the same way, execute the formula for the other two cells (I7:I8).

(Video) Learn Excel from MrExcel - Create A Bell Curve in Excel - Podcast #1663

Finally, fill the y-axis label values (J2:J8) with zeros as you want the data markers placed on the horizontal axis.

## Step #7: Insert the label data into the chart.

Now, add all the data you have prepared. Right-click on the chart plot and choose “Select Data.

In the dialog box that pops up, select “Add.

Highlight 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 newly-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 combo chart:

1. Navigate to the Combo tab.
2. For Series “Series2,” change “Chart Type” to “Scatter.
• Note: Make sure “Series1” remains as “Scatter with Smooth Lines.” Sometimes Excel will change it when you make a Combo Also make sure “Series1” is not pushed to the Secondary Axis—the check box next to the chart type should not be marked.
3. Click “OK.”

## Step #9: Modify the horizontal axis scale.

Center the chart on the bell curve by adjusting the horizontal axis scale. Right-click on the horizontal axis and pick “Format Axis” from the menu.

Once the task pane appears, do the following:

• Go to the Axis Options tab.
• Set the Minimum Bounds value to “15.”
• Set the Maximum Bounds value to “125.”

You can tweak the axis scale range however you see fit, but since you know the standard deviation ranges, set the Bounds values a bit away from each of your third standard deviations to show the “tail” of the curve.

(Video) How to Make a Bell Curve in Microsoft Excel

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

As you polish up your chart, be sure to add the custom data labels. First, right-click on any dot representing Series “Series2” and select “Add Data Labels.

Next, replace the default labels with the ones you previously set up and place them above the data markers.

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

Also, you can now remove the gridlines (right-click on them > Delete).

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

Finally, recolor the dots to help them fit into your chart style.

1. Right-click on any Series “Series2” data label.
2. Click the “Fill” button.
3. Pick your color from the palette that appears.

Also, remove the borders around the dots:

1. Right-click on the same data marker again and select “Outline.”
2. Choose “No Outline.”

## Step #12: Add vertical lines (optional).

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

• Select the chart plot (that way, the lines will be inserted directly into the chart).
• Go to the Insert tab.
• Click the “Shapes” button.
• Choose “Line.

Hold down the “SHIFT” key while dragging the mouse to draw perfectly vertical lines from each dot to where each line meets the bell curve.

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

(Video) How to Make a Bell Curve in Excel

## FAQs

### Why is my bell curve not smooth on Excel? ›

Always make sure to sort the data in Ascending order to get a smooth bell curve in Excel. Remember to Freeze the Cell of Average (Mean) & Standard Deviation when inputting the formula for the Normal Distribution. There are two formulas for Standard Deviation – STDEV. P & STDEV.

How do I make my data normally distributed in Excel? ›

How to calculate normal distribution in Excel
2. Find the mean of your data set. ...
3. Find the standard deviation of your data set. ...
4. Select a value for the distribution. ...
5. Type the NORM. ...
Apr 8, 2022

Why bell curve is outdated? ›

Some can debate that the bell curve creates too much emotional stress, and most importantly, it destroys the loyalty factor and leads to high levels of attrition. A study by the Society for Human Resource Management found that 90% of performance appraisals are painful and don't work.

Can Excel draw a normal distribution curve? ›

To make a normal distribution graph, go to the “Insert” tab, and in “Charts,” select a “Scatter” chart with smoothed lines and markers. When we insert the chart, we see that our bell curve or normal distribution graph is created.

How do you ensure data is normally distributed? ›

The statistical way to check if the data is normally distributed is to perform the Anderson-Darling test of normality. In this approach, the data points are used to compute a test statistic (A) which measures the distance between the expected distribution and the actual distribution.

How do you make Excel data dynamic? ›

Dynamic tables in Excel are the tables where when a new value is inserted into it.
...
Example
1. We must first select the data, A1:E6.
2. Now, in the “Insert” tab, we need to click on the “Table” under the “Tables” section.
3. Next, we have to select the data. ...
4. As a result, a dialog box will pop up, as shown below, then click “OK.”

How do you create a curved function in Excel? ›

Curve Fitting in Excel with Charts

First, create a scatter chart. Then right click on the data series and select “Add Trendline…” What is this? In the Format Trendline pane, select the options to Display Equation on chart and Display R-Squared value on chart.

How do you automatically insert Shapes in Excel? ›

To create an AutoShape in Excel, first select the Insert tab on the ribbon. Then click the Shapes drop-down in the Illustrations group. This will open a menu of shapes to choose from. Select the desired shape, and then click and drag to draw it on the worksheet.

What does Norm Dist do in Excel? ›

DIST function. Returns the normal distribution for the specified mean and standard deviation.

How do you know when you have a perfect bell curve? ›

Characteristics of a Bell Curve

The bell curve is perfectly symmetrical. It is concentrated around the peak and decreases on either side. In a bell curve, the peak represents the most probable event in the dataset while the other events are equally distributed around the peak.

### How do you normalize a bell curve? ›

Normal/Gaussian Distribution/Bell Curve
1. Min Max Scaling.
2. (X1 — MIN(X1) )/ MAX(X1) — MIN(X1)
3. Standard Score.
4. (x1 — μ) / σ
5. Where μ = mean and σ = standard deviation. We sill calculate all the values similarly.
6. Divide by Max.
7. x1/max(x1)
8. We will therefore normalize the prices distribution by using Divide by Max as following :
Oct 5, 2020

What is better than bell curve? ›

A 360 feedback appraisal system instead of the bell curve method can effectively rank an employee's performance based on individual work, not against the work of their peers.

What is the drawback of bell curve? ›

Using the bell curve model in performance management may be considered a rigid approach for rating employees. Sometimes managers need to put employees in specific gradients just for the sake of bell curve requirements. This happens more often when the teams are small.

What are the limitations of a bell curve? ›

Limitations of a Bell Curve

In reality, data are not perfectly normal. Sometimes there is skewness, or a lack of symmetry, between what falls above and below the mean. Other times there are fat tails (excess kurtosis), making tail events more probable than the normal distribution would predict.

What Excel function can be used to determine a probability of a standard normal distribution? ›

The NORM. S. DIST Function[1] is categorized under Excel Statistical functions. It will calculate the Standard Normal Distribution function for a given value.

What is cumulative in normal distribution in Excel? ›

Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.

How do you fix data that is not normally distributed? ›

Too many extreme values in a data set will result in a skewed distribution. Normality of data can be achieved by cleaning the data. This involves determining measurement errors, data-entry errors and outliers, and removing them from the data for valid reasons.

How do you tell if data will be normally distributed? ›

In order to be considered a normal distribution, a data set (when graphed) must follow a bell-shaped symmetrical curve centered around the mean. It must also adhere to the empirical rule that indicates the percentage of the data set that falls within (plus or minus) 1, 2 and 3 standard deviations of the mean.

What to do when the data is not normally distributed? ›

Many practitioners suggest that if your data are not normal, you should do a nonparametric version of the test, which does not assume normality. From my experience, I would say that if you have non-normal data, you may look at the nonparametric version of the test you are interested in running.

What does dynamically mean in Excel? ›

Dynamic Formulas allow you to insert Excel's formulas into cells even when the formula must reference rows that will be inserted later during the merge process. They can repeat for each inserted row or use only the cell where the field is placed.

### How do you dynamically select data in Excel chart? ›

Changing Source Data for the Chart to the Dynamic Named Range
1. Right click on your chart and select “Select Data”.
2. Under legend entries, click on edit.
3. In series values, change range reference with named range “amount”.
4. Click OK.
5. In horizontal axis, click edit.
6. Enter named range “months” for the axis label.
7. Click Ok.

Can Excel be used to create a histogram? ›

In Excel Online, you can view a histogram (a column chart that shows frequency data), but you can't create it because it requires the Analysis ToolPak, an Excel add-in that isn't supported in Excel for the web.

Can Excel be dynamic? ›

With the introduction of dynamic arrays, we can talk about two types of Excel: Dynamic Excel that fully supports dynamic arrays, functions and formulas. Currently it's only Excel 365 and Excel 2021.

What is Excel dynamic chart? ›

For the purposes of this discussion, an Excel dynamic chart is a chart that automatically updates itself whenever new data rows are added. The opposite is a static chart, which won't incorporate any new data rows added to the existing range.

How do you create a dataset of a normal distribution? ›

To proceed, just specify your values for the mean, standard deviation and dataset size, and then press "Generate". If you wish to plot your distribution as a histogram, we suggest you check out the histogram maker at our sister site, StatsCharts.Com. Population Mean (μ): Standard Deviation (σ):

Which variable will you use to draw a normal curve? ›

The shape and position of a normal distribution curve depend on two parameters, the mean and the standard deviation. Each normally distributed variable has its own normal distribution curve, which depends on the values of the variable's mean and standard deviation.

How do you create a cumulative distribution function? ›

The cumulative distribution function (CDF) of a random variable X is denoted by F(x), and is defined as F(x) = Pr(X ≤ x).
...
The CDF can be computed by summing these probabilities sequentially; we summarize as follows:
1. Pr(X ≤ 1) = 1/6.
2. Pr(X ≤ 2) = 2/6.
3. Pr(X ≤ 3) = 3/6.
4. Pr(X ≤ 4) = 4/6.
5. Pr(X ≤ 5) = 5/6.
6. Pr(X ≤ 6) = 6/6 = 1.

What is the difference between regular and cumulative histogram? ›

Definition and Use

Frequency histograms use each bar height to show the number of values in that interval. Cumulative frequency histograms use each bar height to show the number of values in that interval, plus the number of values in all lower intervals.

How do you write a function for a curve? ›

Given a graph of a line, we can write a linear function in the form y=mx+b by identifying the slope (m) and y-intercept (b) in the graph. GIven a graph of an exponential curve, we can write an exponential function in the form y=ab^x by identifying the common ratio (b) and y-intercept (a) in the graph.

What is an AutoShape in Excel? ›

An AutoShape is a drawing object that Excel has already designed for you. There a many of these AutoShapes and you can group them into even more complex drawings. Once you select a shape from the palette, you can click in the document to create a shape with the default size and color (blue).

### How do you insert an auto shape? ›

With your shape selected, click AutoShape on the Format menu, and then click the Colors and Lines tab. In the Color list under Fill, click Fill Effects > Picture tab > Select Picture.

How do you use smart shapes in Excel? ›

On the Insert menu, select SmartArt Graphic. On the SmartArt tab of the ribbon, in the Insert SmartArt Graphic group, select at the type of graphic you want (List, Process, etc.), and then select a layout. Enter your text by doing one of the following: Click [Text] in the Text pane, and then type your text.

How do you smooth distribution in Excel? ›

Another way to smooth out data series in Excel is to use the Lowess Smoothing tool. To do this, select your data series, then click on the "Data" tab. In the "Data Tools" group, click on the "Lowess Smoothing" button. In the dialog box that appears, select the desired smoothing factor and click OK.

What is smoothing in Excel? ›

Exponential Smoothing in Excel is an inbuilt smoothing method used for Forecasting, Smoothing the data, trend projection. To access, Exponential Smoothing in Excel, go to the Data menu tab and, from the Data Analysis option, choose Exponential Smoothing.

How do I make Excel run fuzzy? ›

How to Perform Fuzzy Matching in Excel (With Example)
2. Step 2: Enter the Two Datasets. ...
3. Step 3: Create Tables from Datasets. ...
4. Step 4: Perform Fuzzy Matching.
Jul 26, 2022

Can you create a normal distribution in Excel? ›

To make a normal distribution graph, go to the “Insert” tab, and in “Charts,” select a “Scatter” chart with smoothed lines and markers. When we insert the chart, we see that our bell curve or normal distribution graph is created.

How do you convert data to normal distribution? ›

1. Checking the distribution with Skewness.
2. Checking the distribution of some variables using Histogram.
3. Checking the distribution of variables using KDE plot.
4. Checking the distribution of variables using a Q-Q plot.
5. Transformations to change the distribution of features.
May 22, 2021

How do you create a curve in Excel? ›

On the Insert tab, click Shapes. Under Lines, click Curve. Click where you want the curve to start, drag to draw, and then click wherever you want to add a curve.

What is the difference between auto scrolling and smooth scrolling? ›

This makes the auto-scroll icon appear You move the mouse up or down to auto-scroll in that direction. If you enable smooth scrolling then you scroll by pixel and not by line height, so it is less jumpy but takes more CPU/GPU load.

How do you optimize table views performance for smooth fast scrolling? ›

A simple way to avoid that is to load images asynchronously by creating a simple wrapper around URLSession: This lets us fetch each image using a background thread and then update the UI once the required data is available. We can improve our performances even further by caching the images.

### What does smooth scrolling mean? ›

Instead of the page visibly rendering with each scroll, Chrome will instead adapt the page rendering to make it smoother. It's visibly better to view and, in many cases, works well with a scroll wheel mouse. The problem with smooth scrolling is that, like any animation, it can cause issues.

## Videos

1. How to simulate normal distribution in Excel with VBA
(Investment Analysis)
2. Bell Curve Excel template download - Chart for Employee Performance Rating - BPO Interview questions
(U Excel in MS office)
3. Creating Excel Normal Distribution Curve - When to buy or sell stocks Day Trading
(William K. Errico)
4. Normal Curves in Excel
(Eric Merchant)
5. Bell Curve Visual Report (Helix Forms)
(Daniel Zaccaro)
6. Easy Excel S-Curve Charts for Tracking Progress Over Time
(MyOnlineTrainingHub)
Top Articles
Latest Posts
Article information

Author: Gregorio Kreiger

Last Updated: 04/19/2023

Views: 5388

Rating: 4.7 / 5 (57 voted)

Author information

Name: Gregorio Kreiger

Birthday: 1994-12-18

Address: 89212 Tracey Ramp, Sunside, MT 08453-0951

Phone: +9014805370218

Job: Customer Designer

Hobby: Mountain biking, Orienteering, Hiking, Sewing, Backpacking, Mushroom hunting, Backpacking

Introduction: My name is Gregorio Kreiger, I am a tender, brainy, enthusiastic, combative, agreeable, gentle, gentle person who loves writing and wants to share my knowledge and understanding with you.