two tailed hypothesis test excel

How to Do a T Test in Excel (2 Ways with Interpretation of Results)

Download the Practice Workbook

T Test.xlsx

T Test Types

There are two types of t-tests. They are:

• One-tailed t-test
• Two-tailed t-test

Each of them has 3 subtypes. They are:

• Two sample equal variance
• Two sample unequal variance

How to Do a T-Test in Excel: 2 Effective Ways

Method 1 – using the excel t.test or ttest function for a t-test, case 1.1 – two sample equal variance t-test.

In the dataset, you will see the prices of different laptops and smartphones. Here is a formula that performs a T-Test on the prices of these products and returns the t-test result.

=T.TEST(B5:B14,C5:C14,2,2)

We set the 3rd argument of the function to 2 as we are doing a two-tailed t-test on the dataset. The 4th argument should be 2 for a two-sample equal variance t-test.

Case 1.2 – Paired T-Test

We are going to apply another formula to calculate the Paired T-Test . The following dataset shows the performance mark of some employees in two different criteria.

=T.TEST(C5:C13,D5:D13,2,1)

Note: The explanation of the results is described in the following sections.

Method 2 – Using the Analysis ToolPak

• Go to the Options window.
• Select Add-ins and click on the Go button in the Manage section.

• The Add-ins window will appear. Select Analysis ToolPak and click OK again.

Case 2.1 – Tw-Sample Equal Variance T-Test

• Click on the Data Analysis button from the ribbon of the Data tab.
• The Data Analysis features will appear. Select t-Test: Two Sample Assuming Equal Variances and click OK .

• Set up the parameters for the t-test operation. Insert the Laptop and Smartphone prices as Variable 1 Range and Variable 2 Range. Include the headings in the range and check Labels.
• Set the value of Hypothesized Mean Difference to 0 .
• Select an Output option of your preference and click OK .

• As we have chosen a New Worksheet for the outputs, we will see the results in a new sheet.

Comments on Results

The output shows that the mean values for Laptops and Smartphones are 1608.85 and 1409.164 respectively. We can see from the Variances row that they are not precisely equal, but they are close enough to be assumed to have equal variances. The most relevant metric is the p-value .

The difference between means is statistically significant if the p-value is less than your significance level. Excel calculates p-values for one- and two-tailed T Tests .

One-tailed T Tests can detect only one direction of difference between means. A one-tailed test, for example, might only evaluate whether Smartphones have higher prices than Laptops . Two-tailed tests can reveal differences that are larger or smaller than. There are some other disadvantages to utilizing one-tailed testing, so I’ll continue with the conventional two-tailed results.

For our results, we’ll utilize P(T=t) two-tail, which is the p-value for the t-test’s two-tailed version. We cannot reject the null hypothesis because our p-value ( 0.095639932 ) is greater than the conventional significance level of 0.05 . The hypothesis that the population means differ is supported by our sample data. The mean price of Laptops is greater than the mean price of Smartphones’ .

The Analysis ToolPak also returns results for a one-tailed t-test . Here, the one-tailed P value of the two-sample equal variance t-test is 1.734 .

Case 2.2 – Paired T-Test

Select the t-Test: Paired Two Samples for Mean when you open the Data Analysis window.

The result shows that the mean for the Workpace is 104 and the mean for the Efficiency is 96.56 .

The difference between means is statistically significant if the p-value is less than your significance level. For our results, we’ll utilize P(T=t) two-tail, which is the p-value for the t-test’s two-tailed version. We cannot reject the null hypothesis because our p-value ( 0.188 ) is greater than the conventional significance level of 0.05 . The hypothesis that the population means differ is supported by our sample data. In particular, the Workpace mean exceeds the Efficiency mean.

How to Interpret T-Test Results in Excel

Let’s bring out the results again.

• The mean of laptop prices = 1608.85
• The mean of smartphone prices = 1409.164

ii. Variance

• The variance of laptop prices = 77622.597
• The variance of smartphone prices = 51313.7904

iii. Observations

The number of observations for both laptops and smartphones are 10 .

iv. Pooled Variance

The samples’ average variance, calculated by pooling the variances of each sample.

The mathematical formula for this parameter is:

((No of observations of Sample 1-1)*(Variance of Sample 1) + (No of observations of Sample 2-1)*(Variance of Sample 2))/(No of observations of Sample 1 + No of observations of Sample 2 – 2)

So it becomes: ((10-1)*77622.59676+(10-1)*51313.7904)/(10+10-2) = 64468.19358

v. Hypothesized Mean Difference

We “hypothesize” that the number is the difference between the two population means. In this situation, we chose 0 because we want to see if the difference between the means of the two populations is zero.

It indicates the value of the Degrees of Freedom. Formula for this parameter is:

No of observations of Sample 1 + No of observations of Sample 2 – 2 = 10 + 10 – 2 = 18

vii. t-Stat

The test statistic value of the t-Test operation.

The formula for this parameter is given below.

(Mean of Sample 1 – Mean of Sample 2)/(Square root of (Pooling Variance* (1/No of observations of Sample 1 + 1/No of observations of Sample 2)))

So it becomes: (1608.85 – 1409.164)/Sqrt(64468.19358 * (1/10 + 1/10)) = 1.758570846

viii. P(T<=t) two-tail

A two-tailed t-test’s p-value. This value can be found by entering t = 1.758570846 with 18 degrees of freedom into any T Score to P Value Calculator.

In this situation, the value of p is 0.095639932 . Because this is greater than 0.05 , we cannot reject the null hypothesis. This suggests that we lack adequate evidence to conclude that the two population means differ.

ix. t-Critical two-tail

This is the test’s crucial value. A t-Critical value Calculator with 18 degrees of freedom and a 95% confidence level can be used to calculate this number.

In this instance, the critical value is 2.10092204 . We cannot reject the null hypothesis because our test statistic t is less than this number. Again, we lack adequate information to conclude that the two population means are distinct.

Things to Remember

