In this post, I provide bit-by-bit instructions for using Excel to perform t-tests. importantly, I besides show you how to select the right shape of t-test, choose the right options, and interpret the results. I besides include links to extra resources I ’ ve written, which present clear explanations of relevant t-test concepts that you won ’ thyroxine discover in Excel ’ mho software documentation. And, I use an case dataset for us to work through and interpret together !
Reading: How to do t-Tests in Excel
T-tests are
- Two group means are different.
- Paired means are different.
- One mean is different from a target value.
T-tests are hypothesis tests that assess the means of one or two groups. guess tests use sample data to infer properties of entire populations. To be able to use a t-test, you need to obtain a random sample distribution from your target populations. Depending on the t-test and how you configure it, the test can determine whether : For more information about the types of t-tests you can use, read my mail about 1-sample, 2-sample, and Paired t-Tests .
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 ’ metric ton see Data Analysis, you need to install it. Don ’ t worry. It ’ second spare !
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.
To install Excel ’ s Analysis Tookpak, click the File yellow journalism 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 suction stop 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 shroud adjacent.
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 autonomous 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 subject variable. Effect sizes avail you understand how important the findings are in a practical smell. To learn more about unstandardized and standardize effect sizes, read my post about Effect Sizes in Statistics .
The standard form tests the comply hypotheses :
- Null: The two population means are equal.
- Alternative: The two population means are not equal.
If the p-value is less than your meaning level ( for example, 0.05 ), you can reject the nothing hypothesis. The deviation between the two means is statistically significant. Your sample provides hard enough evidence to conclude that the two population means are different .
For more data about the nothing and alternative hypotheses and other hypothesis testing terms, see my Hypothesis Testing Overview .
besides, learn about the remainder between descriptive statistics and illative 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 relate standard deviation are measures of variability. All t-tests assume you obtained data from normally distributed populations. however, the conventional t-test besides assumes the standard deviations/variances for both groups are equal. Another imprint of the examination, known as Welch ’ s t-test, does not assume equal variances .
As an apart, thanks to the cardinal 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 inadequate variances form of the 2-sample t-test varies because this issue is more complicate than it first appears. Some analysts advise using an F-test to determine whether the variances are inadequate. And, Excel does offer the F-test Two-Sample for Variances. however, using extra 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 exponent. This condition can cause the trial to identify an inconsequent deviation as being statistically significant. That ’ s the deviation between hardheaded significance and statistical meaning. conversely, small sample sizes can fail to detect a substantial remainder between variances .
When you have an equal, or closely equal, number of observations in both groups and a moderate sample distribution size, t-tests are robust to differences between variances. If you find one group has doubly the discrepancy 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 inadequate variances. If you use this approach when the variances are equal, you lose a superficial sum of statistical power, but you ’ ll be better off when the variances are not adequate .
If you have inadequate variances and inadequate samples sizes, it ’ randomness critical to use the inadequate variances version of the 2-sample t-test !
Step-by-Step Instructions for Running the Two-Sample t-Test in Excel
Let ’ s behave a two-sample t-test ! This test is besides 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. immediately, 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.
Let ’ s assume that the variances are equal and use the Assuming Equal Variances interpretation. If we had chosen the unequal variances shape of the test, the steps and interpretation are the same—only the calculations change. For the case data, your popup should look like the prototype 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
After Excel creates the output signal, I autofit the width of column A to display all text in it. 
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 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 precisely equal, but they are close adequate 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 triiodothyronine 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 entirely one commission. For exemplar, a one-tailed test might determine alone whether Method B is greater than Method A. Two-tailed tests can detect differences in either direction—greater than or less than. There are extra 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 < =t ) two-tail, which is the p-value for the two-tailed shape of the t-test. Because our p-value ( 0.000336 ) is less than the standard significance horizontal surface of 0.05, we can reject the null guess. Our sample distribution data support the guess that the population means are different. specifically, Method B ’ south mean is greater than Method A ’ sulfur bastardly .
Paired t-Tests in Excel
Paired t-tests tax paired observations, which are frequently two measurements on the same person or detail. Statisticians call these dependant samples. Suppose you gather a random sample of people. You give them all a pretest, administer a discussion, and then perform a posttest. Each capable has a pretest and posttest sexual conquest. Or, possibly you have a sample of wood boards, and you paint half of each board with one paint and the other half with different rouge. then, you measure the paint lastingness for both types of rouge on all the boards. Each board has two paint lastingness scores .
In both cases, you can use a pair t-test to determine whether the remainder between the means of the two sets of scores is statistically meaning .
Unlike independent t-tests, paired t-tests use the same people or items in both groups. One way to determine whether a copulate t-test is allow for your data is if each row in the dataset corresponds to one person or item. For our pretest/posttest example, we measure each subjugate before and after the experiment and placed the measurements for an individual on one row .
Related post : freelancer and Dependent Samples and Paired T Test
Step-by-Step Instructions for Running the Paired t-Test in Excel
For this exemplar, imagine that we have a train program, and we need to determine whether the difference between the hateful pretest score and the mean posttest score is significantly different .
To perform a pair t-test in Excel, arrange your data into two columns then that each row represents one person or item, as shown below. note that the analysis does not use the discipline ’ mho ID number .
- In Excel, click Data Analysis on the Data tab.
- From the Data Analysis popup, choose t-Test: Paired Two Sample for Means.
- 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 variables labels in row 1. This option helps make 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 case data, your popup should look like the prototype below :
Interpreting Excel’s Paired t-Test Results
The output indicates that mean for the Pretest is 97.06223 and for the Posttest it is 107.8346.
The output indicates that mean for the Pretest is 97.06223 and for the Posttest it is 107.8346. If the p-value is less than your meaning level, the dispute between means is statistically meaning. Again, Excel provides p-values for both one-tailed and two-tailed t-tests—and we ’ ll stick with the two-tailed result. For information about the other statistics, click the links in the 2-sample t-test section .
For our results, we ’ ll use P ( T < =t ) two-tail, which is the p-value for the two-tailed mannequin of the t-test. Because our p-value ( 0.002221 ) is less than the standard significance degree of 0.05, we can reject the null guess. Our sample data support the guess that the population means are different. specifically, the Posttest beggarly is greater than the Pretest beggarly .
What Excel’s t-Tests Do Not Include
As decent as it is to be able to perform t-tests in Excel, it leaves out some all-important features. notably, Excel can not create confidence intervals. The means in these analyses are the point estimates for the population means. however, thanks to random error, the sample distribution means never precisely equal the population base. There is a margin of error around the estimates. confidence intervals use a margin of erroneousness to calculate a range of values that is likely to contain the actual population mean for each group. Learn more about confidence intervals .
Excel besides doesn ’ thymine calculate the estimated difference between the means. The difference between the means is the impression size for the analysis—an important prize to know. By using a convention in Excel, you can easily calculate the difference between means. however, it would be dainty to have a confidence time interval for this deviation excessively. For more information, read my post about using confidence intervals to assess differences between means .
last, Excel, queerly, does not provide a one-sample t-test ! In some cases, you might have a individual sample of data and want to determine whether it is different from a target value. For example, you might measure the potency of a intersection and use a one-sample t-test to determine whether it is importantly different from an authoritative strength value .
t-Tests can compare up to two groups. If you have three or more groups, you ’ ll indigence to use ANOVA. For more information, see my posts about how to do one-way ANOVA in Excel and how to do two-way ANOVA in Excel !
If you ’ re learning about hypothesis test and like the approach I use in my blog, check out my eBook !
