How to Calculate P Value in Excel: Step-by-Step Guide (2024)

P-value stands for probability value. It is used to define the statistical significance of any finding.

You will see it being used for statistical hypothesis testing everywhere around you. Whether you are a statistician, an engineer, an accountant, or a scientist – you’ll have to use p-value for a variety of jobs.

There are two easy ways to calculate p-value in Excel. 😀

Download our sample workbook here and follow the guide till the end to learn them both.

What is a p-value?

P-Value is used to perform hypothesis testing. It indicates how statistically significant a value might be.

The p-value tells if a null hypothesis is valid or not. It can be measured in terms of its significance ranging from 0 to 1.

The lower the p-value, the greater the chances of invalidity of the null hypothesis. (This means there are more chances for the alternative hypothesis to be true)

Higher the p-value, the lesser the chance of invalidity of the null hypothesis.

The measure of the p-value:

P-value can be somewhere between 0 to 1. You might express it as a decimal, for example, 0.5. Alternatively, you might express it as a percentage i.e. 50%.

The significance of a p-value can be measured on three levels.

1. Significant: Equal to or less than 5% (0.05)
2. Marginally Significant: Between 0.05 (5%) to 0.10 (10%)
3. Insignificant: More than 0.10 (10%)

The validity of the null hypothesis depends upon the p-value.

How to calculate p-value with Analysis ToolPak

There are two main ways how you can find p-value in Excel. Out of these two, the first one is through the T.test tool in Analysis ToolPak.

Let’s begin learning.

The image below shows the actual and expected scores for some students.

It’s time we compute the p-value for this data! 🤨

Begin with adding ‘Analysis ToolPak’ to your Excel.

1. Go to File > Options > Add-ins.

2. Check the option for Excel Add-ins at the bottom.

3. Check ‘Analysis ToolPak’ from the next window and hit ‘Ok’.

And there you are!

4. Reach out for the ‘Data’ tab on the Ribbon to see something new added.

5. Go to Data > Data Analysis.

This will launch the Data Analysis tool kit, as shown below.

6. Select ‘t-Test: Paired Two Sample for Means’ and click ‘Okay’.

In the ‘t-Test: Paired Two Sample for Means’ dialogue box that opens up, make the right inputs.

7. Select the first data set (Actual scores in this case) as Variable Range 1.

8. Select the second data set (Expected scores in this case) as Variable Range 2.

9. As our data consists of labels (headers), check the ‘Labels’ option.

Don’t miss out on the Alpha value. An appropriate measure of the Alpha value is 0.05.

10. Define the output range – where do you want the t-tail test’s results to appear?

We have created a reference to Cell E1 as the Output Range in this very instance.

You are all good to go now! Press ‘Ok’ to have the following results.

Excel gives a whole table with many statistical results in addition to the one-tail and two-tail p-value.

Pro Tip!

Don’t want the p-value in terms of decimals but percentage?

Select both the p-values and go to Home > Format > %.

Adjust the decimal number to your choice.

Decoding the P-value:

Performing the t-test to find the p-value might not be as difficult as is to decode the obtained results. Let’s make it easy for you.

The one-tail test gives a p-value of 0.383 (38.3%), which is more than 0.10 (10%).

This means the p-value indicates that there is little evidence against the null hypothesis being invalid. The null hypothesis in this case holds true.

Simply put, the ‘Null hypothesis’ of there being a relation between the actual scores and the expected scores stands valid (and the alternative hypothesis fails).

The two-tailed test gives a p-value of 0.766 (76.6%), which is much more than the p-value above and 0.10 (10%).

This serves as strong evidence that the null hypothesis is true.

Find the p-value with the T-TEST function

If you only need the p-values, you need not follow the process above.

Instead, you can instantly find the p-values for any given data set by using the TEST function. 😄

  One-Tailed P-Value:

1. Write the TEST function as follows:

The first argument (array_1) refers to the range that contains the first variable (actual scores).

2. Create a reference to the second variable (expected scores) as array_2.

=T.TEST (B1:B8, C1:C8,

3. Set the tails argument as 1.

=T.TEST (B1:B8, C1:C8, 1

It refers to the number of distribution tails. We have set it to 1 because we are finding a one-tailed p-value.

4. The t-test type argument can be set to three modes.

1 = paired.

2 = two-sample equal variance (homoscedastic)

3 = two-sample unequal variance (heteroscedastic)

We are setting it to ‘1’.

=T.TEST (B1:B8, C1:C8, 1, 1)

All done! Hit enter to have the one-tailed p-value for this data set generated.

  Two-Tailed P-Value:

Can you generate a two-tailed P value in Excel using the T.Test function? Yes, see below.

1. Write the following formula:

=T.TEST (B1:B8, C1:C8, 2, 1)

Set the tails argument to ‘2’ instead of ‘1’.

Everything else remains the same. Go ahead and hit ‘Enter’.

And there you go! Computing p-value using the simple formula of T.Test is more like a child’s play.

Did you notice how both methods yield the same result?

Both the one-tailed and two-tailed p-values stand tallied.

Which method to use? 🤔That depends upon your needs.

If you are only in need of the P-value, save time by using the T.Test function.

However, to fetch other details in respect of your dataset (Mean, Variables, etc.) too, better go with the T-Test tool in the Analysis ToolPak.

That’s it – Now what?

That’s all about finding p-values in Excel. We not only learned two different ways of finding p-values but also two different types of p-values – one-tailed and two-tailed.

However, that doesn’t even make an iota of the number of functions offered by Excel. Excel offers a wide variety of other useful functions that you’ll have to use time and time again.

Most importantly, the VLOOKUP, SUMIF, and IF functions.

Register for my free 30-minute email course to learn these functions (and more!).

Other relevant resources:

You might have to use the p-value function in pair with other statistical functions.

Do take a look at how to Calculate the Correlation Coefficient and Confidence Interval in Excel to manage all your statistical projects single-handedly.