• Excel demands that your data be arranged in columns, with data from each group in a separate column. The first row should have labels or headers.
• Clearly state your null hypothesis (usually that there is no significant difference between the group means) and your alternative hypothesis (the opposite of the null hypothesis).
• As a result of the t-test, Excel returns the p-value. A small p-value (usually less than the specified alpha level) indicates that the null hypothesis may be rejected and that there is a substantial difference between the group means.

Frequently Asked Questions

Can I perform a t-test on unequal sample sizes in Excel?

Yes, you can use the T.TEST function to do a t-test on unequal sample sizes. When calculating the test statistic, Excel automatically accounts for unequal sample sizes.

What is the difference between a one-tailed and a two-tailed t-test?

A one-tailed t-test determines if the means of the two groups differ substantially in a given direction (e.g., greater or smaller). A two-tailed t-test looks for any significant difference, regardless of direction.

Can I calculate the effect size in Excel for t-tests?

While there is no built-in tool in Excel to calculate effect size, you can manually compute Cohen’s d for independent t-tests and paired sample correlations for paired t-tests using Excel’s basic mathematical operations.

<< Go Back to Excel for Statistics  |  Learn Excel

What is ExcelDemy?

Tags: Statistical Significance in Excel

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

Dr. Matt C. Howard

My research interests include (1) statistics and methodologies, (2) health and well-being, (3) personality and individual differences, as well as (4) technology-enhanced training and development., how to perform a two-sample t-test in excel.

I often use two-sample t-tests as an introduction to Excel in my undergraduate statistics courses – and sometimes my graduate courses, too.  Because the students are still getting used to functions in Excel, they tend to have many difficulties with this lesson.  For this reason, I created the page below to provide an easy-to-read guide on performing two-sample t-tests in Excel.  As always, if you have any questions, please email me a [email protected] !

Before learning about two-sample t-tests in Excel, we must first know what a two-sample t-test is used for.  The textbook definition says that a two-sample t-test is used to “determine whether two sets of data are significantly different from each other”; however, I am not a fan of this definition.  Instead, I prefer to say that a two-sample t-test is used to “test whether the means of a measured variable in two groups is significantly different.”  So, a two-sample t-test is used to answer questions that are similar to the following:

• In our sample, do women have better test grades than men?
• Are men taller than women?
• Do people in a class taught by Dr. Howard perform better on a test than those in Dr. Smith’s class?
• Do employees in Training Group A have better performance than Training Group B?

Now that we know what a two-sample t-test is used for, we can now calculate a two-sample t-test in Excel!  To begin, open your data in Excel.  If you don’t have a dataset, download the example dataset here. In the example dataset, we are comparing the test grades of two classes (Dr. Howard and Dr. Smith) to determine which class has higher grades on an exam.

The data should look something like this:

If it doesn’t, that is okay.  You can calculate a t-test in Excel fairly easily using many different data formats, but we’ll stick with the format in the picture for simplicity.

Once you have the data open, click on the Data tab at the top.  Then click on Data Analysis, as seen below:

Don’t see that tab? If not, go to my page on Activating the Data Analysis Tab. It should appear once you activate it.

Anyways, if it worked, the following window should have appeared.  You’ll want to click on t-test: Two-Sample Assuming Unequal Variances, and then press OK – as seen below:

Then, the following window should pop up.  On this window, you need to first click on the icon to identify your Variable 1 Range.  Then, you need to highlight (click and drag) your data and press the icon again (seen below).  This will identify the data representing Group 1 for your t-test, which is Dr. Howard’s exam scores in the current example.

Do the exact same thing but identify the Group 2 data instead, which is Dr. Smith’s exam scores in the current example.

Does your window now look like this?

If so, good!  Click “OK,” and let’s see what we get.

Uhh, what does this mean?  Well, let’s walk through it.

This is your t-statistic, which is the effect size.  It is a standardized estimate of the difference between the two groups.  Unless you know a decent amount about statistics, however, it probably doesn’t mean much to you.  So, we should look at the p-values instead…but which one?

Excel provides both one-tailed and two-tailed p-values.  Two-tailed p-values are more conservative estimates, and I usually use these to determine whether my results are significant.  So, let’s see what the two-tailed p-value is:

There it is!  From our results, we can identify that…

• The test statistic is: 2.783
• The p value is .015

Because our p-value is less than .05, we can reject the null and assume that a significant difference exists between our groups!  Yay!

But how do we know which group performed significantly better?  To do this, we have to calculate the means between the two groups.  In this example, they were:

• Mean Dr. Howard’s class – 88.5
• Mean Dr. Smith’s class – 75.3

So, because there was a significant difference and Dr. Howard’s class had the higher mean, we know that Dr. Howard’s class performed significantly better on the exam than Dr. Smith’s class.

We did it!  We calculated everything that we needed to know about the t-test!  Good work!

Do you still have any questions?  Or comments about this guide?  Feel free to email me at [email protected] .  I am always happy to chat!

Share this:

• Already have a WordPress.com account? Log in now.
• Subscribe Subscribed
• Copy shortlink
• Report this content
• View post in Reader
• Manage subscriptions
• Collapse this bar

T.DIST.2T function

Returns the two-tailed Student's t-distribution.

The Student's t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution.

T.DIST.2T(x,deg_freedom)

The T.DIST.2T function syntax has the following arguments:

X      Required. The numeric value at which to evaluate the distribution.

Deg_freedom      Required. An integer indicating the number of degrees of freedom.

If any argument is nonnumeric, T.DIST.2T returns the #VALUE! error value.

If deg_freedom < 1, T.DIST.2T returns the #NUM! error value.

If x < 0, then T.DIST.2T returns the #NUM! error value.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Hypothesis Testing in Excel: A Practical Handbook

Anit Mathew

Hypothesis testing is a crucial statistical method used to draw meaningful conclusions about populations based on sample data. Excel, a ubiquitous spreadsheet tool, can be a handy companion in conducting these tests. In this guide, we’ll delve into the process of hypothesis testing using Excel, specifically focusing on a t-test for comparing means.

Example Scenario: Let’s consider a scenario where we have two sets of data, Sample 1 and Sample 2, and we want to determine if their means are significantly different.

