Most popular statistical tests are based on the assumption that your data comes from a normal distribution. These tests are unreliable when that assumption is wrong. This is a massive problem with Excel’s native testing capabilities, because Excel does not have a way to test for normality, not even in their Analysis Toolpak (which I discuss in-depth in this 3-part series).
If you try and find information about how to conduct a normality test in Excel, this is what you’ll mostly find:
- Guides that tell you how to create a normal probability plot. While a probability plot can be a nice visual aid, your conclusion is completely subjective. Two people can look at the exact same plot, and arrive at different conclusions about normality.
- Videos or guides that are really just advertisements for an Excel plugin you will have to pay for.
- Lengthy guides explaining how to set up a normality test in Excel which are not very self-explanatory, and that are usually based on the chi-square goodness of fit test, simply because it’s the easiest test to set up in Excel. Unfortunately, this doesn’t mean it’s the best test, and it still takes a lot of time to set up.
Rather than put you through all that, I decided I would just put up a spreadsheet that you can use right away.
This spreadsheet is based on the Shapiro-Wilk test, which is the most powerful of the popular normality tests according to a paper (PDF link) published in the Journal of Statistical Modeling and Analysis.
To get the spreadsheet for free, and to sign up for more practical updates like this one, sign up below.
We will never share your email address with anyone.