T Test Excel: Make your Analysis as Simple as It Can Be

Microsoft Excel offers a variety of great tools for performing statistical analyses that range from basic to complex. While many of the advanced tools a statistician will employ are enabled through add-ins, and particularly Microsoft’s Analysis Toolpak, you can perform some basic statistical calculations using functions that come bundled with the basic package.

T Test is one of the standard functions that will get you a lot of mileage in working with the stats, and this guide will help you understand the function and how to use it.  If you would like to explore the many other features, both as part of the standard program and available through add-ins, you might consider a course in using Excel’s statistical functions.

Understanding T Test

The T Test function in Excel is designed to work on two independent sample sets. It allows you to compare the two sample sets, determining the two means’ difference in relation to the data variation. In simple terms, the result of the test indicates whether the difference is significant or due to random chance.

This supports hypothesis testing that you likely have become familiar with in basic statistics courses or in your job responsibilities, if you work in a field that deals with statistical analysis.  If, however, you are not familiar with the processes involved in hypothesis testing, you can enhance your understanding in a basic statistics course online.

Setting up the T Test

A great thing about using Excel to run a T Test is that it simplifies the routine by bundling the calculations in to one function. Of course, instead of running the test in this simplified fashion, you can use a step-by-step approach if you want to see the results of the independent calculations involved in a T Test.

This could be an approach you want to take if you are using the test to lear about statistics, and you can use a number of basic Excel functions to support you in doing this. You can get familiar with the types of functions available in an online course covering Excel essentials.

However, all you really need to perform the T Test in Excel is the sample data you want to perform the test on.  For the purpose of this tutorial, we can use these non-specific sample sets

	Sample One	Sample Two
Replicate 1	500	280
Replicate 2	480	230
Replicate 3	520	290
Replicate 4	470	250
Replicate 5	510	240

You can copy and paste this table in to an Excel spreadsheet if you would like to run a sample test for yourself.

Running the T.Test function

With your data prepared in your Excel spreadsheet, you are ready to set up and run the function.  You can do this using the pop-up interface for the function, or you can simply write the function out in the formula bar. Either way, you can generally begin by calling up the function in the formula bar.  Select the cell where you want your result to go.  Then enter this text:

=T.Test(

If you would like to use the interface for completing this function you can access it by clicking the button to the left of the formula bar, labeled with the fx symbol. Here, we will first walk through the equation as you would type it in. This will help you understand it and easily modify it as needed.

Your first step is to define the ranges containing your sample data, separated by a comma. These will be in the format [Beginning cell in range]:[Ending cell in range]. So, if the sample referenced above begins in cell A1, the first range you will define is B2:B6. Your second range is C2:C6. Make sure you are only using the range for one of the samples, and don’t include any labels.  So far, your function should look like this:

=T.Test(B2:B6, C2:C6

The next step, then, is to define the number of distribution tails.  For a one-tailed distribution, you will enter 1 and for a two tailed distribution.  As you will know from studying statistics, this indicates whether we will test the possibility of a relationship in one direction or two. Here we will indicate a two-tailed test by entering 2. So our function looks like this:

=T.Test(B2:B6, C2:C6, 2

As the final step in building your function is to tell Excel which type of T Test to perform.  There are three options indicated by integers 1-3.  Here are the types corresponding to each option

1 performs a paired test. If your samples are dependent, you will want to use this option.  For example, the paired test is appropriate if your samples were taken from the same group. If your samples are independent, you will choose one of the other two options, based on variance.

2 Performs a two-sample test with equal variance.

3 performs a two-sample test with unequal variance

Let’s assume in this test that our samples are taken from the same group and enter option one.  This complete the T.Test formula, and it should look like this.

=T.Test(B2:B6, C2:C6, 2, 1)

To run the test, you simply press the Enter or Return key, and in our example, you will get the result 1.63422E-05.  Using a p-value of .05, you will see that this would be low enough to reject the null hypothesis for your study.

In Summary

Hopefully, this walkthrough of the T.Test function in Excel helped you understand this useful statistical tool well enough to use it in your own work.  This is just one of many functions that you can use to make your life easier when undertaking statistical analysis, and if you would like to get a handle on others, you might try a course that surveys the Excel Functions.

Add T.Test to your bag of statistics tricks and enjoy crunching the numbers!