Step 1: Input Your Data Begin by organizing your data. Assume Sample 1 is in column A (A1:A10), and Sample 2 is in column B (B1:B10).

Step 2: Calculate Mean and Standard Deviation In a vacant cell, compute the mean using the AVERAGE function, and standard deviation using the STDEV function for both samples.

Step 3: Perform the t-test Utilize the T.TEST function to perform the t-test. The syntax is:

In our case:

The third argument (tails) is set to 2 for a two-tailed test, and the fourth argument (type) is set to 2 for a paired test.

Step 4: Interpret the Results The p-value generated by the t-test is crucial. If the p-value is less than your chosen significance level (commonly 0.05), you have evidence to reject the null hypothesis.

Excel provides a user-friendly platform for hypothesis testing, allowing even those without extensive statistical backgrounds to derive meaningful insights from their data. As with any statistical analysis, understanding the assumptions and conditions specific to your test is paramount. So, next time you find yourself exploring the nuances of your data, consider leveraging the power of Excel for hypothesis testing.

Written by Anit Mathew

Data Scientist, Analytics, Project Management, and Agile Methodology | MS in Data Science | Enhancing Efficiency and Driving Revenue Growth

Text to speech

#1 Excel tutorial on the net

This example teaches you how to perform a t-Test in Excel . The t-Test is used to test the null hypothesis that the means of two populations are equal.

Below you can find the study hours of 6 female students and 5 male students.

To perform a t-Test, execute the following steps.

1. First, perform an F-Test to determine if the variances of the two populations are equal. This is not the case.

2. On the Data tab, in the Analysis group, click Data Analysis.

Note: can't find the Data Analysis button? Click here to load the Analysis ToolPak add-in .

3. Select t-Test: Two-Sample Assuming Unequal Variances and click OK.

4. Click in the Variable 1 Range box and select the range A2:A7.

5. Click in the Variable 2 Range box and select the range B2:B6.

6. Click in the Hypothesized Mean Difference box and type 0 (H 0 : μ 1 - μ 2 = 0).

7. Click in the Output Range box and select cell E1.

8. Click OK.

Conclusion: We do a two-tail test (inequality). lf t Stat < -t Critical two-tail or t Stat > t Critical two-tail, we reject the null hypothesis. This is not the case, -2.365 < 1.473 < 2.365. Therefore, we do not reject the null hypothesis. The observed difference between the sample means (33 - 24.8) is not convincing enough to say that the average number of study hours between female and male students differ significantly.

• Analysis ToolPak

Learn more, it's easy

• Descriptive Statistics
• Moving Average
• Exponential Smoothing
• Correlation

Download Excel File

• t-test.xlsx

Next Chapter

• Create a Macro

Follow Excel Easy

Become an Excel Pro

• 300 Examples

t-Test • © 2010-2024 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA

Tail of the Test: Interpreting Excel Data Analysis t-test output

Excel’s Data Analysis ToolPak has three tools for running tests of hypotheses using the t-distribution – t-tests. The output from the tools can be a bit confusing because, unlike other statistical software, these do not allow you to specify the “tail of the test” before you run the analysis. Here is how Microsoft explains how to interpret the output here :

“Under the assumption of equal underlying population means, if t < 0, “P(T <= t) one-tail” gives the probability that a value of the t-Statistic would be observed that is more negative than t. If t >=0, “P(T <= t) one-tail” gives the probability that a value of the t-Statistic would be observed that is more positive than t. “t Critical one-tail” gives the cutoff value, so that the probability of observing a value of the t-Statistic greater than or equal to “t Critical one-tail” is Alpha. “P(T <= t) two-tail” gives the probability that a value of the t-Statistic would be observed that is larger in absolute value than t. “P Critical two-tail” gives the cutoff value, so that the probability of an observed t-Statistic larger in absolute value than “P Critical two-tail” is Alpha.

Understanding what all that means can be a bit daunting. Here is my attempt as a simpler explanation. 

For convenience, we are just using the output from the t-test: Two-Sample Assuming Unequal Variances , but the concepts apply to all three t-test tools. And to both one-sample and two-sample tests.

Example 1: Two-tail Test

Montgomery County is where the capital of Alabama is located. Traditionally, the county bridges between urban and rural Alabama communities. But in more recent times, Montgomery county has been classified as a “metro” county. The Montgomery mayor thinks the obesity prevalence in Montgomery County is significantly different from the average obesity prevalence in the other Alabama counties. Note: The sample data is from the National Health Interview Survey (About the National Health Interview Survey, 2019)

State the Null and Alternative hypotheses:

Null Hypothesis Ho: Obesity Prevalence Montgomery County = Mean Obesity Prevalence Other Counties Alternative Hypothesis Ha: Obesity Prevalence Montgomery County ≠ Mean Obesity Prevalence Other Counties

Note: The tail of the test is indicated by the math operator in the Alternative which is always a form of inequality: <, ≠, > . The Not equal ( ≠) symbol does not “point” to ether side, so this is a two-tail test.

Important:  Put the data ranges for the two groups in the in the same relationship as stated in the Alternative Hypothesis . This will help you keep “things” straight and make interpreting the results easier.

The Montgomery County sample (green highlight) is on the left side of the Null equation and should be placed in the Variable 1 Range field. The Other Counties group (red highlight) is on the right side of the Null equation and must be in the Variable 2 Range field. [your data can be anywhere in your worksheet, but it may be better to arrange it in the right relationship there as well.]

Here is the output from the Excel t-Test Two-sample Assuming Unequal Variances tool using a significance level alpha, α, of = 0.05.

Note that Montgomery County is on the left [Variable 1 range] and the Other Counties group is on the right [Variable 2 range] in the output. In each of the following examples, I will place the samples data ranges in the Excel dialog box to match their relationship in the hypotheses.

We can see that the Montgomery County prevalence is smaller than that of the average of the Other Counties. But is the apparent difference statistically significantly different?

To make our decision on rejecting (or not rejecting) the null, we can look at the three output values we have highlighted in yellow: the t statistic (t Stat), the two-tail p-value, and the two-tail critical value of t.

