Spreadsheet How-To
How to Calculate a P-Value in Excel and Google Sheets
Excel turns a test statistic into a p-value with one distribution function: =T.DIST.2T(ABS(t),df) for a two-tailed t-test, =2*(1-NORM.S.DIST(ABS(z),TRUE)) for a z-test, =CHISQ.DIST.RT(x,df) for chi-square and =F.DIST.RT(F,df1,df2) for ANOVA. If you have the raw data rather than the statistic, T.TEST and CHISQ.TEST return the p-value directly.
From a test statistic to a p-value
| You have | Excel formula | Google Sheets formula |
|---|---|---|
| z, two-tailed | =2*(1-NORM.S.DIST(ABS(z),TRUE)) | =2*(1-NORMSDIST(ABS(z))) |
| z, right-tailed | =1-NORM.S.DIST(z,TRUE) | =1-NORMSDIST(z) |
| t, two-tailed | =T.DIST.2T(ABS(t),df) | =T.DIST.2T(ABS(t),df) |
| t, right-tailed | =T.DIST.RT(t,df) | =T.DIST.RT(t,df) |
| Chi-square, right-tailed | =CHISQ.DIST.RT(x,df) | =CHIDIST(x,df) |
| F, right-tailed | =F.DIST.RT(F,df1,df2) | =F.DIST.RT(F,df1,df2) |
Chi-square and F tests reject only for large values, so their p-value is always the right tail. For a left-tailed t or z test, the p-value is 1 minus the right-tailed p-value. Replace df, t and the other names with cell references.
Worked examples
| Statistic | Formula | p-value |
|---|---|---|
| z = 1.96 | =2*(1-NORM.S.DIST(1.96,TRUE)) | 0.049996 two-tailed; 0.024998 in one tail |
| t = 2, 8 df | =T.DIST.2T(2,8) | 0.0805 two-tailed; T.DIST.RT(2,8) = 0.0403 |
| χ² = 13.4, 5 df | =CHISQ.DIST.RT(13.4,5) | 0.0199 |
| F = 12, 2 and 6 df | =F.DIST.RT(12,2,6) | 0.0080 |
| r = 0.7746, n = 5 | =T.DIST.2T(ABS(r)*SQRT((n-2)/(1-r^2)),n-2) | 0.1240 (t = 2.1213, 3 df) |
The last row turns a correlation coefficient into a p-value through its t statistic, t = r√((n − 2)/(1 − r²)), which has n − 2 degrees of freedom. You can confirm each row with the p-value calculator.
From the raw data
| Test | Function | Returns |
|---|---|---|
| t-test on two ranges | =T.TEST(range1,range2,tails,type) | The p-value; type 1 paired, 2 equal variances, 3 unequal variances |
| Chi-square from counts | =CHISQ.TEST(observed,expected) | The p-value; you build the expected counts first |
| One-sample z-test | =Z.TEST(range,mu,sigma) | The one-tailed p-value |
| Equality of two variances | =F.TEST(range1,range2) | The two-tailed p-value |
These functions return a p-value, not the test statistic, so add the statistic separately if you need to report it. The next guides walk through the t-test in Excel and the chi-square test in Excel.
Microsoft's own Z.TEST example uses the values 3, 6, 7, 8, 6, 5, 4, 2, 1 and 9 with a hypothesized mean of 4. =Z.TEST(A2:A11,4) returns 0.090574 and the two-tailed formula above returns 0.181148. With sigma omitted Excel uses the sample standard deviation, so a one-sample t-test on the same data is the better choice for so few values, and it gives a two-tailed p-value of 0.2140.
Critical values for the same tests
| Distribution (α = 0.05) | Excel formula | Result |
|---|---|---|
| z, two-tailed | =NORM.S.INV(0.975) | 1.9600 |
| t, two-tailed, 8 df | =T.INV.2T(0.05,8) | 2.3060 |
| Chi-square, 5 df | =CHISQ.INV.RT(0.05,5) | 11.0705 |
| F, 2 and 6 df | =F.INV.RT(0.05,2,6) | 5.1433 |
In Google Sheets use CHIINV(0.05,5) in place of CHISQ.INV.RT; the other three functions exist under the same names. A test statistic beyond the critical value has a p-value below α.
Mistakes to avoid
- #NUM! from T.DIST.2T. The function rejects a negative t, so wrap the statistic in ABS().
- Doubling the wrong tail. A two-tailed p-value is twice the smaller tail. For a symmetric distribution like t or z, that is 2 × the right tail of |t| or |z|.
- Using the wrong degrees of freedom. The p-value depends on them; see degrees of freedom explained.
- Reading the p-value as the probability that the null hypothesis is true. It is the probability of data at least this extreme if the null hypothesis were true; see the p-value explained.
- Older function names. In Excel, TDIST, CHIDIST and FDIST still work but were replaced by the dotted names above, which state the tail in the name.
Try the P-Value Calculator
Convert a z, t, chi-square or F statistic into a one- or two-tailed p-value.
Try the T-Test Calculator
Run a one-sample, two-sample or paired t-test and see the t statistic and p-value.
Frequently Asked Questions
How do I get a p-value from a t statistic in Excel?
Use =T.DIST.2T(ABS(t),df) for a two-tailed p-value and =T.DIST.RT(t,df) for a right-tailed one. A t of 2 with 8 degrees of freedom gives 0.0805 two-tailed and 0.0403 right-tailed. T.DIST.2T returns #NUM! for a negative t, which is why the ABS() wrapper is needed.
How do I get the p-value of a z-score in Excel?
For a two-tailed p-value use =2*(1-NORM.S.DIST(ABS(z),TRUE)); for the right tail use =1-NORM.S.DIST(z,TRUE). A z of 1.96 gives 0.0500 two-tailed and 0.0250 in one tail. In Google Sheets replace NORM.S.DIST(z,TRUE) with NORMSDIST(z).
Why does Excel show a p-value of 0 or 0.000?
The cell is formatted with too few decimals, and the true p-value is very small but not zero. Switch the number format to Scientific, or widen the decimals, to see a value like 3.08E-04. In a report, write p < 0.001 instead of p = 0.
Where is the p-value in the Data Analysis output?
In every Analysis ToolPak table it is labelled with a P-value or a probability. A t-test table has P(T<=t) one-tail and P(T<=t) two-tail, an ANOVA table has a P-value column next to F, and a regression table shows a P-value for each coefficient plus Significance F for the model as a whole.
Is Z.TEST in Excel a one-tailed or two-tailed test?
It returns a one-tailed p-value, the probability that the sample mean would exceed the observed one. For a two-tailed value use =2*MIN(Z.TEST(range,mu,sigma),1-Z.TEST(range,mu,sigma)). If you leave sigma out, Excel plugs in the sample standard deviation, which is not a proper z-test for a small sample, so prefer a t-test then.
What p-value counts as significant?
Compare the p-value with the significance level α that you chose before looking at the data, usually 0.05. If p is below α the result is called statistically significant. The threshold is a convention, not a law, and a small p-value says nothing about the size or importance of an effect.