P-Values in Excel
Excel produces p-values from four different places, and reaching for the wrong one still returns a clean, confident number with no error attached. Two averages compared across separate groups is T.TEST(array1, array2, tails, type), which hands back the probability itself rather than a t-statistic; counts in a contingency table is CHISQ.TEST(actual_range, expected_range), which wants the expected counts built by hand as row total x column total / grand total; two spreads compared is F.TEST. Regression coefficients and correlations have no worksheet function at all and come from Data > Data Analysis > Regression once the Analysis ToolPak is switched on. A statistic you already hold converts with T.DIST.2T or NORM.S.DIST.
The formula
=T.TEST(A2:A31, B2:B31, 2, 3) two independent groups, two-tailed, variances not assumed equal
=T.TEST(A2:A31, B2:B31, 2, 1) the SAME subjects measured twice — type 1 is the paired test
=CHISQ.TEST(B2:D4, B8:D10) observed counts against expected counts you computed yourself
=F.TEST(A2:A31, B2:B31) are the two variances equal? the question type 2 versus type 3 turns on
=T.DIST.2T(ABS(B1), B2) a p-value from a t-statistic in B1 with the degrees of freedom in B2
=2*(1-NORM.S.DIST(ABS(B1), TRUE)) the same from a z-score
=T.DIST.2T(ABS(B1*SQRT((B2-2)/(1-B1^2))), B2-2) is the correlation r in B1, over n rows in B2, significant?A worked example
A2:A31 holds thirty conversion rates recorded under the old page layout and B2:B31 thirty recorded under the new one. Different visitors saw each layout, and nothing suggests the two spreads match.
=T.TEST(A2:A31, B2:B31, 2, 3)
0.0312 — the probability of a gap this large between the two samples if the layouts genuinely performed alike. Against a 0.05 threshold fixed before the test ran, that counts. Change the final argument to 1 and Excel returns a paired-test p-value instead, pairing row 2 with row 2 and so on down: a different question, and a meaningless one here, since these are not the same visitors. No error appears — the wrong type returns a clean number.
Which one do I need?
| If you want to… | Use |
|---|---|
| Two groups of different subjects, comparing their averages | =T.TEST(range1, range2, 2, 3). Type 3 makes no equal-variance assumption, which is the safer default |
| One group measured before and after | The paired test, =T.TEST(before, after, 2, 1) — row 2 is compared with row 2, so the rows must line up |
| The hypothesis has a direction — "higher", not merely "different" | Set tails to 1, which halves the p-value. Decide this before looking at the data, or the halving is simply a thumb on the scale |
| Counts in a contingency table — is outcome related to group? | =CHISQ.TEST(observed, expected), building the expected table as row total x column total / grand total |
| The significance of a regression coefficient | Data > Data Analysis > Regression: the P-value column covers each coefficient, Significance F the model as a whole |
| Whether a correlation is real or noise | CORREL returns r and no p. Convert it: t = r*SQRT((n-2)/(1-r^2)), then =T.DIST.2T(ABS(t), n-2) |
| You already hold a t-statistic or a z-score | =T.DIST.2T(ABS(t), df) for the first, =2*(1-NORM.S.DIST(ABS(z),TRUE)) for the second |
| There is no Data Analysis button on the Data tab | File > Options > Add-ins > Manage: Excel Add-ins > Go > tick Analysis ToolPak; on a Mac it is Tools > Excel Add-ins |
| Three or more groups | Running t-tests on every pair inflates the false-positive rate. Data > Data Analysis > ANOVA: Single Factor reports one P-value for the whole comparison |
| Comparing spread rather than averages | =F.TEST(range1, range2) gives the p-value for the two variances being equal |
Frequently asked questions
Is there a P.VALUE function in Excel?
No, and nothing equivalent either. T.TEST returns a p-value directly when two means are being compared, CHISQ.TEST does so for a contingency table and F.TEST for two variances — all three give the probability itself, not a statistic you then look up. Regression and ANOVA p-values live in the Analysis ToolPak rather than in any worksheet function, which is why they appear nowhere until the add-in is switched on.
What does the type argument in T.TEST do?
It selects the test. 1 is paired, for the same subjects measured twice; 2 is a two-sample test that assumes both groups share a variance; 3 is a two-sample test that does not. Type 3 is the safer default for independent groups, because the equal-variance assumption behind type 2 is rarely checked — F.TEST is what checks it. Excel accepts whichever number you type without ever asking whether the design matches.
Why does CHISQ.TEST need two ranges instead of one table?
The first range is what you observed, the second is what you would expect if the two variables were unrelated, and Excel does not derive the second for you. Build it over the same shape as the observed table, each cell being its row total times its column total divided by the grand total. Passing the observed table twice returns a p-value of 1: correct for two identical tables, and an answer to no question you meant to ask.
Does a p-value under 0.05 prove the result is real?
No. It says the data would be surprising if the null hypothesis were true; it is not the probability that the hypothesis is false, and 0.05 is a convention rather than a natural constant. Two habits quietly break it: choosing the threshold after seeing the number, and testing the same data repeatedly until one comparison lands under the line. Fix the threshold and the number of comparisons before calculating anything.
New guides and tools, once a month
DE + EN · double opt-in · no spam