Instead of formulas, we can use Excel *Data Analysis Tools* to calculate t value in Excel. This functionality is under the tab* Data-Data Analysis*.

If there is no *Data Analysis* under the Data tab, we can add it in several steps. Go to *File-Options-Add-ins* and select *Analysis ToolPak*. Click button *Go, *check the Analysis ToolPak check box and click *OK.*

*Figure 1.** Run t Test in Excel *

Click on the Data Analysis button, and several t Test options will appear:

* Figure 2. How to get **tvalue in Excel*

**One-Sample t Test in Excel**

In the following example, we want to calculate the one sample t test in Excel. We want to check if the column “Value” means is significantly greater than 30. In the Data Analysis tool, there is no built-in option for one sample t test.

Because of that we will create a Dummy column with dummy zero data and choose *t-Test: Two-Sample Assuming Unequal Variances*:

* Figure 3.** One sample t test in Excel*

For *Variable 1 Range* select the data to be tested (in our example column B) and for *Variable 2 Range* select the Dummy column data. The *Hypothesized Mean Difference* is number 30 because we want to check if column B means is significantly greater than 30. Select the checkbox* Labels* and in Output options define the location of the data to be presented.

* Figure 4.** One sample t test** parameters*

* Figure 5. The result of the **one sample t test in Excel*

Since t Stat is greater than t Critical one-tail and P(T<=t) one-tail is less than α, H0 hypothesis is rejected.

* Figure 6. Rejected H0 Hypothesis*

**Two-Sample t Test in Excel**

If we want to calculate two samples paired t test in Excel, in the Data Analysis tab we should choose *t-Test: Paired Two Sample for Means*.

For *Variable 1 Range* select the sample 1 range (column B) and for *Variable 2 Range* select the sample 2 data (column C) while the *Hypothesized Mean Difference *is 0. Select the checkbox* Labels* and in Output options define the location of the data to be presented.

* Figure 7. **Two sample t test excel** data inputs*

Sample 2 data (column C) while the *Hypothesized Mean Difference* is 0. Select the checkbox* Labels* and in Output options define the location of the data to be presented.

* Figure 8. **Two sample t test excel** output*

As a result H0 hypothesis is accepted because absolute t Stat value is less than t Critical one-tail.

**How to Do Unpaired t Test in Excel**

To do unpaired t test in Excel we should select in the Data Analysis tab* t-Test: Two-Sample Assuming Unequal Variances* or *t-Test: Two-Sample Assuming Equal Variances*. In the following example we will do the unpaired t Test in Excel with unequal variances.

For *Variable 1 Range* we should select column B and for *Variable 2 Range* column C. The *Hypothesized Mean Difference *is 0 and alpha is 0.05. After selecting the checkbox* Labels* we should define in Output options the location of the data to be presented.

* Figure 9. **Unpaired t test in excel –**data inputs*

Since absolute t Stat value is less than t Critical one-tail value and H0 hypothesis is accepted:

*Figure 10. **Independent t test in excel** assuming unequal variances*

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment