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 [email protected]! 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:
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 simplicity. Once you have the data open, click on the Data tab at the top. Then click on Data Analysis, as seen below: Don’t see that tab? If not, go to my page on Activating the Data Analysis Tab. It should appear once you activate it. 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…
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:
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 [email protected]. I am always happy to chat! |