Why two-tail? Consider this graphic of the above test results:

The first rule for deciding whether to reject the Null tells us to compare our test statistic to the critical value.

When we have a two-tail test, we must put half of our significance level α of 5% in each tail to account for the possibility of our test statistic being either positive or negative, i.e. one sample mean being larger or smaller than the other. Putting 2.5% in each tail, we find two critical values: – 1.997 and + 1.997. Excel gives us the absolute value of the critical values – it ignores the sign or direction of the value and just uses the magnitude.  That means t Critical is -1.997 on the left side and +1.997 on the right side.

If our test statistic, the t Stat , falls in either rejection area, less than – 1.997 or larger than + 1.997, we must reject the Null. But here our t Stat of – 1.867 does not fall in either rejection area, so we must decide to not reject the Null. [an explanation of how the t-stat is calculated is not included here but the process is similar to finding a z-score.]

Another method to decide whether or not to reject the Null is to compare the two-tail p-value against our significance level. This is labeled “ P(T<=t) two-tail .” Thankfully, for a two-tail test, we can always just use the two-tail p-value the Excel tool gives us. It is 0.066 which is larger than our significance level, alpha, of 0.05. Thus, this rule also tells us to not reject the Null that there is no difference in the ratings.

Note that the two rules always agree , unless your technology tool is faulty, which is very rare.

Example 2: Left-tail Test – Negative t Stat

If the Mayor believes Montgomery has a lower mean obesity prevalence, we should run a left-tail test . Why not just run the two-tail? As you will see, a one-tail test gives us more “power” to detect a real effect that is there in the direction we believe it to have . The downside of a one-tail test, if you guess wrong and the effect is in the other direction, the test has no power to detect it.

Null Hypothesis Ho: Obesity Prevalence Montgomery County > = Mean Obesity Prevalence Other Counties Alternative Hypothesis Ha: Obesity Prevalence Montgomery County < Mean Obesity Prevalence Other Counties

Here is our output again with the one-tail values we need, highlighted in yellow.

The tail of the test is always determined by the math operator in the Alternative hypothesis, which in this example is the less than symbol. Remember the less than symbol < points to the left, so this is a left-tail test.

Here is our left-tail graphic:

Excel reports the absolute value of the critical values.  For a left-tail test, we need the negative t Critical one-tail which is -1.669. You should note that the one-tail critical value is “smaller” than the two-tail value of -1.997 because we put all of the alpha in that one tail which “pushes” the critical value toward the mean.

Now, the t Stat does fall in the rejection area, so the results say we must reject the Null hypothesis.

To use the second rule, we need to determine the one-tail p-value. Here, Excel’s output can be confusing. If the t Stat is positive, the Excel one-tail p-value is for the right tail – the probability of getting a positive value for t-stat that is as large or even larger.

If the t Stat is negative, the one-tail p-value is for the left tail – the probability of getting a value for t-stat that is as small (negative) or even smaller (more negative).

Here, the t Stat is negative, so the one-tail p-value is for the left tail test, which is what we need.  It is 0.033 which is less than our alpha of 0.05.

So, this result also tells us to reject the Null and we conclude the Obesity Prevalence in Montgomery County is significantly less than the Mean Obesity Prevalence of the other Alabama counties.

Example 3: Left-tail test – Positive t Stat

Let’s assume the State Public Health Officer claims that the average obesity prevalence in Alabama counties is less than that in Montgomery county.

The null and alternative are:

Null Hypothesis Ho: Mean Obesity Prevalence Other Counties > = Obesity Prevalence Montgomery County Alternative Hypothesis Ha: Mean Obesity Prevalence Other Counties < Obesity Prevalence Montgomery County

Here is the output of the t-test. Note that the Other Counties Data has been placed In Variable 1 (left) location to match its location in the Alternative Hypothesis.

Because the t Stat is positive, the Excel one-tail p-value is for the right tail test, which is the area under the curve above ( to the right of) the t Stat of 1.867. We need to find the complement (the area to the left of t Stat) to use it for the left tail test here. So, the left-tail p-value is 1 – 0.0332 = 0.9676. That is much larger than 0.05, so this method tells us to not reject the Null.

Remember the critical value for the left tail test is -1.669. Our t-stat of +1.867 does not fall in the rejection area to the left of -1.669, so this method also, as you should expect, tells us not to reject the Null.

Example 4: Right-tail test – Negative t Stat

Now our Mayor claims Montgomery County has a higher obesity prevalence than the average of the other Alabama counties.

Null Hypothesis Ho: Obesity Prevalence Montgomery County < = Mean Obesity Prevalence Other Counties Alternative Hypothesis Ha: Obesity Prevalence Montgomery County > Mean Obesity Prevalence Other Counties

Remember the tail of the test is indicated by the math operator in the Alternative . Here, the Alternative math operator is greater than > which points to the right, so this is a right-tail test.

Again, we place the data for the two samples to match their relationship in the hypotheses. Here is the highlighted one-tail t-test results:

Here is our graphic:

For a right-tail test, we are interested in what is happening on the right side of the curve. We use the positive one-tail critical value of +1.669 and we find our t Stat of -1.866 is very far away from the right tail rejection area. So, the first rule tells us to not reject the Null.

Because our t Stat is negative, the one-tail p-value is for the left-tail test. To find the right-side p-value , we must recall that the area under the curve is equal to 1. We want the right-tail p-value, so we must subtract that left-tail value from 1 to get the right-tail p-value. 1 – 0.0344 = 0.966, which is much larger than 0.05, so this rule tells us to not reject the Null of no difference in the ratings of the two groups.

Example 5: Right-tail – Positive t-stat

Finally, the State Public Health Officer claims that the average obesity prevalence in Alabama counties is greater than that in Montgomery county.

Null Hypothesis Ho: Mean Obesity Prevalence Other Counties < = Obesity Prevalence Montgomery County Alternative Hypothesis Ha: Mean Obesity Prevalence Other Counties > Obesity Prevalence Montgomery County

