Excel can perform various statistical analyses, including t-tests. It is an excellent option because nearly everyone can access Excel. This post is a great introduction to performing and interpreting t-tests even if Excel isn’t your primary statistical software package.
In this post, I provide step-by-step instructions for using Excel to perform t-tests. Importantly, I also show you how to select the correct form of t-test, choose the right options, and interpret the results. I also include links to additional resources I’ve written, which present clear explanations of relevant t-test concepts that you won’t find in Excel’s documentation. And, I use an example dataset for us to work through and interpret together!
- Two group means are different.
- Paired means are different.
- One mean is different from a target value.
For more information about the types of t-tests you can use, read my post about 1-sample, 2-sample, and Paired t-Tests. Learn how it compares to the Z Test.
Install the Data Analysis ToolPak in Excel
The Data Analysis ToolPak must be installed on your copy of Excel to perform t-tests. To determine whether you have this ToolPak installed, click Data in Excel’s menu across the top and look for Data Analysis in the Analyze section. If you don’t see Data Analysis, you need to install it. Don’t worry. It’s free!
To install Excel’s Analysis Tookpak, click the File tab on the top-left and then click Options on the bottom-left. Then, click Add-Ins. On the Manage drop-down list, choose Excel Add-ins, and click Go. On the popup that appears, check Analysis ToolPak and click OK.
After you enable it, click Data Analysis in the Data menu to display the analyses you can perform. Among other options, the popup presents three types of t-test, which we’ll cover next.
Two-Sample t-Tests in Excel
Two-sample t-tests compare the means of precisely two groups—no more and no less! Typically, you perform this test to determine whether two population means are different. For example, do students who learn using Method A have a different mean score than those who learn using Method B? This form of the test uses independent samples. In other words, each group contains a unique set of people or items.
Statisticians consider differences between group means to be an unstandardized effect size because these values indicate the strength of the relationship using values that retain the natural units of the dependent variable. Cohen’s d is the corresponding standardized effect size and it’s appropriate to report in some cases. Effect sizes help you understand how important the findings are in a practical sense. To learn more about unstandardized and standardized effect sizes, read my post about Effect Sizes in Statistics.
The standard form tests the following hypotheses:
- Null: The two population means are equal.
- Alternative: The two population means are not equal.
If the p-value is less than your significance level [e.g., 0.05], you can reject the null hypothesis. The difference between the two means is statistically significant. Your sample provides strong enough evidence to conclude that the two population means are different.
For more information about the null and alternative hypotheses and other hypothesis testing terms, see my Hypothesis Testing Overview.
Also, learn about the difference between descriptive statistics and inferential statistics.
t-Tests for Equal and Unequal Variances
You’ll notice that Excel has two forms of the two-sample t-test. One that assumes equal variances and the other that assumes unequal variances. Variances and the closely related standard deviation are measures of variability. All t-tests assume you obtained data from normally distributed populations. However, the conventional t-test also assumes the standard deviations/variances for both groups are equal. Another form of the test, known as Welch’s t-test, does not assume equal variances.
As an aside, thanks to the central limit theorem, you can safely use t-tests to analyze nonnormal data when have ~20 or more observations per group.
Which One to Use?
Advice for using either the equal or unequal variances form of the 2-sample t-test varies because this issue is more complicated than it first appears. Some analysts advise using an F-test to determine whether the variances are unequal. And, Excel does offer the F-test Two-Sample for Variances. However, using additional tests always increases the probability of both false positives and false negatives [a.k.a, Type I and Type II errors].
Additionally, if you have a large sample size, the f-test has more statistical power. This condition can cause the test to identify an inconsequential difference as being statistically significant. That’s the difference between practical significance and statistical significance. Conversely, small sample sizes can fail to detect a substantial difference between variances.
When you have an equal, or nearly equal, number of observations in both groups and a moderate sample size, t-tests are robust to differences between variances. If you find one group has twice the variance of another group, it might be time to worry! However, you don’t need to worry about smaller differences.
Other analysts suggest always using the form of the t-test that assumes unequal variances. If you use this approach when the variances are equal, you lose a trivial amount of statistical power, but you’ll be better off when the variances are not equal.
If you have unequal variances and unequal samples sizes, it’s vital to use the unequal variances version of the 2-sample t-test!
Step-by-Step Instructions for Running the Two-Sample t-Test in Excel
Let’s conduct a two-sample t-test! This test is also known as the independent samples t-test. Click the link to learn more about its hypotheses, assumptions, and interpretation.
Our hypothetical scenario is that we are comparing scores from two teaching methods. We drew two random samples of students. One sample comprises students who learned using Method A while the other sample learned using Method B. These samples contain entirely different students. Now, we want to determine whether the two means are different. Download the CSV file that contains all data for both t-test examples in this post: t-TestExamples.
To perform a 2-sample t-test in Excel, arrange your data in two columns, as shown below.
Let’s assume that the variances are equal and use the Assuming Equal Variances version. If we had chosen the unequal variances form of the test, the steps and interpretation are the same—only the calculations change.
- In Excel, click Data Analysis on the Data tab.
- From the Data Analysis popup, choose t-Test: Two-Sample Assuming Equal Variances.
- Under Input, select the ranges for both Variable 1 and Variable 2.
- In Hypothesized Mean Difference, you’ll typically enter zero. This value is the null hypothesis value, which represents no effect. In this case, a mean difference of zero represents no difference between the two methods, which is no effect.
- Check the Labels checkbox if you have meaningful variable names in row 1. This option makes the output easier to interpret. Ensure that you include the label row in step #3.
- Excel uses a default Alpha value of 0.05, which is usually a good value. Alpha is the significance level. Change this value only when you have a specific reason for doing so.
- Click OK.
For the example data, your popup should look like the image below:
After Excel creates the output, I autofit the width of column A to display all text in it.
Interpreting the Two-Sample t-Test Results
The output indicates that mean for Method A is 71.50362 and for Method B it is 84.74241. Looking in the Variances row, we can see that they are not exactly equal, but they are close enough to assume equal variances. The p-value is the most important statistic. If you want to learn about the other statistics, you can read my posts about the t Stat [i.e., the t-value], df [degrees of freedom], and the t Critical values.
If the p-value is less than your significance level, the difference between means is statistically significant. Excel provides p-values for both one-tailed and two-tailed t-tests.
One-tailed t-tests can detect differences between means in only one direction. For example, a one-tailed test might determine only whether Method B is greater than Method A. Two-tailed tests can detect differences in either direction—greater than or less than. There are additional drawbacks for using one-tailed tests—so I’ll stick with the standard two-tailed results. To learn more, read my post about one-tailed and two-tailed tests.
For our results, we’ll use P[T