**How to** **Calculate P Value**** in Excel**

**How to**

**Calculate P Value**

**in Excel**

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

The p-value, short for **probability value**, is an important concept in statistical hypothesis testing.

Its use in **hypothesis testing **is common in many fields like finance, physics, economics, psychology, and many others.

Knowing how to compute the probability value using Excel is a great time-saver.

In this article, we’ll show you 2 easy ways you can calculate the p-value in Excel.

Let’s get started!

**This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.*

**The concept of P Value**

Basically, the p-value is used in hypothesis testing to **quantify the idea of statistical significance of evidence**.

It’s a value that can be expressed in *percentage *or *decimal *to support or reject the null hypothesis.

In Excel, the p-value is expressed in decimal.

But in reporting, try to use the percentage form (multiply the decimal form by 100) as some people prefer hearing it that way like it’s a part of a whole.

As for the results:

**Significant**: <=5%**Marginally significant**: <=10%**Insignificant**: >10%

As stated earlier, there are two ways to get the p-value in Excel:

- t-Test tool in the analysis toolpak
- The ‘T.TEST’ function

For this tutorial, we’ll be using the gym program data set shown below and compute the p-value:

**The t-Test tool in the Analysis ToolPak**

To use the t-Test tool in the Analysis ToolPak, you have to load the toolpak first in Excel.

Here’s a summary on how to add the analysis tool:

- Click
**‘File’**from the tab list - Click
**‘Options’**on the bottom of the left-hand sidebar - Click
**‘Add-ins’**on the left-hand sidebar of the window - On the bottom part, make sure that
**‘Excel Add-ins’**is selected - Then, press
**‘Go’** - On the next window, check the
**‘Analysis ToolPak’** - Click
**‘OK’**

Now that the toolpak is loaded, **click **** ‘Data’ **from the tab list.

On the **‘Analysis’ **group, **click the ***‘Data Analysis’ ***icon**.

On the window, **select **** ‘t-Test: Paired Two Sample for Means’**. Then,

**click**

**.**

*‘OK’*Another window will open with the following options:

**‘Variable 1 Range’**– the cell range of the ‘before’ data (‘Weight’)**‘Variable 2 Range’**– the cell range of the ‘after’ data (‘Result’)**‘Hypothesized Mean Difference’**– the hypothesized mean difference; can be left blank**‘Labels’**– if labels were included in the ranges**‘Alpha’**– common alpha values are 0.05 and 0.01**‘Output options’**– determine where the results will be placed

As an example, let’s supply the tool with the following variables:

**‘Variable 1 Range’**– $B$2:$B$12**‘Variable 2 Range’**– $C$2:$C$12**‘Labels’**– Check**‘Alpha’**– 0.05**‘Output options’**– ‘Output Range: $F$2’

The results will be displayed on the range provided:

On the results, we can see that the p-value with the one-tail test is

0.0063or0.63%and the p-value with the two-tail test is0.0127or1.27%.

Both results show that the p-value is **lower than 5%**, which means the null hypothesis is **significant**.

**The ‘T.TEST’ function**

It’s also possible to get the p-value using this function.

It’s syntax is simple:

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

Parameters:

**‘array1’**– the cell range of the first data set**‘array2’**– the cell range of the second data set**‘tails’**– represents the number of distribution tails; 1 = one-tailed and 2 = two-tailed**‘type’**– the t-test type to perform; 1 = paired; 2 = two-sample equal variance (homoscedastic); 3 = two-sample unequal variance (heteroscedastic)

Now, all you got to do is **use the formula **and provide it with the appropriate variables:

*p-value (one-tailed):*

**=T.TEST(B2:B11,C2:C11,1,1)**

*p-value (two-tailed):*

**=T.TEST(B2:B11,C2:C11,2,1)**

As you can see, using the ‘T.TEST’ function will give you exactly the same result as the t-Test tool.

**Wrapping things up…**

Whichever of the 2 methods we showed you to calculate the p-value works and will give you the same result.

If you like to have a detailed analysis, go with the analysis toolpak’s t-test tool. If the p-value is all you’re after, the function will do just that.

If you’re not sure which to use, simply give each one a try. We included an exercise file in this tutorial you can use to follow the steps described above.