T-Test In Excel

What Is T-Test In Excel?

The T-Test Excel function compares the mean values of samples between two population sets or groups and determines if the two groups are considerably different or not.

The T-Test in Excel helps users perform hypothesis testing. It statistically shows whether we need to go with our null or alternate hypothesis for the population in question.

For example, consider the table with electricity rates in the US (column C) and Australia (column D). We can determine if the mean values of the two data sets have a considerable difference between them using the two sample T-Test in Excel in cell D13.

=T.TEST(C2:C11,D2:D11,1,1)

The T-Test returns the result as 0.017, which is the p-value. The value is below the threshold of 0.05, the default cut-off. It implies that the probability of the two samples’ mean values is the same and less than 5%. It also interprets that the difference in the mean values of the two samples in columns C and D is significant. So, our null hypothesis is false, and we need to reject it.

What Is T-Test In Excel?

The T-test in Excel compares the mean values of the two population samples to determine the probability of the two data sets being notably different.
One can decide on the T-test type based on the population samples of interest. The syntax of the T.Test() Excel function is =T.TEST(array1,array2,tails,type)
We can perform one-tailed or two-tailed T-Tests.
We can enter the specific T-test formula with the required arguments, or we can install the Analysis ToolPak to get a complete analysis for the required type of T-test.

TTEST() Excel Formula

Unlike other functions, all the T-Test arguments in Excel are mandatory.

array1: The first data set.
array2: The second data set.
tails: This denotes the distribution tail used in a T-Test in Excel. If the value is 1, it indicates the one-tailed distribution; if the value is 2, it means two-tailed distribution.
type: It refers to the T-Test type. We can enter the value 1, 2, or 3. While 1 is for Paired T-Test, 2 and 3 denote Two sample equal and unequal variance T-Tests, respectively.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

How To Use TTEST() Excel Function?

Following are the steps for the T-test in excel.

First, ensure you have your data sets in a spreadsheet.
Then, choose the cell where you want to display the T-Test result, the p-value.
Enter the T-Test in Excel formula by selecting the Test() function, accessible using Formulas > More Functions > Statistical.
Enter the first two array arguments by selecting the first and second data sets. Then, provide the tails argument (one or two-tailed T-Test) and decide the type based on the T-Test type (paired T-Test Excel, Two sample equal or unequal variance T-Tests).
Press Enter to get the output.
Finally, from the Test() outcome, make the necessary inferences.

Consider the following table that shows the stress levels of a group before (in column A) and after (in column B) attending a therapy. It is a two-tailed T-Test, as we are not making a hypothesis whether the therapy reduced the stress levels. While the null hypothesis is that there is no difference in the mean values of the two samples, the alternate one implies a significant difference.

The second table is where we display the T-Test result.

Since the data set values are before-after measurements, we consider paired T-test.

Here are the steps to use the T-Test in Excel function in this scenario.

Step 1: Choose cell D3 and enter the two sample T-Test in Excel function as:

=T.TEST(A2:A16,B2:B16,2,1)

Please Note: The cell ranges A2:A16 and B2:B16 are the two arrays we need to compare, so they are the first and second arguments. The third argument, 2, denotes a two-tailed T-Test, and the fourth argument, 1, refers to a paired T-Test Excel.

Step 2: Press Enter to view the output.

The function returns the p-value as 0.008. It is less than the standard threshold of significance, 0.05. So, we reject our null hypothesis. It indicates that the mean values of the two samples are notably different. Thus, we have to go with our alternate hypothesis.

Examples

Let us look at the various scenarios where you can use the T-Test in Excel.

Example #1

Below is the table with a list indicating a farm’s height (in feet) of banana trees.

Using this example, we will explain how to calculate using one sample T-Test Excel. We will also determine if a sample mean value differs significantly from a hypothesized mean.

Hypothetically, let us assume that the average height of a banana tree is 11 feet.

We will check whether the heights in the sample differ significantly from the hypothesized average height.

The null and alternative hypotheses will be:

Null hypothesis: No significant difference between the sample banana tree heights and the average height of the banana tree.
Alternate hypothesis: Significant difference between the sample banana tree heights and the average height of the banana tree.

Step 1: Select cell D3 to enter the sample mean (x̄) using the AVERAGE excel formula.

=AVERAGE(A2:A13)

Step 2: Press Enter key to obtain the mean value.

Step 3: Choose cell D4 and enter the sample standard deviation (s) formula.

=STDEV.S(A2:A13)

Step 4: Press Enter key to view the value in cell D4.

Step 5: Count the total data points in the sample (n) using the COUNT() function.

=COUNT(A2:A13)

Step 6: Press Enter key to obtain the results. The COUNT function will return the value as 12.

Step 7: Next, determine the standard error in mean (SEM) using the mathematical formula:

SEM = S / √n

Where,

s: Standard Deviation
n: Total data points in the sample

Select cell D6 to enter the SEM formula

=D4/SQRT(D5)

Step 8: Press Enter to get the SEM value in cell D6.

Step 9: Now determine the degree of freedom (df). It is n-1, where n is the total data points in the sample.

Choose cell D7 and enter the df formula:

=D5-1

Step 10: Let us denote the hypothesized mean, 11 feet, as µ. So, enter the µ value in cell D8.

Step 11: Select cell D9 to enter the t-statistic (t) value. The mathematical formula is:

t = (x̄ – µ) / S_x̄

Where,

x̄: Sample mean
µ: Hypothesized mean (11 feet in this example)
S_x̄: SEM

The T-statistic formula for a one-sample T-Test in Excel will be:

