How to Get the p-Value in Excel?

by

The p-value is an important statistical term in hypothesis testing.

This value is one of the indicators that help decide whether a null hypothesis being tested can be rejected or not.   

In this tutorial we will show you two ways to get the p-value in Excel:

  • Using the Excel T.TEST function
  • Using Excel’s Data Analysis Toolpak

What is the p-Value and How is it Used?

The p-value is commonly used in addition to other pre-selected confidence levels for hypothesis testing.

In simple words, the p-value is the probability for a null hypothesis (which is being tested) to be true. The ‘p’ is short for ‘probability’.

This value gives us an idea of how statistically significant is the evidence relating to a given hypothesis.

The smaller the p-value, the greater is the evidence in support of rejecting the null hypotheses.

A null hypothesis is an initial claim about a population (or process that generates data). An alternative hypothesis states whether the actual observations differ from the assumed values (as stated in the null hypothesis).

The p-value is commonly used in hypothesis testing in fields like physics, chemistry, economics, finance, and more. 

Since it is a probability value, it can be expressed either in the form of a decimal number or a percentage. 

A p-value of less than 0.05 is generally considered as an indication to reject the null hypotheses. However, this cut-off point varies according to application.

Also read: How to Calculate the Interquartile Range in Excel?

Two Ways to Get the p-Value in Excel

The calculation of the p-value can be quite complex when done manually.

The value is calculated from the deviation between the observed value and a chosen reference value, given the probability distribution of the statistic.

A bigger difference between the values is indicated by a lower p-value.

The calculation involves the application of integral calculus, which can get quite complex. So most of the time, statisticians refer to a table to obtain the p-value.

This table has been created based on an assumed or known probability distribution of the statistic being tested.

A p-table might not always be available to obtain the p-value.

So an easier way would be to use spreadsheet software like Microsoft Excel.

Let us look at two ways to get the p-value in Excel.

To demonstrate both methods, we will use the following data on scores obtained by students from two different classes:

Scores dataset

Let’s say we want to see if there is any significant difference in the scores obtained by students between these two classes. 

We are going to test the null hypothesis that there is no significant difference in test scores between the two classes.

For this, we will need to compute the p-value.  

Using the T.TEST Formula to Find the p-Value in Excel

A t-test is a statistic that can be used to determine if there is a significant difference between the means of two groups.

The T.TEST formula in Excel is used to perform a t-test and return the probability of a significant difference between two datasets, in other words, the p-value.

The formula also takes into account whether the data being used is a one-tail or two-tail distribution, as well as the kind of variance in the distribution.

The syntax for the T.TEST function is as follows:

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

Here,

  • array1 is the range of cells corresponding to the first set of values
  • array2 is the range of cells corresponding to the second set of values
  • tails is an integer that specifies the number of distribution tails. A value of 1 indicates a one-tailed distribution, while a value of 2 indicates a two-tailed distribution.
  • type is an integer that specifies the type of t-Test that you want to perform. You will find in the table below the different integer values this parameter can have and what each value indicates.
Type valueTest being performed
1Paired – this tests if the mean between paired sets is equal.
2Two-sample equal variance- this tests if the variance of means between two sets is equal.
3Two-sample unequal variance- this tests if the variance of means between two sets is unequal.

To apply the T.TEST function to our dataset, we can use the following formula:

=T.TEST(A2:A8,B2:B8,2,1)

The above formula computes the p-value associated with a paired t-test for ranges A2:A8 and B2:B8, with a two-tailed distribution.

As can be seen from the screenshot below, the returned p-value is 0.259408.

T.TEST formula to calcualte p value

Since this is not less than α = .05, we fail to reject the null hypothesis of the test.

We do not have sufficient evidence to say that the mean height between the two species is different.

Using the Data Analysis Toolpack to Find the p-Value in Excel

The T.TEST method is great if you only want to compute the p-value.

However, proper hypothesis testing involves computation of a number of other indicators too, like the Pearson’s coefficient, t-values, and more.

If you want to look at the bigger picture, so that you have more insight into the data distributions, then Excel’s Data Analysis Toolpak would be a better option.

The Analysis ToolPak is an Excel add-in that gives access to data analysis tools for financial, statistical, and engineering data analysis.

To use the Data Analysis Toolpak, you need to first upload it into Excel. For this follow the steps outlined below:

  1. Click on the File tab.
Click the File tab
  1. Select Options from the menu items that appear.
Click on Options
  1. This will open the ‘Excel Options’ window. Select ‘Add-ins’ from the left sidebar of the window.
Select Add-ins in the left pane
  1. In the dropdown box next to ‘Manage’ (at the bottom of the window), make sure that the ‘Excel Add-ins’ option is selected.
Select Excel Add-ins from the drop-down
  1. Click Go.
  2. This opens the ‘Add-ins’ dialog window.
  3. Make sure the box next to ‘Analysis ToolPak’ is checked.
Check the Analysis Toolpak option
  1. Click OK.

Your Data Analysis Toolpak should now be available as a menu item under ‘Analysis’ in the Data tab.

Click on the Data Analysis option in the Data tab

Once your Analysis Toolpak is loaded you can analyze your data by the following steps:

  1. From the Data tab, click on ‘Data Analysis’.
  2. This opens the ‘Data Analysis’ window.
  3. Under ‘Analysis tools’ you will see a list of analysis tools available.
Select the ‘t-Test: Paired Two Sample for Means’ option in hte list
  1. Scroll down and select ‘t-Test: Paired Two Sample for Means’.
  2. Click OK.
  3. Another window will open that will let you select all the required inputs for the t-Test.
  4. Enter the inputs here as follows:
  • Variable 1 Range: Type in the cell reference for the range of cells that contain the first data set.
  • Variable 2 Range: Type in the cell reference for the range of cells that contain the second data set.
  • Hypothesized Mean Difference – Enter your hypothesized mean or leave it blank if you want.
  • Labels – Check this box if you have labels included in your variable 1 and variable 2 ranges.
  • Alpha – Enter your required alpha value or leave the default value of 0.05.
  • Output options – Select where you want the results of the analysis to be displayed.

For our example, here are the inputs you can enter:

‘t-Test: Paired Two Sample for Means’ Input box

Note that we specified we want to display the result in a new tab by checking the radio button next to ‘New Worksheet Ply’.

Here are the results displayed based on our example data:

Result of the T-Test with the P one tail and two tail values

Notice that the p-value with the two-tail test is found to be 0.259408, the same value that we obtained by the first method.

We found from the above data analysis that the p-value with the one-tail test is  0.129704, and that with the two-tail test is 0.259408.

Since the p-value obtained in both results are more than 0.05 we can go ahead and accept the null hypothesis that there is no significant difference in the average scores of students between the two classes.

In this tutorial, we showed you two ways to get the p-value in Excel. If you need to get a detailed analysis of your data, then the second method (using the Data Analysis Toolpak) is a good option.

However, if you are after just the p-value, then the first method (using the T.TEST function) should be enough.

Other articles you may also find useful:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment