Normality Check and Finding Outliers in Excel

Summary: In inferential statistics, you need to know whether small data sets are normally distributed and free of outliers. This page shows you how to do it in Excel.

Example — Vehicle Weights

Consider these vehicle weights (in pounds):

The question is whether they are nearly normally distributed, and free of outliers. As you see, the numbers are already entered in the Excel sheet.

Test for Outliers

To test for outliers, the usual tool is a boxplot or box-whisker diagram. Boxplots are quite difficult to do in Excel; see for example Box Plot and Whisker Plots in Excel 2007. (That page actually works for later Excels too.)

But the boxplot is based on calculations that are easy in Excel: find the interquartile range (IQR), then set fences equal to Q1−1.5×IQR and Q3+1.5×IQR. Any values outside the fences are outliers.

In the workbook, this is done in the top half of columns I and J. You can see that the first and third quartiles are 3250 and 3500 pounds, so the IQR is 250. The fences are 2875 and 3875 pounds. There are no outliers on the low end, but there’s one on the high end. By looking at the sorted data in column F, you can identify 4000 pounds as the outlier.

Test for Normality

The main tool for testing normality is a normal probability plot. Actually, no real-life data set is exactly normal, but you use that plot to test whether a data set is close enough to normally distributed. The closer the data set is to normal, the closer the plot will be to a straight line.

Just looking at a plot, you may not be sure whether it’s “close enough” to a straight line, especially with smaller data sets. Most of the time, you need to make some fairly gnarly computations to answer that question: see Appendix — The Theory.

But the Excel workbook handles all that for you. All you have to do, if the normal probability plot is inconclusive, is look at r and the critical number CRIT. If r > CRIT, you’re safe to treat the sample as normally distributed; if r < CRIT, you can’t treat the sample as normally distributed or use it for inferential statistics.

As you see in the middle section of columns I and J, r = 0.9599 and CRIT = 0.9179. r > CRIT, so the data are near enough to a normal distribution.

More Examples

Feel free to explore by changing, adding or removing numbers. Just remember to click the Go! button after any changes.

When you click the Go! button, a macro runs to sort the data and update the normal probability plot. If you’re interested in the VBA code that does this, press Alt+F11 to open the Visual Basic Editor.

There are some sample data sets in Sheet2; you can copy them into the Sample box with Edit » Paste Special » Values.

Many of these data sets are nearly normal but still have outliers. The accompanying workbook started out as a simple simulation using @RISK from Palisade Corporation to see whether such a thing was possible, but once I had the data sets I didn’t want to throw them away. ☺)

Appendix — The Theory

The basic idea isn’t too bad. You make an xy scatterplot where the x’s are the data points, sorted in ascending order, and the y’s are the expected z-scores for a normal distribution. (I’m going to abbreviate “normally distributed” or “normal distribution” as ND to save wear and tear on my keyboard and your eyes.)

Why would you expect that to be a straight line? Recall the formula for a z-score: z = (x−x̅)/s. Breaking the one fraction into two, you have z = x/s−x̅/s. That’s just a linear equation, with slope 1/s and intercept x̅/s. So an xz plot of any theoretical ND, plotting each data point’s z-score against the actual data value, would be a straight line.

Further, if your actual data points are ND, then their actual z-scores will match their expected-for-a-normal-distribution z-scores, and therefore a scatterplot of expected z-scores against actual data values will also be a straight line.

Now, in real life no data set is ever exactly a ND, so you won’t ever see a perfectly straight line. Instead, you say that the closer the points are to a straight line, the closer the data set is to normal. If the data points are too far from a straight line — if their correlation coefficient r is lower than some critical value — then you reject the idea that the data set is ND.

Okay, so you have to plot the data points against what their z-scores should be if this is a ND, and specifically for a sample of n points from a ND, where n is your sample size. This must be built up in a sequence of steps:

1. Divide the normal curve (mentally) into n regions of equal probability and take one probability from each region. For technical reasons, the probability number you use for region i is (i−.375)/(n+.25). This formula is in many textbooks, and also in Ryan and Joiner’s paper Normal Probability Plots and Tests for Normality [full citation at https://BrownMath.com/swt/sources.htm#so_Ryan1976].
2. Compute the expected z-scores for those probabilities. Working with the calculator, that’s just invNorm of (i−.375)/(n+.25).
3. Plot those expected z-scores against the data values. This xy plot (or xz plot) has a correlation coefficient r, computed just like any other correlation coefficient.
4. Compare the r for your data set to the critical value for the size of your data set. Ryan and Joiner determined that the critical value for sample size n, at the 0.05 significance level,, is 1.0063 − .1288/√n − .6118/n + 1.3505/n². To make it a little easier on the calculator I rearranged it as 1.0063 − .6118/n + 1.3505/n² − .1288/√n.
   In the same paper, they gave formulas for critical values at other significance levels:

   1.0071 − 0.1371/√n − 0.3682/n + 0.7780/n² at α=0.10

   0.9963 − 0.0211/√n − 1.4106/n + 3.1791/n² at α=0.01

The closer the points are to a straight line, the closer the data set is to fitting a normal model. In other words, a larger r indicates a ND, and a smaller r indicates a non-ND. You can draw one of two conclusions:

• If r is less than the critical value, reject the hypothesis of normality at the 0.05 significance level and say that the data set is not ND.

  (If you haven’t studied hypothesis testing yet, another way to say it is that you’re pretty sure the data set doesn’t fit the normal model because there’s less than a 5% probability that it does.)

• If r is greater than the critical value, fail to reject the hypothesis that the data set comes from a ND.

  This doesn’t mean you are certain it does, merely that you can’t rule it out. Technically you don’t know either way, but practically it doesn’t matter. Remember (or you will learn later) that inferential statistics procedures like t tests are robust, meaning that they still work even if the data are moderately non-normal. But if your data were extremely non-normal, r would be less than the critical value. When r is greater than the critical value, you don’t know whether the data set comes from normal data or moderately non-normal data, but either way your inferential statistics procedures are okay.

So the bottom line is, if r > CRIT, treat the data as normal, and if r < CRIT, don’t.

The normal probability plot is just one of many possible ways to determine whether a data set fits the normal model. Another method, the D’Agostino-Pearson test, uses numerical measures of the shape of a data set called skewness and kurtosis to test for normality. For details, see Assessing Normality in Measures of Shape: Skewness and Kurtosis.