I often use two-sample t-tests as an introduction to Excel in my undergraduate statistics courses – and sometimes my graduate courses, too. Because the students are still getting used to functions in Excel, they tend to have many difficulties with this lesson. For this reason, I created the page below to provide an easy-to-read guide on performing two-sample t-tests in Excel. As always, if you have any questions, please email me a MHoward@SouthAlabama.edu!
Before learning about two-sample t-tests in Excel, we must first know what a two-sample t-test is used for. The textbook definition says that a two-sample t-test is used to “determine whether two sets of data are significantly different from each other”; however, I am not a fan of this definition. Instead, I prefer to say that a two-sample t-test is used to “test whether the means of a measured variable in two groups is significantly different.” So, a two-sample t-test is used to answer questions that are similar to the following:
- In our sample, do women have better test grades than men?
- Are men taller than women?
- Do people in a class taught by Dr. Howard perform better on a test than those in Dr. Smith’s class?
- Do employees in Training Group A have better performance than Training Group B?
Now that we know what a two-sample t-test is used for, we can now calculate a two-sample t-test in Excel! To begin, open your data in Excel. If you don’t have a dataset, download the example dataset here. In the example dataset, we are comparing the test grades of two classes (Dr. Howard and Dr. Smith) to determine which class has higher grades on an exam.
The data should look something like this:
If it doesn’t, that is okay. You can calculate a t-test in Excel fairly easily using many different data formats, but we’ll stick with the format in the picture for
Once you have the data open, click on the Data tab at the top. Then click on Data Analysis, as seen below:
Anyways, if it worked, the following window should have appeared. You’ll want to click on t-test: Two-Sample Assuming Unequal Variances, and then press OK – as seen below:
Then, the following window should pop up. On this window, you need to first click on the icon to identify your Variable 1 Range. Then, you need to highlight (click and drag) your data and press the icon again (seen below). This will identify the data representing Group 1 for your t-test, which is Dr. Howard’s exam scores in the current example.
Do the exact same thing but identify the Group 2 data instead, which is Dr. Smith’s exam scores in the current example.
Does your window now look like this?
If so, good! Click “OK,” and let’s see what we get.
Uhh, what does this mean? Well, let’s walk through it.
This is your t-statistic, which is the effect size. It is a standardized estimate of the difference between the two groups. Unless you know a decent amount about statistics, however, it probably doesn’t mean much to you. So, we should look at the p-values instead…but which one?
Excel provides both one-tailed and two-tailed p-values. Two-tailed p-values are more conservative estimates, and I usually use these to determine whether my results are significant. So, let’s see what the two-tailed p-value is:
There it is! From our results, we can identify that…
- The test statistic is: 2.783
- The p value is .015
Because our p-value is less than .05, we can reject the null and assume that a significant difference exists between our groups! Yay!
But how do we know which group performed significantly better? To do this, we have to calculate the means between the two groups. In this example, they were:
- Mean Dr. Howard’s class – 88.5
- Mean Dr. Smith’s class – 75.3
So, because there was a significant difference and Dr. Howard’s class had the higher mean, we know that Dr. Howard’s class performed significantly better on the exam than Dr. Smith’s class.
We did it! We calculated everything that we needed to know about the t-test! Good work!
Do you still have any questions? Or comments about this guide? Feel free to email me at MHoward@SouthAlabama.edu. I am always happy to chat!