How to Calculate P-Value in Excel Using 2 Methods

By Indeed Editorial Team

Published April 20, 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.

There are two basic types of hypotheses in statistics, the null hypothesis and the alternative hypothesis, which help determine the similarity between two data sets. The p-value allows researchers to accept or reject either of these hypotheses when making comparisons. Learning how to calculate the p-value can help you determine the support for or against a claim by quantifying the evidence. In this article, we explain how to calculate p-value in Excel using the t-Test tool and the T.TEST function, explore the p-value's relationship with hypotheses and significance level, and offer examples.

How to calculate p-value in Excel using the t-Test tool

Follow the steps below for how to calculate p-value in Excel using the t-Test tool:

1. Enable the data analysis command

To use this command, enable it on your spreadsheet. If you want to confirm if the data analysis is active, check the top left of your toolbar for “Data.” Click on this button and look to the far right. The data analysis command is typically the last command on the right side of the “Data” panel. If the command isn't active, you can easily activate it by loading the “Analysis ToolPak” add-in program. You may activate this add-in program by following the steps below:

  1. Click the “File” tab, click “Options,” and then click the “Add-ins” category.

  2. Click the “Manage” box, select “Excel Add-ins,” and click “Go.”

  3. In the “Add-ins available” box, select the “Analysis ToolPak” check box, and click “OK.”

If the system prompts you that the Analysis ToolPak is not on your computer, click “Yes” to install it. After installing the ToolPak successfully, the data analysis button appears when you click on “Data” at the far right of the toolbar.

2. Select the ranges of the variable

Go to “Data Analysis” by clicking “Data” and select “t-Test: Paired Two Sample for Means” by scrolling through the available options. Then click “OK.” Select the range of the variables in the boxes with the labels “Variable 1 Range” and “Variable 2 Range.” Ranges are the cells that belong to the two groups of data, whose probability you're trying to calculate.

Click and hold the first value in the data and drag it to the desired cell. Do this for both variable ranges. Set the “Alpha” value to 0.05 if it's another value. The Alpha value is the significance level, and it helps determine the validity of the result.

3. Select the output range

Click on an empty cell either close to the cells containing your values or not to be the output range and click “OK.” The output range is where the program displays the analysis results. The system shows a p-value with a one-tailed test and a p-value with two-tailed tests.

A one-tailed hypothesis, directional, or one-sided test involves testing effects in one direction or for only one extreme. The significance level or percentage follows one tail of the distribution to determine whether the value is greater or less than the referent, but not both. In a two-tailed hypothesis, non-directional, or two-sided test, you can test for effects in both directions. After the system displays the p-values for the data, you can determine whether to accept the null hypothesis or reject it.

How to calculate p-value using T.TEST function

You can follow the steps below when learning how to calculate p-value in Excel using the T.TEST function:

1. Open the T.TEST function

T.TEST is an inbuilt Excel function that helps determine whether two data samples originate from two underlying populations with the same mean. Select any desired cell where the p-value may display and type “=T.TEST” to open the function. The formula for the T.TEST function is as follows:

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

2. Input the required data sets

The T.TEST syntax function contains four compulsory arguments, which are the following:

  • Array 1: This array contains the first data set, like the Variable 1 Range in the t-Test tool.

  • Array 2: This array contains the second data set, like the Variable 2 Range in the t-Test tool.

  • Tails: This component specifies the number of distribution tails. If you set tails to “1,” T.TEST uses the one-tailed distribution, and if you set tails to "2," T.TEST uses the two-tailed distribution.

  • Type: Type specifies the kind of t-Test to perform. The three t-test types under this function include the paired t-test, the two-sample equal variance t-test, and the two-sample unequal variance t-test.

You can insert the required arrays by clicking the first value in the list and dragging it to the last value.

3. Calculate the p-value

Close the formula to get the p-value result. The value of your result typically depends on the arguments that you set. You can then determine the statistical significance of the result based on the significance level or percentage displayed in the cell.

Important considerations for using the T.TEST function

When using the T.TEST function, try to remember the following because they're often important:

  • The tails and type arguments can only contain integers. The tails argument can only be 1 or 2, while the type arguments can be 1, 2, or 3.

  • The system returns an error when the arrays have different lengths, and the type argument is “1”.

  • If you give non-numeric values to the tails or type arguments, T.TEST returns an error.

Related: Advanced Excel Skills: Definitions and Examples

What is the p-value?

The p-value or probability value measures how likely it is that an observed difference occurred by random chance. The null hypothesis is a concept in statistics that says there's no statistical significance or difference between specific characteristics of a population. The p-value helps validate or reject the null hypothesis based on the significance level.

The lower the p-value, the greater the statistical significance between the observed values. That means the closer the p-value is to 0, the greater the difference between the observed data. The higher the p-value, the greater the similarity. P-value can range from 0–1, with “0” meaning there's no observable similarity and “1” meaning absolute similarity.

Related: 5 Basic Excel Skills and How to Include Them in Your Resume

Relationship between p-value, significance level, null hypothesis, and alternative hypothesis

The significance level measures the required evidence in your sample to reject the null hypothesis or accept the alternative hypothesis. The alternative hypothesis states that any two observed data are significantly different. Before starting the experiment, researchers determine the significance level and typically set its value to 0.05 or 5%. This value indicates a 5% risk of concluding that a difference exists when there's no actual difference between the data sets.

A lower significance level, such as 0.01, indicates that you require stronger evidence before rejecting the null hypothesis. After you calculate your p-value, compare it with the significance level. If the p-value is less than your significance level, you can reject the null hypothesis and conclude that the effect is statically significant.

Related: 12 Jobs for Statistics Majors (With Salaries and Duties)

Examples of calculating p-value in Excel

The examples below clarify how to calculate the p-value using the two methods:

Example using the t-Test tool

Below is an example showing how to calculate p-value using the t-Test tool:

A fitness agency wants to help its clients lose weight through various methods, such as exercise and weight loss drugs. The company recently gave weight-loss drugs to 10 of their clients and recorded their weights before and after using the drugs. The clients' weights before using the drugs are 70, 71, 76, 84, 72, 78, 79, 80, 74, 75, and their weights after using it are 67, 70, 77, 80, 70, 79, 70, 77, 70, 70.

The company decides to check the effectiveness of the drugs by calculating the p-value of the new weights relative to the old weights. They used the t-Test: Paired Two Sample for Means feature of Microsoft Excel to determine this value. They inserted the previous weights in the Variable 1 Range and the new weights in the Variable 2 Range and set Alpha, which is the significance level, to 0.05. The p-value for a one-tailed test is 0.006389191 while the two-tailed test is 0.012778382. The p-value is less than 0.05, which means the null hypothesis is false.

Example using the T.TEST function

Below is an example showing how to calculate p-value using the T.TEST function:

A local high school compared the test results of 10 students before and after exercising. The results of the students before exercising are 85, 70, 78, 75, 88, 80, 82, 89, 98, 77 while the results after exercise are 80, 72, 75, 80, 80, 82, 75, 80, 95, 79. To determine how exercise can affect test scores, the school calculated the p-value of the results using the T.TEST function in Microsoft Excel.

They arranged the first set of results in an array and put them in the Array 1 argument. Then they placed the post-exercise results in the Array 2 argument and set the tails and type to 1. The resulting p-value of the two data sets is 0.078043. This value is greater than the significance level of 0.05, which means there's weak evidence against the null hypothesis. Since the first and second results are very similar, the school decided that exercise didn't influence the results.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles