Normal Distribution in Excel: NORM.DIST and a Bell Curve

The normal distribution in Excel comes down to two functions: NORM.DIST, which turns a value into a probability, and NORM.INV, which turns a probability back into a value. With them you can answer "what share of scores are below 85?" and "what score marks the top 10%?", and you can draw the classic bell curve. This guide is for students, analysts and anyone who has to work with test scores, measurements or other data that clusters around an average.

What is a normal distribution?#

A normal distribution is a symmetric, bell-shaped pattern where most values sit near the average and fewer sit far from it. Two numbers describe it completely:

• The mean: the centre of the bell, from AVERAGE.
• The standard deviation: how wide the bell is, from STDEV.S. See how to calculate standard deviation in Excel.

In a normal distribution, about 68% of values fall within 1 standard deviation of the mean, about 95% within 2, and about 99.7% within 3. This is often called the 68-95-99.7 rule.

All examples below use eight exam scores with a mean of 70 and a standard deviation of 10:

=AVERAGE(B2:B9) returns 70 and =STDEV.S(B2:B9) returns 10. The AVERAGE function guide covers how AVERAGE treats blanks and text.

NORM.DIST in Excel: syntax#

NORM.DIST takes four arguments:

=NORM.DIST(x, mean, standard_dev, cumulative)

Most of the time you want TRUE. FALSE returns the probability density, the height of the curve. For a smooth distribution, the chance of hitting one exact value is zero, so the height is not a probability by itself. You need it only to draw the curve.

NORM.DIST arrived in Excel 2010. The older NORMDIST still works and returns the same numbers.

Find probabilities with NORM.DIST#

Put the mean in G1 and the standard deviation in G2, so you never retype them. Then:

The last two rows are the 68-95-99.7 rule in action: 60 to 80 is 1 standard deviation either side, and 50 to 90 is 2. For "above" questions, subtract from 1. For "between" questions, subtract the lower cumulative result from the higher one.

Find a cutoff with NORM.INV#

NORM.INV is the reverse of NORM.DIST. Give it a probability and it returns the value:

=NORM.INV(probability, mean, standard_dev)

To find the score that marks the top 10%, ask for the value with 90% below it: =NORM.INV(0.9,G1,G2) returns 82.8. The middle 50% of scores runs from =NORM.INV(0.25,G1,G2) (63.3) to =NORM.INV(0.75,G1,G2) (76.7).

These are what the model predicts. To get percentiles straight from your actual data instead, use PERCENTILE.INC or QUARTILE.INC; the guide to percentiles and quartiles in Excel shows both.

Z-scores and NORM.S.DIST#

A z-score says how many standard deviations a value is from the mean:

=(x - mean) / standard_dev

The top score, 89, has a z-score of (89 − 70) ÷ 10 = 1.9. Excel's STANDARDIZE does the same: =STANDARDIZE(89,70,10) also returns 1.9.

NORM.S.DIST is the standard normal distribution, with mean 0 and standard deviation 1. It takes a z-score directly: =NORM.S.DIST(1.9,TRUE) returns 0.9713, so a score of 89 is higher than about 97% of the model's values. NORM.S.INV goes the other way: =NORM.S.INV(0.975) returns 1.96, the number behind 95% confidence intervals.

How to make a bell curve in Excel#

A bell curve is a chart of NORM.DIST with FALSE over a range of x values. Build it in four steps:

1. In G1 type the mean (70) and in G2 the standard deviation (10).
2. In C2 type 40 (the mean minus 3 standard deviations). In C3 type =C2+2 and fill down until you reach 100.
3. In D2 type the height formula and fill it down (Windows: Ctrl+D; Mac: ⌘D):

=NORM.DIST(C2,$G$1,$G$2,FALSE)

4. Select C2:D32 and choose Insert > Scatter > Scatter with Smooth Lines.

The $ signs matter. Without them, the mean and standard deviation references slide down to G2, G3 and so on as you fill, and the curve falls apart. The peak height at 70 is about 0.0399, and the curve is symmetric: the height at 60 and at 80 is the same, about 0.0242. Use a step of 1 instead of 2 for a smoother line.

Check whether your data looks normal#

Not every data set is normal, and NORM.DIST gives misleading answers when it isn't. Three quick checks:

• Mean vs median. In a symmetric distribution they are close. Here both are 70. A big gap means the data is skewed; see average vs median.
• The 68% check. Count the values within 1 standard deviation: =COUNTIFS(B2:B9,">="&G1-G2,B2:B9,"<="&G1+G2) returns 6 of 8 (75%). With only eight values, that's close enough to 68%.
• The shape. A histogram shows whether the data has one central peak. Follow how to make a histogram in Excel and compare it with your bell curve.

Income, wait times and house prices are often skewed, with a long tail on one side. For data like that, report percentiles rather than normal probabilities.

Practise it#

The normal distribution is built on the mean, and the bell curve depends on locked references. Practise AVERAGE, MIN and MAX in a live spreadsheet, then practise absolute references with the $ sign so your formulas fill down correctly. You type each formula and it checks it instantly. Or work out the z-score of 89 in the exercise on this page.