To test for normality in Excel, you can use the built-in Data Analysis Toolpak to generate a Histogram and Descriptive Statistics, or create a Q-Q plot manually. The most common statistical tests are the Shapiro-Wilk test (not native to Excel but can be added via a custom function) and the Kolmogorov-Smirnov test (available in the Toolpak). This article explains each method step by step, how to interpret results, and common pitfalls.
Formula
The z-score formula is:
z = (x – μ) / σ
where:
- x = the value you are testing
- μ = the sample mean
- σ = the sample standard deviation
The p-value is the probability of observing a value as extreme as x under the normal distribution. For a two-tailed test, it is calculated as 2 × (1 – Φ(|z|)), where Φ is the cumulative distribution function of the standard normal distribution. In Excel, this is NORM.S.DIST(z, TRUE).
Worked Example
Suppose you have a sample with mean 50, standard deviation 10, and you want to test the value 55.
- Calculate the z-score: z = (55 – 50) / 10 = 0.5
- Find the cumulative probability for z = 0.5 using Excel:
NORM.S.DIST(0.5, TRUE)= 0.6915 - Two-tailed p-value = 2 × (1 – 0.6915) = 0.6170
This p-value is large (>0.05), so the value 55 is not significantly unusual under the normal assumption.
Assumptions
- The sample is randomly drawn from the population.
- The data are continuous (or at least approximately so).
- The test is for a single value; for a full dataset, use the Shapiro-Wilk or Kolmogorov-Smirnov test.
Why Test for Normality?
Many statistical procedures—like t-tests, ANOVA, linear regression, and confidence intervals—assume that the data (or the residuals) follow a normal distribution. If your data deviate significantly, the results of these tests may be misleading. Testing for normality helps you decide whether to use parametric methods or switch to non-parametric alternatives like the Mann-Whitney U test or Kruskal-Wallis test.
Excel Methods for Normality Testing
Excel does not have a single “normality test” button, but you can use several approaches:
- Histogram with normal curve overlay – Visual check.
- Q-Q plot (quantile-quantile plot) – Visual check comparing your data quantiles to theoretical normal quantiles.
- Descriptive Statistics – Skewness and kurtosis values can indicate non-normality (values between -2 and +2 are often considered acceptable).
- Kolmogorov-Smirnov test – Available in the Data Analysis Toolpak (as “K-S Test” in some versions, but often you must run it manually).
- Shapiro-Wilk test – Not built-in, but you can install the Real Statistics Resource Pack or use a custom VBA function.
Step-by-Step: Using the Data Analysis Toolpak
- Go to File > Options > Add-ins.
- Select “Analysis ToolPak” and click “Go”. Check the box and click OK.
- Now under the Data tab, you’ll see “Data Analysis”.
- Choose “Descriptive Statistics” to get skewness and kurtosis.
- To create a histogram, select “Histogram” and choose your data range.
- To compare with a normal curve, you can add a column of expected frequencies using the NORM.DIST function.
For a Q-Q plot, sort your data, compute the percentile for each point (using RANK or PERCENTRANK), then plot the data values against the theoretical quantiles from NORM.S.INV.
Interpreting the Results
For a formal test like Shapiro-Wilk or Kolmogorov-Smirnov, the null hypothesis is that the data are normally distributed. If the p-value is less than your significance level (commonly 0.05), you reject the null and conclude the data are not normal. If p > 0.05, you do not have enough evidence to say they are non-normal, but this does not prove normality—it just means the deviation is not significant.
For skewness and kurtosis, values close to 0 indicate symmetry and normal tail weight. A skewness > 2 or < -2 suggests significant asymmetry. Kurtosis > 2 or < -2 indicates heavy tails or light tails.
Common Mistakes and Misconceptions
- Mistaking a large sample for normality – The Central Limit Theorem says the sampling distribution of the mean becomes normal, but the raw data may still be non-normal.
- Using a single test only – It’s better to combine visual checks (histogram, Q-Q plot) with a formal test.
- Ignoring outliers – Outliers can heavily influence normality tests; check for them first.
- Forgetting that normality tests are sensitive to sample size – With large samples, even trivial deviations become significant; with small samples, they may miss real deviations.
Alternatives to Excel for Normality Testing
If you need more robust tests or easier workflows, consider:
- R – Has the
shapiro.test()andks.test()functions. - Python (SciPy) –
scipy.stats.shapiro()andscipy.stats.kstest(). - SPSS, Minitab, JMP – All include normality tests with graphical output.
But for quick checks, Excel is perfectly adequate, especially with the Toolpak.
Frequently Asked Questions
Does Excel have a Shapiro-Wilk test?
No, Excel does not include the Shapiro-Wilk test natively. You can download the Real Statistics Resource Pack (a free Excel add-in) that adds it, or you can run it via VBA code. Alternatively, use the Kolmogorov-Smirnov test which is available in the Toolpak.
What is the difference between Shapiro-Wilk and Kolmogorov-Smirnov?
Shapiro-Wilk is generally more powerful for detecting non-normality, especially with small samples (n < 50). Kolmogorov-Smirnov is less powerful but works for any sample size and can also compare two samples. In Excel, the Toolpak’s K-S test is actually a one-sample test against a specified distribution.
Can I use a histogram alone to test normality?
A histogram is a visual aid, not a formal test. It helps you see skewness, multimodality, or outliers, but it cannot give you a p-value. Always accompany a histogram with a formal test or at least skewness/kurtosis values.
What if my data is not normal?
You have several options: transform the data (e.g., log, square root, Box-Cox), use non-parametric tests (e.g., Mann-Whitney, Kruskal-Wallis), or use robust methods that do not assume normality (e.g., trimmed means, bootstrapping). The choice depends on your research question and data type.
Is normality testing necessary for large samples?
With very large samples (say n > 200), the Central Limit Theorem ensures that the mean is approximately normally distributed, so many parametric tests are valid even if the raw data are not normal. However, for regression and ANOVA, the residuals still need to be approximately normal for valid inference. So it’s still worth checking, but interpret the test with caution—minor deviations may be flagged as significant.
How do I create a Q-Q plot in Excel?
- Sort your data in ascending order.
- Compute the percentile for each point: (rank – 0.5) / n.
- Use
NORM.S.INV(percentile)to get the theoretical z-scores. - Plot your data values (y-axis) against the theoretical z-scores (x-axis) as a scatter plot.
- If the points roughly follow a straight line, the data are normal.
Related
Formula used
in m | Sample mean | - | 50 | -1000000 in s | Sample standard deviation | - | 10 | 0.0001 in n | Sample size | - | 30 | 3 in x | Value to test | - | 55 | -1000000 out z | Z-score of x | - | (x - m) / s | 4 out p | Two-tailed p-value | - | 2 * (1 - normdist(abs(z))) | 4
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation with the constants and conversion factors written on the page, rounded as each result states. See method and constants, the disclaimer or report an error.
More in this section
- Normality to g/L Converter & Formula29.220 g/L
- Normality to meq/mL Conversion Calculator1.0000 meq/mL
- Normality Test Q-Q Plot: How to Read It
- Normality Test Results: How to Analyze Them
- Normality Slay the Spire?
- Normality Test for Paired t Test: How to Check