There is no normality test in Google Sheets. The Shapiro-Wilk test can be built with SORT, NORM.S.INV, SUMPRODUCT and DEVSQ (steps below). If the p-value is below 0.05, the data are not normally distributed. With ExplainStats, the test runs automatically before every t-test and ANOVA.
Why test normality?
The t-test, ANOVA and linear regression assume that the data (or the residuals) are roughly normal. With small samples, a clear departure from normality can make their p-values wrong. The Shapiro-Wilk test is the most widely recommended normality test for small and medium samples.
The layout
Your data are in A2:A21 (20 exam scores). We use columns B to E for the calculation and G:H for the results.
| Column | Content | Formula in row 2 |
|---|---|---|
| B | rank i | =SEQUENCE(COUNT(A2:A21)) |
| C | sorted data x(i) | =SORT(A2:A21) |
| D | normal scores mi | =NORM.S.INV((B2-0.375)/($H$2+0.25)), filled down |
| E | weights ai | see Step 3, filled down |
SEQUENCE and SORT fill the whole column by themselves. Leave the cells below them empty.
Step by step
Step 1: sample size and helper values
| Cell | Meaning | Formula | Result |
|---|---|---|---|
| H2 | n | =COUNT(A2:A21) | 20 |
| H3 | sum of mi² | =SUMSQ(D2:D21) | 17.6336 |
| H4 | u = 1/√n | =1/SQRT(H2) | 0.2236 |
Step 2: the two largest weights
Royston's method uses polynomial corrections for the two extreme weights. D21 and D20 are the last two normal scores (for n = 20):
H5 (a_n): =D21/SQRT(H3)+0.221157*H4-0.147981*H4^2-2.07119*H4^3+4.434685*H4^4-2.706056*H4^5
H6 (a_n-1): =D20/SQRT(H3)+0.042981*H4-0.293762*H4^2-1.752461*H4^3+5.682633*H4^4-3.582633*H4^5
H7 (phi): =(H3-2*D21^2-2*D20^2)/(1-2*H5^2-2*H6^2)Results: an = 0.4734, an−1 = 0.3217, phi = 19.4712.
Step 3: all the weights
The two smallest weights are the negatives of the two largest; the others are mi / √phi. In E2, fill down to E21:
=IF(B2=1,-$H$5,IF(B2=2,-$H$6,IF(B2=$H$2,$H$5,IF(B2=$H$2-1,$H$6,D2/SQRT($H$7)))))Step 4: the W statistic
H8 (W): =SUMPRODUCT(E2:E21,C2:C21)^2/DEVSQ(A2:A21)Result: W = 0.9743. W is always between 0 and 1; values close to 1 mean the data look normal.
Step 5: the p-value
For 12 to 5,000 values, Royston's approximation turns W into a z-score:
H9 (mu): =0.0038915*LN(H2)^3-0.083751*LN(H2)^2-0.31082*LN(H2)-1.5861
H10 (sigma): =EXP(0.0030302*LN(H2)^2-0.082676*LN(H2)-0.4803)
H11 (z): =(LN(1-H8)-H9)/H10
H12 (p): =1-NORMSDIST(H11)Result: p = 0.841. Since p is well above 0.05, there is no evidence against normality. These formulas reproduce the Shapiro-Wilk results of SciPy and R (we checked them against SciPy on several datasets).
Change every A2:A21, D2:D21, C2:C21 and E2:E21 to your range, and replace D21 and D20 in Step 2 with the last and second-to-last cells of column D. The p-value formula is not valid below 12 values.
Select your data, click Run: full results table, assumption checks, a plain-language interpretation and an APA line, free in Google Sheets.
A visual check: the Q-Q plot
A test gives a yes/no answer; a plot shows how the data depart from normality. You already have what you need: select columns C and D, choose Insert › Chart › Scatter chart, and in the chart editor set column D (normal scores) as the X-axis and column C (sorted data) as the series. If the points fall close to a straight line, the data are close to normal. A curve suggests skewness; points bending away at both ends suggest heavy tails.
How to read the result
- p ≥ 0.05: no evidence against normality. You can use the t-test or ANOVA.
- p < 0.05: the data are not normal. With small samples, use a non-parametric test such as the Mann-Whitney U test or Kruskal-Wallis. With large samples (more than about 30 per group), the t-test and ANOVA are usually robust anyway.
- With very large samples, Shapiro-Wilk flags even tiny, harmless departures. Look at the Q-Q plot too.
How to report it
A Shapiro-Wilk test indicated that exam scores did not deviate significantly from normality, W = .97, p = .841.
Frequently asked questions
Does Google Sheets have a Shapiro-Wilk function?
No. Google Sheets has no normality test function. You can build the Shapiro-Wilk test with the formulas in this guide, or use an add-on that runs it for you.
What sample size does this method work for?
The p-value formula in this guide (Royston's approximation) is valid from 12 to 5,000 values. Smaller samples need a different p-value formula.
What does a significant Shapiro-Wilk test mean?
If p < 0.05, the data are unlikely to come from a normal distribution. If p ≥ 0.05, there is no evidence against normality; it does not prove the data are normal.
Should I test normality on the raw data or on the residuals?
For a t-test, test each group separately (or the differences, for a paired test). For ANOVA, test each group or the residuals. For regression, test the residuals.
Select your data, click Run: full results table, assumption checks, a plain-language interpretation and an APA line, free in Google Sheets.