Using the p-value method, we see the t Stat is positive. That means the Excel one-tail p-value is for the right-tail test and we can use it directly to decide to reject the Null, the p-value of 0.033 < 0.05.

And our t-stat of +1.887 is greater than the right tail critical value of +1.669, so that too tells us to reject the Null of no difference. We conclude that there is sufficient evidence to support the claim that the mean obesity prevalence in the other Alabama counties is significantly greater than the prevalence in Montgomery county.

It is important to note, in the 2 nd example, that while using the left-tail test gave us the power to detect the significant “less than” difference between the prevalences, using the right-tail test does not detect any difference. That is why you need to be careful if you decide to use a one-tail test and be pretty sure of the direction of the difference. Using a two-tail test is a bit more conservative in that it will pick up a larger difference either way but misses the smaller significant “less than” difference on the left side.

Using the proper tail of the test makes all the difference.

About the National Health Interview Survey . (2019). Retrieved from CDC: https://www.cdc.gov/nchs/nhis/about_nhis.htm

Support.Office. (n.d.). Use the Analysis ToolPak to perform complex data analysis . Retrieved from Support.Office: http://bit.ly/2XXgg6T

10 Responses

Thank you so much for this comprehensive yet concise explanation! It really helped me understand the differences between the tests.

thank you so much, I looked almost all day for an explanation like this and am so glad I found it!

This is very, very helpful! You’re explanation is so easy to understand especially for people not knowledgeable at stat like me :D

Thanks for the positive feedback.

Great job, Dawn!! Seriously… clean and clear! :)

Very good. Enjoyed the way you explained using worked examples.

Very helpful and great. Was confused between methods to reject null hypothesis and here both methods are present together which cleared the things.

I could not remember about how to interpret t-test results according to whether I was performing a left- or right-tailed test. (How do I know which tail the t-statistic is calculated for?) Your explanation was extremely clear and helpful. Thank you!

Great explanation, Thank you.

Thanks for your detailed explanation. This can be a really complex area and you’ve explained it well. I also use the Pearson correlation for paired data which is another powerful tool.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed .

The Complete Guide: Hypothesis Testing in Excel

In statistics, a hypothesis test is used to test some assumption about a population parameter .

There are many different types of hypothesis tests you can perform depending on the type of data you’re working with and the goal of your analysis.

This tutorial explains how to perform the following types of hypothesis tests in Excel:

• One sample t-test
• Two sample t-test
• Paired samples t-test
• One proportion z-test
• Two proportion z-test

Let’s jump in!

Example 1: One Sample t-test in Excel

A one sample t-test is used to test whether or not the mean of a population is equal to some value.

For example, suppose a botanist wants to know if the mean height of a certain species of plant is equal to 15 inches.

To test this, she collects a random sample of 12 plants and records each of their heights in inches.

She would write the hypotheses for this particular one sample t-test as follows:

• H 0 :  µ = 15
• H A :  µ ≠15

Refer to this tutorial for a step-by-step explanation of how to perform this hypothesis test in Excel.

Example 2: Two Sample t-test in Excel

A two sample t-test is used to test whether or not the means of two populations are equal.

For example, suppose researchers want to know whether or not two different species of plants have the same mean height.

To test this, they collect a random sample of 20 plants from each species and measure their heights.

The researchers would write the hypotheses for this particular two sample t-test as follows:

• H 0 :  µ 1 = µ 2
• H A :  µ 1 ≠ µ 2

Example 3: Paired Samples t-test in Excel

A paired samples t-test is used to compare the means of two samples when each observation in one sample can be paired with an observation in the other sample.

For example, suppose we want to know whether a certain study program significantly impacts student performance on a particular exam.

To test this, we have 20 students in a class take a pre-test. Then, we have each of the students participate in the study program for two weeks. Then, the students retake a post-test of similar difficulty.

We would write the hypotheses for this particular two sample t-test as follows:

• H 0 :  µ pre = µ post
• H A :  µ pre ≠ µ post

Example 4: One Proportion z-test in Excel

A  one proportion z-test  is used to compare an observed proportion to a theoretical one.

For example, suppose a phone company claims that 90% of its customers are satisfied with their service.

To test this claim, an independent researcher gathered a simple random sample of 200 customers and asked them if they are satisfied with their service.

• H 0 : p = 0.90
• H A : p ≠ 0.90

Example 5: Two Proportion z-test in Excel

A two proportion z-test is used to test for a difference between two population proportions.

For example, suppose a s uperintendent of a school district claims that the percentage of students who prefer chocolate milk over regular milk in school cafeterias is the same for school 1 and school 2.

To test this claim, an independent researcher obtains a simple random sample of 100 students from each school and surveys them about their preferences.

• H 0 : p 1 = p 2
• H A : p 1  ≠ p 2

How to Change Axis Scales in Google Sheets Plots

Statistics vs. analytics: what’s the difference, related posts, how to create a stem-and-leaf plot in spss, how to create a correlation matrix in spss, how to convert date of birth to age..., excel: how to highlight entire row based on..., how to add target line to graph in..., excel: how to use if function with negative..., excel: how to use if function with text..., excel: how to use greater than or equal..., excel: how to use if function with multiple..., pandas: how to rename only the last column....

• Skip to secondary menu
• Skip to main content
• Skip to primary sidebar

Statistics By Jim

Making statistics intuitive

How t-Tests Work: t-Values, t-Distributions, and Probabilities

By Jim Frost 12 Comments

T-tests are statistical hypothesis tests that you use to analyze one or two sample means. Depending on the t-test that you use, you can compare a sample mean to a hypothesized value, the means of two independent samples, or the difference between paired samples. In this post, I show you how t-tests use t-values and t-distributions to calculate probabilities and test hypotheses.

As usual, I’ll provide clear explanations of t-values and t-distributions using concepts and graphs rather than formulas! If you need a primer on the basics, read my hypothesis testing overview .

What Are t-Values?

