How To Run a t-Test in Microsoft Excel (With Tips)

By Indeed Editorial Team

Published April 8, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Running t-Tests in Excel often helps people perform statistical analysis and find and organize data in a more efficient manner. Learning how to run these tests in Excel may help some people improve their work performance and their skills using this application. In this article, we discuss what a t-Test does, how to run a t-Test in Excel using two methods and some tips that may help you use this tool in Excel.

Related: 25 Best Excel Tips To Optimize Your Use

What is a t-Test, and what does it do?

A t-Test is a hypothesis test that you can use to evaluate the means of two or more data groups. The t-Test uses sample data from your selected groups to form estimates of the entire population's properties. The test has three functions. It may determine the mean differences between groups, the differences between paired means or if a mean differentiates from a target value.

You can perform a t-Test by using the TTEST function or the Data Analysis plug-in. Using this plug-in requires first installing the Excel add-in Data Analysis ToolPak. You can determine if you have this free add-in by first clicking the "Data" tab on the Ribbon menu in Excel. If you've installed the ToolPak, the "Data Analysis" button appears in the "Analyze" group on this tab. If you don't see this button, you can install this function by following the steps below:

  1. Select the "File" tab on the Ribbon menu.

  2. Select "Options" on the bottom left of the menu. A new window appears.

  3. Select "Add-Ins" on this window.

  4. Under "Inactive Application Add-Ins," select "Analysis Toolpak" and click "GO."

  5. In the new window, select the box next to "Analysis ToolPak" and click "OK."

Related: Basic Excel Formulas and How To Use Them

How to run a t-Test in Excel using the Data Analysis ToolPak

The Data Analysis ToolPak offers three different t-Tests. A Paired Two Sample for Means t-Test analyzes two data sets from related groups. A Two-Sample Assuming Equal Variances t-Test analyzes data from two unrelated groups with equal data variances. A Two-Sample Assuming Unequal Variances t-Test analyzes data from two unrelated groups with unequal data variances. If you're unsure if your data variances are equal, you can perform an f-Test. Here's a list of steps that may help you use the Data Analysis plug-in to run a t-Test:

  1. Create two columns of data.

  2. Select a third column next to these data groups. This is where the ToolPak places the test results.

  3. Select the "Data" tab on the Ribbon menu in Excel, then the "Data Analysis" button in the "Analyze" group on this tab. A new window appears.

  4. Select the t-Test that works best for your data and then click "OK." This example uses the Two-Sample Assuming Equal Variances test.

  5. A new window appears with "Input" and "Output" sections. Enter the data range from your first and second columns into the boxes underneath "Input."

  6. Select the box next to "Labels" if your columns contain headers.

  7. Enter the "Alpha" number you want to use in the "Alpha" box. This is the level of significance, and Excel usually sets this at 0.05 (5%).

  8. Enter the cell range of the third column where you want to display the results of the test. This is underneath the "Output" section.

  9. Click "OK" when you're done entering your data.

After clicking "OK," the Data Analysis ToolPak enters the results of the test in the range of cells you selected. Here's a list of the sections of results and what they mean:

  • Mean: The average value of each group.

  • Variance: The data variance for each group.

  • Observations: The number of samples in each group.

  • Pooled variance: The average statistical variance between the groups.

  • Hypothesized mean difference: The hypothesized mean difference if you selected one. This example didn't use one.

  • df: The degrees of freedom for the test. This is the number of values that may vary independently.

  • t Stat: The T statistic.

  • P(T<=t) one-tail: The P value for a one-tailed analysis. A one-tailed analysis detects the differences between means in only one direction.

  • t Critical one-tail: The T statistic cut-off value for a one-tailed analysis.

  • P(T<=t) two-tail: The P value for a two-tailed analysis. A two-tailed analysis detects differences in both directions, meaning greater-than or less-than.

  • t Critical two-tail: The T statistic cut-off value for a two-tailed analysis.

Related: How To Use Excel Real-Time Data (With Definition and Tips)

How to run a t-Test in Excel using the TTEST function

The TTEST function doesn't require the Data Analysis ToolPak. This function produces only the P value in the results, which is the number that describes the likelihood of finding a specific set of observations if the null hypothesis is true. You can manually type this function, or you can visit the "Functions" tab and enter your information in the TTEST window. Here's a list of the fields in this TTEST window and what they mean:

  • Array1: The first group's data range.

  • Array2: The second group's data range.

  • Tails: The number of tails for the analysis. Type the number 1 for a one-tailed analysis, and 2 for a two-tailed analysis.

  • Type: The type of t-Test you want to perform. Type the number 1 for a paired two-sample means test, 2 for a two-Sample Assuming Equal Variances test and 3 for a Two-Sample Assuming Unequal Variances test.

Here's a list of steps for using the TTEST function window that may help you perform a t-Test:

  1. Create two columns with your two groups of data.

  2. Select the cell where you want to enter the final P value after the test. You can place this in a cell next to the two groups.

  3. Select the "Formulas" tab in the Ribbon menu. Then select "Insert Function" on this tab.

  4. Find the TTEST function from the list and click "OK." A new window appears with four fields.

  5. Enter the information in the four fields.

  6. Click "OK" in the function window. This places the P value in the selected cell.

Tips for using t-Tests and Excel

Here's a list of tips that may help you use Excel more efficiently when performing t-Tests:

  • Use keyboard shortcuts to save time. Keyboard shortcuts such as "CTRL+Z" may help you save time by reducing the number of steps it takes to perform certain actions. For example, the "CTRL+Z" shortcut undoes the most recent action.

  • Use labels on tables and columns. Labeling your data may help you identify and compare data between sets.

  • Try color-coding your data sets. Color coding is another way some people organize their data for easier readability. Also, consider using conditional formatting to automatically change a cell's color based on its data.

Read more: How To Color Code in Excel in 5 Steps (With Tips)

Please note that the company mentioned in this article isn't affiliated with Indeed.

Explore more articles