T Test in Excel: Easy Steps with Video

Microsoft Excel for Statistics > T Test in Excel

How to do a T Test in Excel: Overview

t test in excelThe t test is a way to tell if the difference between before and after results is significant or if those results could have happened by chance. For example, a drug manufacturer might test a new drug and compare the before and after results to see if the drug was effective. It’s mostly used to test if means are different. The larger the t-value, the larger the difference in the two samples.

You have three options in the Data Analysis Toolpak (How to load the Data Analysis Toolpak) for a t test in Excel. Read below if you aren’t sure which test to choose or skip to the section you need:

  1. Paired Two Sample for means in Excel.
  2. Two Sample T test in Excel assuming Equal Variances.
  3. Two-sample T test in Excel assuming Unequal variances.


T-testing is used in hypothesis testing, when you are deciding if you should support or reject a null hypothesis. Which t test in Excel you use depends mostly on what type of data you have. If your data has two sets of observations from the same group (for example, medical testing before and after a drug is administered to the same group of people), you would use the paired two sample for means. Otherwise, use a two sample test for variances.

Paired Two Sample For Means is used when your sample observations are naturally paired. The usual reason for performing this test is when you are testing the same group twice. For example, if you are testing a new drug, you’ll want to compare the sample before and after they take the drug to see if the results are different. This particular t test in Excel used a paired two-sample test to determine if the before and after observations are likely to have been derived from distributions with equal population means.

The other two tests are used when you have different groups (i.e. you aren’t testing one group twice over time). The Two-Sample assuming Equal Variances test is used when you know (either through the question or you have analyzed the variance in the data) that the variances are the same. The Two-Sample assuming UNequal Variances test is used when either:

  1. You know the variances are not the same.
  2. You do not know if the variances are the same or not.

In most cases, you don’t know if the variances are equal or not, so you would use the Two-Sample assuming UNequal Variances test.

Not sure which version of the test to run? Check out our tutoring page!

Back To Top

Paired Two Sample for means in Excel 2013.

Two sample t test for means in Excel 2013
Watch the video for the steps:

Two sample t test in Excel for means: Overview

A two sample t test for means is normally used when you are testing twice on the same subject. For example, in a medical trial you might want to know if a particular medicine is effective so you test patients before the medication is administered and after. The t-test can tell you if the results from the trial have statistical significance (i.e. it worked) or if the results probably occurred by chance.

Two sample t test for means in Excel: Steps

Step 1: Type your data into Excel. As the two sample t test paired two sample for means is usually used for “before” and “after” data, you’ll probably have three columns: the first column for the subject identifier (i.e. a name or a number), the second column for the Before results and the third column for the After Results.

Step 2: State your null hypothesis (How to state the null hypothesis). For example, your null hypothesis might be that the means are the same.

Step 3: Click the “Data” tab and then click “Data analysis.” If you don’t see the Data Analysis option, load the Data Analysis Toolpak.

Step 4: Click “t test paired two sample for means” from the options window then click “OK.”

Step 5: Click the “Variable 1 Range” box and then select your first variable list (usually the Before list).

Step 6: Click the “Variable 2 Range” box and then select your second variable list (usually the After list).

Step 7: Type a number into the Hypothesized Mean Difference box. For example, if your null hypothesis stated that there was no difference between the means, enter “0.” Otherwise, if you are hypothesizing there is a difference, type that difference into the box.

Step 8: Check the “Labels” box if you have included labels.

Step 9: Type an alpha level into the alpha level box. An alpha level of 0.05, or 5%, is standard in hypothesis testing so if you aren’t sure what alpha level you need, leave this at 0.05.

Step 10: Click the Output Range box and select an area to the right of your data.

Step 11: Click “OK.”

Reading The Results from the two sample t Test for means in Excel 2013

Your results will include a lot of data, some that’s obvious (like the number of data items). But when you run a t-test you’re really only looking for two things: t-scores and alpha levels.

Step 1: Compare the alpha level you chose (i.e. 0.05) to the p-value in the output. If the p-value in the output is smaller than the alpha level you chose, reject the null hypothesis.

Step 2: Compare the t-critical value in the output with the t-value. If the t-value is larger than the t-critical value, reject the null hypothesis. There are two t-critical values, one-tail and two-tail. If you aren’t sure if you have a one-tailed test or a two-tailed test, always compare the t-value to the two-tail t critical value.

In order to fully reject the null hypothesis, use both values (p and t) in combination. In other words, if you think you might reject the null based on the t-value, but your p-value is large, then don’t reject the null.

Check out our YouTube channel for more Excel stats help and tips!
Back To Top

Two Sample t test assuming Equal Variances.

Watch the video or read the steps below:

Two sample t test Assuming Equal variances in Excel 2013: Overview

Two sample t test Assuming Equal variances in Excel 2013A two sample t test assuming equal variances is used to test data to see if there is statistical significance or if the results may have occurred randomly. This is one of three t tests available in Excel and of the three, it’s the one least likely to be used. Why? In the vast majority of cases in hypothesis testing, you don’t know the population variances. This test should only be used if you have been explicitly informed that the population variances are equal. If you don’t have this information, you should be running the other t test (Two sample t test Assuming Unequal variances).

How to do a Two sample t test in Excel Assuming Equal variances: Steps

Step 1: Type your data into a worksheet. Generally, you’ll have a list in one column and another list in a second column. The t-test will allow you to compare the means from these two columns.

Step 2: Write the null hypothesis (How to state the null hypothesis). For example, your null hypothesis might be that the means are different by a certain amount.

Step 3: Click the “Data” tab and then click “Data analysis.” If you don’t see the Data Analysis button on the toolbar (to the far right of the Data tab), load the Data Analysis Toolpak.

Step 4: Click “t test two sample Assuming Equal variances ” from the options window then click “OK.”

Step 5: Click the “Variable 1 Range” box and then select your first data list.

Step 6: Click the “Variable 2 Range” box and then select your second data list.

Step 7: Type a number into the Hypothesized Mean Difference box. For example, if your null hypothesis stated that there was no difference between the means, type “0.”

Step 8: Check the “Labels” box (you’ll usually want to include labels so you can easily compare the two sets of data).

Step 9: Type an alpha level into the alpha level box. If you don’t know what alpha level you should be using, leave it at 0.05.

Step 10: Click the Output Range box and select an area for your output.

Step 11: Click the “OK” button.

Reading The Results from two sample t test Assuming Equal variances in Excel 2013

Step 1: Compare the alpha level you types into the two sample t test Assuming Equal variances window (i.e. 0.05) to the alpha level listed in the output on the worksheet. If the alpha level in the output is larger than the alpha level you chose, you will be unable to reject the null hypothesis.

Step 2: Compare the t-critical value in the output on the worksheet with the t-value listed. If the t-value is larger than the t-critical value, you can reject the null hypothesis. There are two t-critical values, one-tail and two-tail. If you aren’t sure if you have a one-tailed test or a two-tailed test, always compare the t-value to the two-tail t critical value.

Check out our YouTube channel for more stats help and tips!

Two-sample t test assuming Unequal variances.

Two Sample T Test Unequal Variance Excel 2013
Watch the video or read the steps below:

Two Sample T Test in Excel Unequal Variances: Overview

A two sample t test assuming unequal variances is the most common type of t test in Excel 2013. You have three options in Excel for t tests: assuming equal variances, assuming unequal variances and a paired two sample. The paired two sample for means in Excel is generally used if you have a sample you’re testing twice (i.e. a “Before” and an “After”) while the two sample test assuming equal variances is only used on the very rare occasion you know the population variance.

Two sample t test in Excel 2013 for unequal variance: Steps

Step 1: Type your data into a worksheet in two columns.

Step 2: State your null hypothesis (How to state the null hypothesis) (i.e. the means for both sets of data are the same).

Step 3: Click “Data” and then click “Data analysis.” If you don’t see Data Analysis, load the Data Analysis Toolpak.

Step 4: Click “t test two sample Assuming unequal variances ” and then click the “OK” button. This will open the t test two sample Assuming unequal variances dialog box.

Step 5: Type the location for your first set of data into the “Variable 1” box.

Step 6: Type the location for your first set of data into the “Variable 2” box.

Step 7: Type a number into the Hypothesized Mean Difference box. The hypothesized mean difference should have been stated when you wrote your null hypothesis. For example, if you think the means are the same then the hypothesized mean difference is 0.

Step 8: Check the “Labels” box (assuming you included labels for your data, which is always a good idea).

Step 9: Click the “Alpha level” box and then type an alpha level. the default is 0.05, which is a standard alpha level for these tests.

Step 10: Click the “Output Range” box and select an area for your output.

Step 11: Click the “OK” button.

Reading The Results from two sample t test unequal variance Excel 2013

  1. Reject the null hypothesis if the alpha level in the output is smaller than your stated alpha level. For example, if the alpha level in the output is 0.03 and your alpha level from Step 9 was 0.05, you can reject the null hypothesis.
  2. Compare the t-value with the t-critical value. If the t-value is larger than the t-critical value, reject the null hypothesis. There are two t-critical values—one for a one-tailed test and one for a two-tailed test. If you don’t know if you have a one or two tailed test, use the two tailed test figure ( How to tell if you have a one-tailed test or a two-tailed test).

Back To Top

Check out our YouTube channel for more Excel for statistics help and tips!

References

Salkind, N. (2016). Statistics for People Who (Think They) Hate Statistics: Using Microsoft Excel 4th Edition.


Comments? Need to post a correction? Please Contact Us.

Join us on our YouTube Channel