How to do a t-test in Google Sheets

The T.TEST function

Google Sheets has one built-in function for t-tests. TTEST is the older name and works the same way.

=T.TEST(range1, range2, tails, type)

The catch: T.TEST only returns the p-value. Your teacher, reviewer or report will also ask for the t value, the degrees of freedom, the group means and an effect size. The steps below show how to get each one.

Example 1: two independent groups

Twelve students were taught with Method A and twelve other students with Method B. Here are their exam scores, in columns A and B (rows 2 to 13):

Step 1: describe each group

Step 2: get the p-value

=T.TEST(A2:A13, B2:B13, 2, 3)

Result: 0.0011. The difference is significant at the 0.05 level (and even at 0.01). With type 2 you get 0.0011 as well, because the two standard deviations are close.

Step 3: calculate the t statistic and degrees of freedom

For the Welch test (type 3), the t statistic is the difference between the means divided by its standard error:

=(AVERAGE(B2:B13)-AVERAGE(A2:A13))/SQRT(VAR.S(A2:A13)/COUNT(A2:A13)+VAR.S(B2:B13)/COUNT(B2:B13))

Result: t = 3.77. The Welch degrees of freedom are not a whole number. Put =VAR.S(A2:A13)/COUNT(A2:A13) in E2 and =VAR.S(B2:B13)/COUNT(B2:B13) in E3, then:

=(E2+E3)^2/(E2^2/(COUNT(A2:A13)-1)+E3^2/(COUNT(B2:B13)-1))

Result: df = 21.89. With the equal-variance version (type 2), df is simply n₁ + n₂ − 2 = 22, and you can check the p-value with =T.DIST.2T(3.77, 22), which also gives 0.0011.

Step 4: add the effect size (Cohen's d)

A p-value tells you whether there is a difference, not how big it is. Cohen's d divides the difference by the pooled standard deviation:

=(AVERAGE(B2:B13)-AVERAGE(A2:A13))/SQRT(((COUNT(A2:A13)-1)*VAR.S(A2:A13)+(COUNT(B2:B13)-1)*VAR.S(B2:B13))/(COUNT(A2:A13)+COUNT(B2:B13)-2))

Result: d = 1.54. As a rule of thumb, 0.2 is small, 0.5 medium and 0.8 large, so this is a large effect.

Step 5: the confidence interval of the difference

The mean difference is 80.67 − 74.42 = 6.25 points. Its 95% confidence interval (equal-variance version) is the difference ± T.INV.2T(0.05, 22) × pooled SD × SQRT(1/12+1/12), which gives [2.81, 9.69]. Because the interval does not include 0, it agrees with the significant p-value.

Example 2: paired t-test (before and after)

Use a paired test when the same people are measured twice. Ten students took a test before and after a revision workshop. Each student's two scores must be on the same row:

=T.TEST(A2:A11, B2:B11, 2, 1)

Result: p = 0.0001. To get t, put the differences in column C (=B2-A2, filled down), then:

=AVERAGE(C2:C11)/(STDEV.S(C2:C11)/SQRT(COUNT(C2:C11)))

Result: t(9) = 6.55. The mean improvement is 4.5 points (SD = 2.17), 95% CI [2.95, 6.05]. The paired effect size d_z = mean difference ÷ SD of the differences = 2.07.

Check the assumptions first

• Independence: each value comes from a different person (or, for a paired test, each pair does).
• Normality: each group (or the differences, for a paired test) should look roughly normal. With small samples, test it with a Shapiro-Wilk test. In Example 1, both groups pass (p = .92 and p = .98).
• Outliers: a single extreme value can move a mean a lot. Look at a box plot before testing.
• Equal variances (type 2 only): =F.TEST(A2:A13, B2:B13) returns the p-value of an F-test for equal variances (0.81 here). When in doubt, use Welch (type 3).

If the data are clearly not normal and the samples are small, use the Mann-Whitney U test for independent groups, or the Wilcoxon signed-rank test for paired data.

How to report the result (APA style)

Report p-values with three decimals (p = .001) and write p < .001 when the value is smaller. Never write p = .000.

Common mistakes

• Reporting the T.TEST result as “t”. It is the p-value.
• Using type 2 or 3 on paired data, or type 1 on two different groups of people.
• Choosing a one-tailed test after seeing which group is higher.
• Running many t-tests to compare three or more groups. Use a one-way ANOVA instead.

Frequently asked questions

What does T.TEST return in Google Sheets?

T.TEST returns only the p-value. It does not give the t statistic, the degrees of freedom, the means or an effect size. You have to calculate those with separate formulas, or use an add-on that outputs the full table.

Which type should I use in T.TEST?

Use type 1 when the same people are measured twice (paired data). For two independent groups, type 3 (Welch, unequal variances) is the safer default; type 2 assumes both groups have the same variance.

Should I use one tail or two tails?

Use two tails (tails = 2) unless you stated a direction before looking at the data. A one-tailed p-value is half the two-tailed one, so choosing it after seeing the result inflates false positives.

How do I run a one-sample t-test in Google Sheets?

There is no one-sample option in T.TEST. Compute t = (mean - reference value) / (STDEV.S / SQRT(n)) and get the p-value with =T.DIST.2T(ABS(t), n-1).