How to Do a T-Test in Excel and Read the P-Value

A t-test in Excel tells you whether the difference between two averages is bigger than you'd expect from random variation alone. You can run one with a single formula, T.TEST, or with the Analysis ToolPak, and both give you a p-value. This guide shows both methods on one small example, explains how to choose the tails and type, and shows how to read the p-value without overstating it.

What does a t-test tell you?#

A t-test compares the averages of two sets of numbers and asks: if there were really no difference between the groups, how unusual would a gap this big be? The answer comes as a p-value, a number between 0 and 1. A small p-value means the gap would be unusual if the groups were truly the same.

Here is the example used throughout. Seven learners were taught with Method A and seven different learners with Method B, then all took the same quiz:

Start with the plain numbers. =AVERAGE(B2:B8) gives 70.86 and =AVERAGE(C2:C8) gives 77.00, so Method B scored about 6.1 points higher. The AVERAGE function guide covers the basics, and the average, min and max lesson practises them. The medians, 71 and 77, tell the same story, so no single extreme score is pulling the averages. The guide to average vs median explains why that check matters.

How to use the T.TEST function#

T.TEST takes the two ranges plus two settings and returns the p-value directly:

=T.TEST(array1, array2, tails, type)

For the quiz data:

=T.TEST(B2:B8,C2:C8,2,3)

The result is about 0.0039. With type 2 instead of 3, the answer is almost identical here, because the two groups spread out by a similar amount (standard deviations of about 3.2 and 3.3).

T.TEST works in Excel 2010 and later, including Microsoft 365 and Excel for Mac. Older files may use TTEST, which takes the same arguments and gives the same result.

How to choose the tails and type#

The tails and type settings change the answer, so choose them for the design of your study, not for the result you hope to get.

Tails. Use 2 in almost every case. A two-tailed test asks "are the averages different in either direction?" Use 1 only if you decided before collecting data that only one direction mattered. A one-tailed p-value is half the two-tailed one (about 0.0020 here), so picking it after seeing the data makes weak evidence look stronger.

Type. Match it to how the data was collected:

• Type 1, paired. The same people or items measured twice, such as each learner before and after training. Each row must belong together, and both ranges need the same number of values or T.TEST returns #N/A.
• Type 2, equal variance. Two separate groups that you have good reason to believe vary by the same amount.
• Type 3, unequal variance (often called Welch's t-test). Two separate groups, with no assumption about their spread. When unsure, use this one.

The choice matters. If the quiz data were seven learners tested twice, a paired test, =T.TEST(B2:B8,C2:C8,2,1), would give about 0.019 instead of 0.0039. It's a different question, so it gets a different answer.

How to read the p-value correctly#

The p-value is the probability of seeing a difference at least as large as yours if the true averages were equal. For the quiz data, p ≈ 0.0039 means that if both methods really produced the same average score, a gap of 6.1 points or more would turn up in only about 0.4% of samples like this one.

The usual rule is to call a result statistically significant when p is below 0.05, a cut-off chosen before the test. By that rule, the quiz result is significant.

Three things a p-value does not tell you:

1. It isn't the chance that the result is a fluke. A p-value of 0.0039 doesn't mean a 0.39% chance that Method B is no better.
2. It isn't the size of the effect. A tiny, unimportant difference can have a small p-value if the sample is huge. Always report the difference itself: 6.1 points.
3. A p-value above 0.05 doesn't prove the groups are equal. It only means this data doesn't give strong evidence of a difference. Small samples often miss real differences.

How to run a t-test with the Analysis ToolPak#

The Analysis ToolPak gives a full results table, including the t statistic that T.TEST doesn't show. It is an add-in that comes with desktop Excel but is switched off by default.

1. Turn it on. Windows: File > Options > Add-ins, choose Excel Add-ins in the Manage box, click Go and tick Analysis ToolPak. Mac: Tools > Excel Add-ins and tick Analysis ToolPak.
2. Go to Data > Data Analysis and pick t-Test: Two-Sample Assuming Unequal Variances (or the Paired or Equal Variances version).
3. Set Variable 1 Range to B1:B8 and Variable 2 Range to C1:C8, tick Labels, leave Hypothesized Mean Difference at 0 and Alpha at 0.05.
4. Choose an output cell and click OK.

The key rows of the output for the quiz data:

The t Stat is negative only because Method A came first. What matters is its size: 3.56 is bigger than the critical value 2.18, which is the same conclusion as p below 0.05. Unlike T.TEST, the ToolPak output is a fixed table of values, so run it again if the data changes.

Check your data before you trust the result#

A t-test is reliable when its basic conditions hold, and a few quick checks catch most problems:

• Independent values. Each score should come from a different person or item, unless you use the paired type.
• No extreme outliers. Compare the average and the median, and look at MIN and MAX. One wild value can swing a small sample. The median and mode lesson practises the median check.
• Roughly symmetric data, especially with small groups. Very skewed data, such as incomes, may need a different test.
• Spread. The guide to standard deviation in Excel shows how to measure it; similar spreads support type 2, very different spreads call for type 3. See STDEV.S vs STDEV.P for which formula to use on a sample.

A t-test compares exactly two groups. For three or more, such as Methods A, B and C, use analysis of variance (the ToolPak's Anova: Single Factor) rather than several t-tests.

Practise it#

A t-test is only as good as the averages behind it. Try the exercise on this page, which measures the gap before any test, then practise AVERAGE, MIN and MAX in a live spreadsheet and practise MEDIAN and MODE. Each lesson checks your formula the moment you press Enter.