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.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Kasper Langmann, Co-founder of Spreadsheeto

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:

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

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

gym program p value dataset

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

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’
loading the analysis toolpak in 5 clicks

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

data from the tab list

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

data analysis button on analysis category

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:

t-test tool results

On the results, we can see that the p-value with the one-tail test is 0.0063 or 0.63% and the p-value with the two-tail test is 0.0127 or 1.27%.

Kasper Langmann, Co-founder of Spreadsheeto

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)

how to use the t-test function

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.

Kasper Langmann, Co-founder of Spreadsheeto
2019-10-08T12:46:50+00:00