The term “t-test” refers to the fact that these hypothesis tests use t-values to evaluate your sample data. T-values are a type of test statistic. Hypothesis tests use the test statistic that is calculated from your sample to compare your sample to the null hypothesis. If the test statistic is extreme enough, this indicates that your data are so incompatible with the null hypothesis that you can reject the null. Learn more about Test Statistics .

Don’t worry. I find these technical definitions of statistical terms are easier to explain with graphs, and we’ll get to that!

When you analyze your data with any t-test, the procedure reduces your entire sample to a single value, the t-value. These calculations factor in your sample size and the variation in your data. Then, the t-test compares your sample means(s) to the null hypothesis condition in the following manner:

• If the sample data equals the null hypothesis precisely, the t-test produces a t-value of 0.
• As the sample data become progressively dissimilar from the null hypothesis, the absolute value of the t-value increases.

Read the companion post where I explain how t-tests calculate t-values .

The tricky thing about t-values is that they are a unitless statistic, which makes them difficult to interpret on their own. Imagine that we performed a t-test, and it produced a t-value of 2. What does this t-value mean exactly? We know that the sample mean doesn’t equal the null hypothesis value because this t-value doesn’t equal zero. However, we don’t know how exceptional our value is if the null hypothesis is correct.

To be able to interpret individual t-values, we have to place them in a larger context. T-distributions provide this broader context so we can determine the unusualness of an individual t-value.

What Are t-Distributions?

A single t-test produces a single t-value. Now, imagine the following process. First, let’s assume that the null hypothesis is true for the population. Now, suppose we repeat our study many times by drawing many random samples of the same size from this population. Next, we perform t-tests on all of the samples and plot the distribution of the t-values. This distribution is known as a sampling distribution, which is a type of probability distribution.

Related posts : Sampling Distributions and Understanding Probability Distributions

If we follow this procedure, we produce a graph that displays the distribution of t-values that we obtain from a population where the null hypothesis is true. We use sampling distributions to calculate probabilities for how unusual our sample statistic is if the null hypothesis is true.

Luckily, we don’t need to go through the hassle of collecting numerous random samples to create this graph! Statisticians understand the properties of t-distributions so we can estimate the sampling distribution using the t-distribution and our sample size.

The degrees of freedom (DF) for the statistical design define the t-distribution for a particular study. The DF are closely related to the sample size. For t-tests, there is a different t-distribution for each sample size.

Related posts : Degrees of Freedom in Statistics and T Distribution: Definition and Uses .

Use the t-Distribution to Compare Your Sample Results to the Null Hypothesis

T-distributions assume that the null hypothesis is correct for the population from which you draw your random samples. To evaluate how compatible your sample data are with the null hypothesis, place your study’s t-value in the t-distribution and determine how unusual it is.

The sampling distribution below displays a t-distribution with 20 degrees of freedom, which equates to a sample size of 21 for a 1-sample t-test. The t-distribution centers on zero because it assumes that the null hypothesis is true. When the null is true, your study is most likely to obtain a t-value near zero and less liable to produce t-values further from zero in either direction.

On the graph, I’ve displayed the t-value of 2 from our hypothetical study to see how our sample data compares to the null hypothesis. Under the assumption that the null is true, the t-distribution indicates that our t-value is not the most likely value. However, there still appears to be a realistic chance of observing t-values from -2 to +2.

We know that our t-value of 2 is rare when the null hypothesis is true. How rare is it exactly? Our final goal is to evaluate whether our sample t-value is so rare that it justifies rejecting the null hypothesis for the entire population based on our sample data. To proceed, we need to quantify the probability of observing our t-value.

Related post : What are Critical Values?

t-Tests Use t-Values and t-Distributions to Calculate Probabilities

Hypothesis tests work by taking the observed test statistic from a sample and using the sampling distribution to calculate the probability of obtaining that test statistic if the null hypothesis is correct. In the context of how t-tests work, you assess the likelihood of a t-value using the t-distribution. If a t-value is sufficiently improbable when the null hypothesis is true, you can reject the null hypothesis.

I have two crucial points to explain before we calculate the probability linked to our t-value of 2.

Because I’m showing the results of a two-tailed test, we’ll use the t-values of +2 and -2. Two-tailed tests allow you to assess whether the sample mean is greater than or less than the target value in a 1-sample t-test. A one-tailed hypothesis test can only determine statistical significance for one or the other.

Additionally, it is possible to calculate a probability only for a range of t-values. On a probability distribution plot, probabilities are represented by the shaded area under a distribution curve. Without a range of values, there is no area under the curve and, hence, no probability.

Related posts : One-Tailed and Two-Tailed Tests Explained and T-Distribution Table of Critical Values

t-Test Results for Our Hypothetical Study

Considering these points, the graph below finds the probability associated with t-values less than -2 and greater than +2 using the area under the curve. This graph is specific to our t-test design (1-sample t-test with N = 21).

The probability distribution plot indicates that each of the two shaded regions has a probability of 0.02963—for a total of 0.05926. This graph shows that t-values fall within these areas almost 6% of the time when the null hypothesis is true.

There is a chance that you’ve heard of this type of probability before—it’s the P value! While the likelihood of t-values falling within these regions seems small, it’s not quite unlikely enough to justify rejecting the null under the standard significance level of 0.05.

Learn how to interpret the P value correctly and avoid a common mistake!

Related posts : How to Find the P value: Process and Calculations and Types of Errors in Hypothesis Testing

t-Distributions and Sample Size

The sample size for a t-test determines the degrees of freedom (DF) for that test, which specifies the t-distribution. The overall effect is that as the sample size decreases, the tails of the t-distribution become thicker. Thicker tails indicate that t-values are more likely to be far from zero even when the null hypothesis is correct. The changing shapes are how t-distributions factor in the greater uncertainty when you have a smaller sample.

You can see this effect in the probability distribution plot below that displays t-distributions for 5 and 30 DF.

Sample means from smaller samples tend to be less precise. In other words, with a smaller sample, it’s less surprising to have an extreme t-value, which affects the probabilities and p-values. A t-value of 2 has a P value of 10.2% and 5.4% for 5 and 30 DF, respectively. Use larger samples!

Click here for step-by-step instructions for how to do t-tests in Excel !

