Spreadsheet How-To
How to Run a T-Test in Excel (T.TEST and Data Analysis ToolPak)
Type =T.TEST(array1,array2,tails,type) to get the p-value of a t-test. Use tails = 2 for a two-tailed test, and type 1 for paired data, type 2 for two groups with equal variances or type 3 for unequal variances (Welch). For the groups 5, 6, 7, 8, 9 and 3, 4, 5, 6, 7, =T.TEST(A2:A6,B2:B6,2,3) returns 0.0805, which is not significant at the 5% level.
The T.TEST function
| Argument | What to enter |
|---|---|
| array1, array2 | The two ranges of values. For a paired test they must have the same length. |
| tails | 1 for a one-tailed test, 2 for a two-tailed test. Any other value gives #NUM!. |
| type | 1 = paired, 2 = two-sample with equal variance, 3 = two-sample with unequal variance. |
T.TEST returns only the p-value. Microsoft's own example pairs the nine values 3, 4, 5, 8, 9, 1, 2, 4, 5 with 6, 19, 3, 2, 14, 4, 5, 17, 1 and gives =T.TEST(A2:A10,B2:B10,2,1) = 0.196016, which is the p-value of a paired, two-tailed test (t = −1.41, 8 df).
If the logic behind these numbers is new, read hypothesis testing first, and which statistical test to use if you are not sure a t-test is the right one. The functions that turn a test statistic into a p-value are covered in p-value in Excel.
Example 1: two independent groups
Put the values 5, 6, 7, 8, 9 in A2:A6 and 3, 4, 5, 6, 7 in B2:B6. The means are 7 and 5 and both variances are 2.5.
=T.TEST(A2:A6,B2:B6,2,3) returns 0.0805
=T.TEST(A2:A6,B2:B6,1,3) returns 0.0403
t = (7 − 5) / √(2.5/5 + 2.5/5) = 2.000 with 8 degrees of freedom
A p-value of 0.0805 is above 0.05, so the difference of 2 points is not statistically significant at the 5% level; the 95% confidence interval for the difference, −0.31 to 4.31, includes zero. Types 2 and 3 agree here because the groups have equal sizes and equal variances. The two-sample t-test calculator shows the interval and every intermediate step.
Example 2: paired measurements
Six people are weighed before (82, 90, 75, 88, 79, 85) and after (78, 85, 74, 80, 77, 79) a programme. Each person appears once in each column, so use type 1.
=T.TEST(A2:A7,B2:B7,2,1) returns 0.0093
Mean difference = 4.3333, t = 4.111, 5 degrees of freedom
The p-value 0.0093 is below 0.05, so the average drop of 4.3 is statistically significant. Using the one-tailed argument gives 0.0046, exactly half, which is only legitimate if a drop was the single direction you predicted before seeing the data.
Why type 3 is the safer choice
Type 2 pools the two variances, which is fine when they are similar and dangerous when the smaller group is the noisier one. Take a made-up data set with 20 values in column A and 6 in column B:
A: 42 48 50 50 45 49 47 48 53 48 50 53 48 50 50 50 46 50 54 45
B: 67 57 46 83 66 38
Means 48.8 and 59.5; standard deviations 2.91 and 16.13
Type 2: t = −2.94, 24 df, p = 0.0071
Type 3: t = −1.62, 5.10 df, p = 0.1658
The pooled test is dominated by the large, tight group and understates the uncertainty in the small, noisy one, so it calls the difference significant. Welch's test estimates each group's variance separately and lowers the degrees of freedom to 5.10, which restores an honest answer. That is why the default should be type 3.
The Data Analysis ToolPak
- Load the add-in once: File, Options, Add-ins, choose Excel Add-ins in the Manage box, click Go, tick Analysis ToolPak and click OK. On a Mac use Tools, Excel Add-ins.
- Open the Data tab, click Data Analysis and pick t-Test: Paired Two Sample for Means, t-Test: Two-Sample Assuming Equal Variances or t-Test: Two-Sample Assuming Unequal Variances.
- Select the two input ranges, enter 0 as the Hypothesized Mean Difference, tick Labels if the ranges include headers, leave Alpha at 0.05 and choose an output location.
The table for the equal-variance test on Example 1 reads:
| Row | Variable 1 | Variable 2 |
|---|---|---|
| Mean | 7 | 5 |
| Variance | 2.5 | 2.5 |
| Observations | 5 | 5 |
| Pooled Variance | 2.5 | |
| Hypothesized Mean Difference | 0 | |
| df | 8 | |
| t Stat | 2 | |
| P(T<=t) one-tail | 0.0403 | |
| t Critical one-tail | 1.8595 | |
| P(T<=t) two-tail | 0.0805 | |
| t Critical two-tail | 2.3060 |
Compare t Stat with t Critical two-tail, or the P(T<=t) two-tail row with your α. Here 2.000 is below 2.3060 and 0.0805 is above 0.05, and both say the same thing. The ToolPak output is static: it does not update when the data change, so rerun it or use the T.TEST formula for a live result.
Mistakes and errors
- Using type 2 or 3 for paired data. Before-and-after values on the same subjects are not independent groups, and the independent test throws away the pairing.
- Reading a one-tailed p-value without checking the direction. T.TEST always reports the tail of |t|, so a difference in the unexpected direction still looks significant.
- Skipping the assumptions. With small samples the data should be roughly normal; if they are clearly skewed, use the Mann-Whitney U test or the Wilcoxon signed-rank test instead.
- Confusing significance with size. A small p-value does not mean a large effect. Report the mean difference and its interval as well, and see effect size.
Try the T-Test Calculator
Run a one-sample, two-sample or paired t-test and see the t statistic, degrees of freedom, p-value and confidence interval.
Try the Two-Sample T-Test Calculator
Compare two independent group means from raw data or summary statistics, with Welch's correction.
Frequently Asked Questions
Which T.TEST type should I use, 2 or 3?
Use type 3, the unequal-variance (Welch) test, unless you have a strong reason to believe the two groups have the same variance. It gives almost the same answer as type 2 when the variances and group sizes are similar, and a trustworthy one when they are not. Type 1 is for paired measurements, not for the equal or unequal choice.
Should I choose one tail or two tails?
Choose two tails unless you decided before collecting data that only one direction matters. Excel's one-tailed value is simply half the two-tailed value whichever way the difference points, so check that the observed difference goes the way your hypothesis predicted; otherwise the halved p-value is misleading.
Does T.TEST return the t statistic?
No, only the p-value. For two groups compute the statistic yourself: =(AVERAGE(A2:A6)-AVERAGE(B2:B6))/SQRT(VAR.S(A2:A6)/COUNT(A2:A6)+VAR.S(B2:B6)/COUNT(B2:B6)). It is 2 for the example groups. The Data Analysis ToolPak reports t Stat, df and both critical values as well.
How do I run a one-sample t-test in Excel?
Neither T.TEST nor the ToolPak has a one-sample option, so combine two functions: =T.DIST.2T(ABS(AVERAGE(A2:A6)-mu)/(STDEV.S(A2:A6)/SQRT(COUNT(A2:A6))),COUNT(A2:A6)-1), with mu replaced by the value you are testing against. For the values 5, 6, 7, 8, 9 tested against 5 it returns 0.0474.
Why does my paired T.TEST return #N/A?
A paired test needs the same number of values in both ranges, one pair per subject. If array1 and array2 have different lengths and type is 1, Excel returns #N/A. Delete any subject with a missing measurement from both columns, or use a two-sample type instead if the groups are really independent.
Is it the same in Google Sheets?
Yes. Google Sheets has T.TEST(range1,range2,tails,type) with the same arguments and the same meaning of type (1 paired, 2 equal variance, 3 unequal variance), plus the older name TTEST. The Analysis ToolPak is an Excel add-in and does not exist in Google Sheets.