=(D3-D8)/D6

Step 12: Press Enter to view the t-statistic value.

Step 13: Choose cell D10 to enter the p-value using the TDIST(). The arguments for the function are t, df, and tails. Next, we will perform a two-tailed T-Test as we have not hypothesized if the sample values are lower or higher than the average. So, the formula in cell D10 will be:

=TDIST(D9,D7,2)

Step 14: Press Enter key to display the p-value.

The p-value is 0.018. It is lower than the default threshold value of 0.05. It interprets that the banana tree heights in the sample differ significantly from the average height of the banana tree. Thus, we reject our null hypothesis and go with the alternate hypothesis.

Example #2

Let us look at the table where the two samples have the same population variance.

The following table shows the number of tasks completed by two teams, Team 1 and Team 2, in columns A and B.

The second table is where you will determine and enter the T-Test result.

The two independent data sets have the same population variation. So, let us calculate the T-Test in Excel function for a one-tailed distribution.

Step 1: Enter the T.Test function in the cell C11.

=T.TEST(A2:A9,B2:B9,1,2)

Please Note: As the population variance is the same for the two data sets, the fourth argument is 2 in the formula, denoting two sample equal variance (homoscedastic).

Step 2: Press Enter to view the T-Test result for the equal variance scenario.

The T.Test() returns the p-value as 0.5. It exceeds the default threshold value, 0.05. So, we need to accept the null hypothesis, implying the two sample data sets have the same mean. Thus, we do not consider the alternate hypothesis, as it may suggest a significant difference in the mean values of the two samples.

Example #3

This example will show how to execute the T-Test in Excel function using the Data Analysis (Analysis ToolPak) in the Data tab.

The below table shows the sample of headcounts in two departments of an IT company.

We can conduct a paired, two sample T-Test using the Analysis ToolPak. But first, we need to install it.

1. Check for the Data Analysis option in the Data. If the option is unavailable in the Data tab, we can install it from File > Options.

2. Choose Add-ins from the menu on the left.

3. Choose Analysis ToolPak from the Inactive Application Add-ins and click on Go.

4. Check the Analysis ToolPak box and click OK.

Using the steps mentioned above, we will see the Data Analysis option in the Data tab.

We can proceed with the T-Test using the Analysis ToolPak in the following steps.

Step 1: Go to Data > Data Analysis.

Step 2: The Data Analysis window pops up. Scroll down to select the t-Test options, choose the Paired Two Sample for Means and click OK.

Step 3: We will see a dialog box where we have to enter the T.Test() arguments.

Please Note: Ensure we select the Labels and Output Range options. Also, the Alpha is 0.05.

Step 4: With the cursor in the Variable 1 Range, select the cell range A2:A11. Then with the cursor in Variable 2 Range, choose the cell range B2:B11. These will be the absolute cell references for array1 and array2, the first two arguments of the T.Test().

Next, with the cursor in Output Range, select the cell where we want to display the T-Test result. For example, let us choose cell D1.

Step 5: Click OK to view the T-Test result in cell D1. After adjusting the column width, the output will appear as shown below.

The analysis provides the mean, variance, and count of data points (observations) used in the T.Test(). It also shows the correlation, degree of freedom (df), and t-statistic (t Stat) values. And based on all these parameters, we will finally obtain the p-value.

Check the p-value in cell E13. It is 0.000099, a value much lower than the p-value threshold of 0.05. It indicates that our sample data is highly significant, with a considerable difference between the mean values of the two samples.

T-Test Excel Errors

When performing the T-Test in Excel, the common possible errors you can get are:

#N/A: The two arrays you provide have different lengths.
#NUM!: The argument, tails, is no
The type argument is not 1, 2, or 3.
#VALUE!: The argument in the T-Test in Excel function, tails or type, is non-numeric.

Important Things To Note

The T-test in Excel and hypothesis testing are useful in the business world. You can use them during new product testing and for measuring employee performance.
You can perform the T-test for dependent and independent data sets.
The T-test requires the standard deviation of each data set, the total data points in each data set, and the mean difference between the data points.
The default threshold of significance is 0.05 or 5%. And a p-value below 0.05 makes your data significant.
The function returns the value that helps users analyze the probability of a significant difference between the mean values of two data sets or groups.
The Test() is useful for hypothesis testing. It helps determine if we need to accept our null or alternate hypothesis.

Frequently Asked Questions (FAQs)

How to run T-test in Excel?

1. Select the cell where you wish to see the T-test result (return value).
2. Depending on the t-test type you want to perform, enter the required formula and press Enter to get the function return value.

Alternatively, we can:

1. Go to Data > Data Analysis.
2. Select the specific T-test you wish to perform.
3. Enter the two data set ranges, the Alpha value and the cell where you want to display the result.
4. Click on OK to view the complete T-test analysis.

What does the T-test tell you in Excel?

It tells you the probability of two sample data sets being considerably different or belonging to the same population with zero difference in their mean values.

What is the p-value for the t-test in Excel?

Once you run the T-test in Excel, you get the complete analysis, with the most critical information being the p-value. It tells you whether you should accept your null or alternate hypothesis based on the significance threshold (0.05).

What is the difference between a paired (dependent) and unpaired (independent) T-test?

A paired T-test compares the mean value of the sample data sets provided for the same group in two different scenarios. The variance may or may not be equal.

An unpaired T-test compares the mean values of independent populations with the assumption that their variance is equal.

Download Template

This article must be helpful to understand the T-test in Excel, with its formula and examples. You can download the template here to use it instantly.

T-Test in Excel Download

T-Test In Excel