If you like this approach and want to learn about other hypothesis tests, read my posts about:

• How the F-test Works in ANOVA .
• How Chi-Squared Tests of Independence Work

To see an alternative to traditional hypothesis testing that does not use probability distributions and test statistics, learn about bootstrapping in statistics !

Share this:

Reader Interactions

May 25, 2021 at 10:42 pm

what statistical tools, is recommended for measuring the level of satisfaction

May 26, 2021 at 3:55 pm

Hi McKienze,

The correct analysis depends on the nature of the data you have and what you want to learn. You don’t provide enough information to be able to answer the question. However, read my hypothesis testing overview to learn about the options.

August 23, 2020 at 1:33 am

Hi Jim, I want to ask about standardizing data before the t test.. For example I have USD prices of a big Mac across the world and this varies by quite a bit. Doing the t-test here would be misleading since some countries would have a higher mean… Should the approach be standardizing all the usd values? Or perhaps even local values?

August 24, 2020 at 12:37 am

Yes, that makes complete sense. I don’t know what method is best. If you can find a common scale to use for all prices, I’d do that. You’re basically using a data transformation before analysis, which is totally acceptable when you have a good reason.

April 3, 2020 at 4:47 am

Hey Jim. Your blog is one of the only few ones where everything is explained in a simple and well structured manner, in a way that both an absolute beginner and a geek can equally benefit from your writing. Both this article as well as your article on one tailed and two tailed hypothesis tests have been super helpful. Thank you for this post

March 6, 2020 at 11:04 am

Thank you, Jim, for sharing your knowledge with us.

I have a 2 part question. I am testing the difference in walking distance within a busy environment compared with a simple environment. I am also testing walking time within the 2 environments. I am using the same individuals for both scenarios. I was planning to do a paired ttest for distance difference between busy and simple environments and a 2nd paired ttest for time difference between the environments.

My question(s) for you is: 1. Do you feel that a paired ttest is the best choice for these? 2. Do you feel that, because there are 2 tests, I should do a bonferroni correction or do you believe that because the data is completely different (distance as opposed to time), it is okay not to do a multiple comparison test?

August 13, 2019 at 12:43 pm

thank you very eye opening on the use of two or one tailed test

April 19, 2019 at 3:49 pm

Hi Mr. Frost,

Thanks for the breakdown. I have a question … if I wanted to run a test to show that the medical professionals could use more training with data set consisting of questions which in your opinion would be my best route?

January 14, 2019 at 2:22 pm

Hello Jim, I find this statement in this excellent write up contradicting : 1)This graph shows that t-values fall within these areas almost 6% of the time when the null hypothesis is true I mean if this is true the t-value =0 hypothesis is rejected.

January 14, 2019 at 2:51 pm

I can see how that statement sounds contradictory, but I can assure that it is quite accurate. It’s often forgotten but the underlying assumption for the calculations surrounding hypothesis testing, significance levels, and p-values is that the null hypothesis is true.

So, the probabilities shown in the graph that you refer to are based on the assumption that the null hypothesis is true. Further, t-values for this study design have a 6% chance of falling in those critical areas assuming the null is true (a false positive).

Significance levels are defined as the maximum acceptable probability of a false positive. Usually, we set that as 5%. In the example, there’s a large probability of a false positive (6%), so we fail to reject the null hypothesis. In other words, we fail to reject the null because false positives will happen too frequently–where the significance level defines the cutoff point for too frequently.

Keep in mind that when you have statistically significant results, you’re really saying that the results you obtained are improbable enough assuming that the null is true that you can reject the notion that the null is true. But, the math and probabilities are all based on the assumption that the null is true because you need to determine how unlikely your results are under the null hypothesis.

Even the p-value is defined in terms of assuming the null hypothesis is true. You can read about that in my post about interpreting p-values correctly .

I hope this clarifies things!

November 9, 2018 at 2:36 am

Jim …I was involved in in a free SAT/ACT tutoring program that I need to analyze for effectiveness .

I have pre test scores of a number of students and the post test scores after they were tutored (treatment ).

Glenn dowell

November 9, 2018 at 9:05 am

It sounds like you need to perform a paired t-test assuming.

Comments and Questions Cancel reply

How to Create a t-Distribution Graph in Excel

A t-distribution is a type of continuous probability distribution. It has the following properties:

• It is continuous
• It is bell-shaped
• It is symmetric around zero
• It is defined by one parameter: the number of degrees of freedom
• The t-distribution converges to the standard normal distribution as the number of degrees of freedom converges to infinity

The t-distribution is often used in various hypothesis tests when sample sizes are small (n < 30) in place of the normal distribution .

Related:   How to Make a Bell Curve in Excel

Often we are interested in visualizing the t-distribution. Fortunately, it’s easy to create a t-distribution graph in Excel by using the  T.DIST()  function which uses the following syntax:

T.DIST(x, deg_freedom, cumulative)

• x:  the value for the random variable in the t-distribution
• deg_freedom: an integer that indicates the number of degrees of freedom in the t-distribution
• cumulative:  when set to TRUE, it returns the value for the cumulative density function; when set to FALSE, it returns the value for the probability density function

Next, we’ll show how to create the following t-distribution graph in Excel:

To create a t-distribution graph in Excel, we can perform the following steps:

1. Enter the number of degrees of freedom (df) in cell A2. In this case, we will use 12.

2. Create a column for the range of values for the random variable in the t-distribution . In this case, we will create a range of values from -4 to 4 by increments of 2 in cells B2 through B42.

3. Create a column for the pdf of the t-distribution associated with the random values.  In cell C2, type the formula  T.DIST(B2, $A$2, FALSE) . Then hover over the bottom right of cell C2 until the  +  sign appears. Click and drag down to autofill the values for cells C2 through C42.

4. Create the graph.  Highlight the two columns (B2:C42). Click the INSERT tab. In the Charts area, click  scatter with smooth lines . The following chart will appear:

5. Change the graph appearance.  By default, the y-axis appears in the middle of the graph and the gridlines show up in the background. We can change this by using the following steps:

