How to Perform a T-Test in Excel – 2 Easy Methods

A T-Test is used to calculate whether there is a significant difference in the mean between two data sets. This can be either done by comparing two separate groups, or done in a way where we look at the same group but with different values at different points – for example before and after results.

Generally, our aim is to determine if there is no significant difference between the means, in which case the p value will be > 0.05. When we see the value sit lower than 0.05 then the difference between our two means is significant.

In this simple guide we will run through how to perform a T-Test in Excel in a couple of different ways. The first will use a built in function, while the second uses a nice and easy formula.

T-Test Using the Data Analysis ToolPak

The first approach we are going to take here will be using the built in functions in Excel itself which in most cases is the way to go as it is easy and provides a nice clear output.

The data set we are going to work with here is pretty simple – we have 12 values for Group 1 and 12 values for Group 2. This could be anything that has a quantifiable value – test scores, weight, sales profit etc

To access the T-Test functions we will need to head over to the Data Analysis options under the Data tab of the ribbon.

If you don’t have the Data Analysis option, head over to this guide to get it set up. Should only take a minute or two.

Once we click on Data Analysis we will see three separate options for T-Test:

The choice here really depends on the dataset we are working with, however a brief summary of each of the options is as follows:

• Paired Two Sample for Means: Comparing means from the same group in a different environment
• Two-Sample Assuming Equal Variances: Comparing means from two independant groups where variances are equal
• Two-Sample Assuming Unequal Variances: Comparing means from two independant groups where variances are assumed to be unequal

To sum it up simply – if we are comparing the same group of people a year apart use paired, if we are comparing two independant groups of people at the same time use two-sample. The equal and unequal part gets a bit more complicated but if you are measuring two groups of people with similar methodologies then chances are it will be equal. To read up a bit more on this a handy guide can be found here.

Let’s start by using the Two-Sample Assuming Equal Variances T-Test. When we select this and press OK we are presented with another pop up which has the following options:

Let’s quickly run through each of the important values.

• Variable 1 Range: The range of our first dataset. In the example it is Column B
• Variable 2 Range: The range of our second dataset. In the example it will be Column C
• Hypothesized Mean Difference: Enter zero here as the hypothesis generally looks for this
• Alpha: The significant value. This is almost always going to be 0.05 so don’t change it.

Leave output range as a new worksheet and press OK. We will be presented with the below summary:

This is one of the nicest benefits of using the built in function – we have a clean and easy to read summary generated for us!

As we can see here the means of each group are incredibly close at 80.25 and 81.83. Therefore as expected the P value (in this case P(T<=t) two tail) is far higher than 0.05 all the way up at 0.76.

That sums up the entire process to perform the T-Test. The other two options for unequal variances, and paired samples both have the same input and outputs so there is no need to run through them separately.

The T.TEST Formula

Our second approach is a bit more manual and simply involves using a formula. This is also pretty quick and easy but being a formula only outputs one number at a time in each cell.

The syntax is as follows:

=T.TEST(array1, array2, tails, type)

Let’s quickly break this down:

• Array1: The range of our first dataset
• Array2: The range of our second dataset
• Tail: One tail or two tail distribution (entered as 1 or 2)
• Type: Paired, Two Sample Equal and Two Sample Unequal (entered as 1, 2, or 3)

To repeat the same analysis performed in the first method, our formula would then look like this:

=T.TEST(B3:B14,C3:C14,2,2)

The output appears as follows in our cell:

As expected, we see the exact same P value of 0.76.

If all we really care about is the P value and not the means themselves or any other outputs from the data analysis function then this more than does the job and can be slightly faster.

This sums up how to perform a T-Test using Excel. For more handy guides on working with Excel, be sure to check out the rest of our tutorials here.