Normal distribution curve in Excel

Everything you need is in one function.

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

FALSE in the last argument returns the probability density at x — the height of the bell curve. TRUE returns the cumulative probability, the area to the left of x, which is the S-shaped curve and the one you want for "what fraction is above the upper limit?".

The legacy NORMDIST still works in current versions and does the same thing.

Drawing the curve

Compute the mean and standard deviation of your data: =AVERAGE(A2:A101) and =STDEV.S(A2:A101). Use STDEV.S — the n−1 form — because you have a sample, not a population. Put them in fixed cells, say E1 and E2.
Build an x column. The curve needs its own axis values, not your data. Start at mean − 4σ and step towards mean + 4σ in a hundred or so increments. In G2 put =$E$1-4*$E$2, in G3 put =G2+8*$E$2/100, and fill down a hundred rows.
Compute the density in H2: =NORM.DIST(G2,$E$1,$E$2,FALSE), filled down.
Plot G against H as a scatter with smooth lines. A line chart also works, but it treats x as categories, so the spacing is only right by accident.

That is a normal curve. It is not yet one you can put on top of a histogram.

The scaling step, which is where it goes wrong

The commonest complaint about this whole exercise is that the curve comes out as a flat line pinned to the x-axis. The cause is always the same: a density and a frequency are not the same units.

NORM.DIST(..., FALSE) returns a density that integrates to 1 over the whole range. For a process with σ = 0.5 the peak density is about 0.8. Your histogram bars are counts — 14, 22, 9. Plot 0.8 against 22 on one axis and the curve is invisible.

To put a density on a frequency histogram, multiply it by the number of observations and the bin width:

expected frequency = NORM.DIST(x, mean, sd, FALSE) × n × bin_width

That is it. The bin width converts density per unit into probability per bin; n converts probability into an expected count. Get either factor wrong — bin width of 2 when the bins are 0.5 wide — and the curve will be the right shape at the wrong height, which looks like a fit problem and is not.

The alternative is to convert the histogram instead: plot count ÷ n ÷ bin width on the y-axis and the raw density overlays directly, no scaling. Cleaner, and nobody in the meeting will understand the axis.

Building the histogram to overlay onto

Excel's native Histogram chart type gives you no way to lay a curve over it, so the overlay needs a bin table you build yourself:

A column of bin upper bounds, evenly spaced.
=FREQUENCY(data_range, bins_range) for the counts. It is an array formula: in Microsoft 365 it spills on its own, in older versions it needs Ctrl+Shift+Enter across the whole output range.
A bin-centre column — bin bounds are edges, and the curve should be evaluated at centres.
Select centres, counts and scaled density → Insert → Combo chart. Counts as a clustered column, scaled density as a smooth line on the same axis. Set the column series' gap width to 0 so the bars touch and read as a histogram.

Both series belong on the same axis. If you find yourself reaching for a secondary axis to make the curve fit, you have skipped the scaling and are about to draw a curve whose height means nothing.

The other direction, and z-scores

NORM.INV(probability, mean, sd) goes the other way: give it a probability, get the value below which that fraction sits. =NORM.INV(0.00135, 10, 0.5) returns the point three standard deviations below the mean. It is how you answer "what value would only 1 in 1,000 parts fall below?"

For standardised work, =STANDARDIZE(x, mean, sd) gives the z-score, =NORM.S.DIST(z, TRUE) the area to its left, and =NORM.S.INV(p) the inverse. The .S functions assume mean 0 and sd 1.

The practical use is the expected fraction out of specification:

above USL  = 1 - NORM.DIST(USL, mean, sd, TRUE)
below LSL  =     NORM.DIST(LSL, mean, sd, TRUE)
ppm        = (above + below) * 1000000

That is the calculation that turns a Cpk into a defect rate, and it rests entirely on the assumption the next section is about.

A curve over a histogram is not a test of normality

It is a picture. It has no threshold, no error rate and no conclusion, and it makes the data look more normal than it is, because the eye is being handed the answer it was meant to be checking.

Eyeballing fails in both directions, predictably:

Small samples look non-normal when nothing is wrong. Thirty readings from a perfectly normal process produce a lumpy, asymmetric histogram with a gap somewhere. That is what thirty draws look like.
Large samples make trivial departures look dramatic. Two thousand readings give smooth bars, and a skew of 0.15 — of no practical consequence for anything — is plainly visible.
The bin width decides the shape. The same data binned twelve ways and binned forty ways can look unimodal or bimodal. Nothing about the process changed.

A normal probability plot is a far better visual test. Sort the data ascending and plot the i-th value against =NORM.S.INV((i-0.5)/n) — several plotting-position conventions exist and the differences are immaterial here. Normal data falls on a straight line, and departures read as shapes rather than impressions: curvature at both ends is heavy or light tails, a bend at one end is skew, a step is a mixture of two populations. Excel has no built-in version, but it is four columns and a scatter chart.

Anderson-Darling is the numerical test, and it is the one to use because it weights the tails, which is where capability lives. Excel does not have it — neither the function library nor the Analysis ToolPak — so it needs an add-in or a statistics package.

And the thing that is misread most often: a non-significant result is a failure to reject, not evidence of normality. p = 0.34 on forty readings means the test did not have the power to detect a departure at that sample size. It does not mean the data is normal. Run the same test on the same process with five thousand readings and it will very likely reject, because at that n it can see departures that do not matter. The answer is a function of your sample size as much as of your process, which is why the test belongs alongside the probability plot rather than instead of it.

When normality actually matters

People worry about this in the wrong place, consistently.

It matters enormously for converting a capability index into a defect rate. Cpk itself is arithmetic on a mean and a sigma and can be computed for anything. The moment you turn it into ppm you have integrated a normal tail, and a tail area is exquisitely sensitive to the shape of the tail. A characteristic bounded at zero — flatness, roundness, concentricity, surface finish, an impurity level — is skewed by its own physics, and the normal ppm for it is wrong, usually optimistically. The Cpk to ppm converter states this assumption on its own results for the same reason.

It matters much less for whether a control chart works. Shewhart charts are famously robust to non-normality. On a subgrouped X̄ chart the plotted points are averages, and the central limit theorem pulls their distribution towards normal even when the individual readings are nowhere near it — with subgroups of four or five the false-alarm rate stays close to nominal for most real distributions. An individuals chart is more exposed, and strongly skewed data will signal on the long-tailed side more often than three sigma implies, but even there the chart remains usable.

So the process that fails a normality test is usually still fine to chart, while the Cpk quoted on it needs a caveat or a different method — a transformation, a percentile-based capability calculation, or a plain statement that the ppm figure is indicative. What you should not do is chase normality by deleting readings.

If you want the capability numbers with the assumption made explicit, the Cp/Cpk calculator will do the arithmetic, and what is Cpk covers the three ways the index misleads — normality being only one of them, and not the largest.