• Right click on the x-axis. Click  Format Axis . Under  Vertical axis crosses , click  Axis Value  and type in  -5 .

• Click inside the chart. A  +  sign will appear in the top right corner. Click it to remove the gridlines (if you’d like) and add axes titles. In this example, we choose to label the x-axis as  t, labelthe y-axis as  f(t) , and remove the title entirely. The picture below shows the end result:

How to Create Several t-Distribution Graphs in Excel

We can also display several t-distribution curves in one graph if we’d like. This can be useful if we want to see how the shape of the t-distribution changes for various values for the degrees of freedom.

In order to display several t-distribution curves, we simply need to add three new columns for a t-distribution with a different value for the degrees of freedom. For example, we can create t-distribution curves for degrees of freedom = 6 and degrees of freedom = 60:

To create the t-distribution curve for df = 60, we can use the exact same steps we used before. To add a curve for df = 6, we can perform the following steps:

• Right click inside the chart. Click  Select Data . 
• Under  Legend Entries (Series) , click  Edit . 
• Choose the cells for the  X Values  and  Y Values  that contain the values in columns F and G. Then click  OK . The following curve will be added to the chart:

You’ll notice a pattern for t-distribution graphs:

• The higher the degrees of freedom, the more narrow the t-distribution curve will be. That is, it will have a higher peak.
• Conversely, the lower the degrees of freedom, the more flattened out the curve will be and the “fatter” the tails of the graph will be.
• As the degrees of freedom approaches infinity, the curve will converge to the standard normal distribution curve.

Modifying the Aesthetics of the Graph

Note that you can also modify the aesthetics of the graph by changing the following features:

• Modify the size and color of the title
• Modify the size and color of the axes labels
• Choose whether or not to display gridlines in the background
• Modify the background color of the graph
• Modify the color of the curve itself
• Choose whether or not to display the tick marks along the axes

Depending on how you would like the graph to appear, Excel gives you the ability to modify the chart quite a bit.

Find more Excel tutorials on Statology here .

Featured Posts

Hey there. My name is Zach Bobbitt. I have a Masters of Science degree in Applied Statistics and I’ve worked on machine learning algorithms for professional businesses in both healthcare and retail. I’m passionate about statistics, machine learning, and data visualization and I created Statology to be a resource for both students and teachers alike.  My goal with this site is to help you learn statistics through using simple terms, plenty of real-world examples, and helpful illustrations.

2 Replies to “How to Create a t-Distribution Graph in Excel”

I found this entry extremely helpful. Thank you. I’d like to use this Excel t-distribution to teach confidence intervals. I’ve been trying to figure out how to superimpose two dynamic vertical lines for the lower and upper limits of the interval estimates but can’t quite figure how to get Excel to do this. Any insights would be greatly appreciated.

Hi Brad…To superimpose two dynamic vertical lines on a t-distribution chart in Excel for teaching confidence intervals, you can use the following steps:

1. **Prepare Your Data:** – Ensure you have your t-distribution data and the corresponding x-values (degrees of freedom) in two columns. – Identify or calculate your lower and upper confidence interval limits based on your sample data.

2. **Create the T-Distribution Chart:** – Select your t-distribution data. – Go to the “Insert” tab and select a scatter plot (with smooth lines) to create the t-distribution chart.

3. **Add Dynamic Vertical Lines:** – Add two additional columns next to your t-distribution data for the lower and upper confidence limits. Let’s assume the t-distribution x-values are in column A and the y-values are in column B, and the lower and upper limits are in cells D1 and E1, respectively.

| A | B | C | D | |———|———|————|————–| | X-values| Y-values| Lower Limit| Upper Limit | | … | … | D1 | E1 |

4. **Create Data for Vertical Lines:** – In column C, add the x-values of your lower limit. In column D, add the x-values of your upper limit. For simplicity, let’s assume you want to use the same range of y-values for the vertical lines.

| A | B | C | D | |———|———|————|————–| | X-values| Y-values| Lower Limit| Upper Limit | | … | … | =D$1 | =E$1 |

5. **Plot Vertical Lines:** – Select the chart, and go to the “Chart Tools” in the “Design” tab. – Click on “Select Data.” – Add a new series for the lower limit: – Name the series “Lower Limit.” – Set the X values to the column with the lower limit (e.g., C). – Set the Y values to the range of y-values you want to plot the vertical line. – Add a new series for the upper limit: – Name the series “Upper Limit.” – Set the X values to the column with the upper limit (e.g., D). – Set the Y values to the range of y-values you want to plot the vertical line.

6. **Format the Vertical Lines:** – Click on each series for the vertical lines and format them to be lines instead of scatter points. – Right-click the series, select “Change Series Chart Type,” and choose a line chart for these series.

7. **Make the Lines Dynamic:** – The lines will now be dynamic and adjust according to the values in cells D1 and E1. You can link these cells to your confidence interval calculations, making the vertical lines automatically adjust when the confidence interval limits change.

Here is a step-by-step example in Excel to visualize it:

1. **Enter the Data:** – Assume column A has x-values, column B has y-values of your t-distribution. – Cell D1 has the lower limit (e.g., -1.96), and cell E1 has the upper limit (e.g., 1.96).

2. **Enter Values for Vertical Lines:** – In column C, enter `=D$1` for each corresponding y-value row. – In column D, enter `=E$1` for each corresponding y-value row.

3. **Select Data for Chart:** – Highlight columns A and B to create the initial t-distribution chart. – Add series for the lower and upper limits by selecting columns C and D for the x-values, and your chosen y-values range for the y-values.

4. **Adjust Chart Type:** – Change the series chart type for the lower and upper limits to lines.

This approach ensures that the vertical lines representing the confidence interval limits adjust dynamically based on the values you input, making it a powerful teaching tool for demonstrating confidence intervals with the t-distribution.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Join the Statology Community

Sign up to receive Statology's exclusive study resource: 100 practice problems with step-by-step solutions. Plus, get our latest insights, tutorials, and data analysis tips straight to your inbox!

By subscribing you accept Statology's Privacy Policy.