This article highlights 2 ways to calculate the significant difference between two means in Excel. Calculating the significant difference between two means may become necessary on many occasions. For example, to determine which team performs better when two teams follow two different methods to perform the same task. Or, to determine if the increase in the weight of fruits of a tree for using chemical fertilizers is significant. It will be very tedious to do this manually.

The following picture highlights the purpose of this article. Have a quick look through it to do that.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from the download button below.

## What Is the Significant Difference and T-Test?

**Significant Difference:**

**The significant difference**simply means if two sets of data are significantly different. Because, in statistics, we consider that there is a 5% probability of this happening by chance. If this is not the case then the datasets are different. A Null Hypothesis assumes that two means are the same. Now, a p-Value (probability) of less than

**0.05**rejects this hypothesis. This way, we can conclude that the two sets of data or means are different and not the same.

**T-Test:**T-Test is a term from statistics that allows us to do a comparison of two data populations and their means. The test is used to see if the two sets of data are significantly different from one another. A Null Hypothesis is used to test for the significant difference. In addition, the datasets usually follow the normal distribution curve. But, the variances are unknown and assumed to be equal.

William Sealy Gosset devised the T-Test in around 1908 while working on the Guinness Brewery in Ireland. He used the T-Test to monitor the quality of the stout brewed by Guinness. A T-Test analysis could be used to test the results from two different portfolios that are managed under two different investment strategies. In this analysis, a Null Hypothesis can be created, for instance, where the means of the returns for the two portfolios do not differ. The test then looks at the T-Statistic and T-Distribution to determine a p-Value (probability) that can be used to either validate or refute the Null Hypothesis. The T-Test is one of several different types of statistical tests used for hypothesis testing. Others include An Analysis of Chi-Square Test, F-Test, Variance Test, and Z-Test. The T-Test is considered a more conservative approach and better suited for smaller groups of data.

## 2 Ways to Calculate Significant Difference Between Two Means in Excel

Imagine you have an orange orchard with 10 trees. The soil has enough natural fertilizer in it. Now you want to find out if using chemical fertilizer will increase the weight of the oranges. Then, measure the average weights of the oranges from each of the trees. This time do not apply any chemical fertilizers. Then, do the same in the next season. But, this time, apply some fertilizers to those trees. Suppose, you get the following result.

Now, it seems that using fertilizers has increased the average weight of the oranges. But, you canâ€™t be sure if this was just by chance without calculating the significant difference. Follow the methods below to do that.

### 1. Calculate Significant Difference with T.TEST Function

You can easily calculate the significant difference between two means using the **T.TEST function** in excel.

Just enter the following formula in cell **D16**. Then, you will get the following result.

`=T.TEST(C5:C14,D5:D14,2,1)`

The formula returns a p-value of less than 0.05. So, the Null Hypothesis is rejected. Therefore, we can say that using chemical fertilizers has increased the weight of the oranges.

**ðŸ”ŽÂ How Does the Formula Work?**

The **T.TEST function** has four arguments. You need to enter them properly to avoid faulty results.

**Syntax:**

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

**Arguments:**

**array1: **Required. Refers to the first range of data.

**array2:** Required. Refers to the second range of data.

**tails:** Required. Enter **1** for **One-tailed distribution** and **2** for **Two-tailed distribution**. One-tailed distribution allows for effect in one particular direction, either positive or negative, good or bad while two-tailed distribution allows both.

**type:** Required. Enter **1** for **Paired**, **2** for **Two-sample equal variance (homoscedastic),** and **3** for **Two-sample unequal variance (heteroscedastic)**. Paired type indicates that the datasets are from two dependent or related or same sources. Use **Two-sample equal variance** for two independent datasets with equal variances and **Two-sample unequal variance** for unequal variances.

**Read More:** **Excel formula to find difference between two numbers**

### 2. Calculate the Significant Difference Using Data Analysis in Excel

You can also calculate the significant difference between two means in excel using the **Analysis Toolpak**. Follow the steps below to be able to do that.

ðŸ“Œ **Steps**

- First, select the
**Data**tab to see if your excel has the**Analysis Toolpak**installed. Is the**Data Analysis**feature visible? If yes then go to**step 4**.

- Otherwise, press
**ALT+F+T**to open the**Excel Options**window. Then, go to the**Add-ins**tab. Next, select**Go**as shown in the picture below.

- Next, check the checkbox for
**Analysis Toolpak**on the**Add-ins**window. Then select**OK**. After that, you will be able to access the**Data Analysis**feature from the**Data**tab.

- Now, select
**Data >> Data Analysis**. This will open the**Data Analysis**window. Next, choose the proper analysis tool from the list. Here, we will select**t-Test: Paired Two Sample for Means**for our dataset. Now select**OK**. This will take you to that analysis tool window.

- Now enter
**$C$4:$C$14**and**$D$4:$D$14**for**Variable 1 Range**and**Variable 2 Range**respectively using the upward arrows. - Next, check the checkbox for
**Labels**. You could keep this unchecked if you did not include the headers in the above step. - Now, enter
**0.05**in the textbox for**Alpha**. Then, mark the radio button for**Output Range**. Next, select the location where you want to get the results. Excel shows the results in a new worksheet by default. Then, select**OK**.

Finally, you will see the following result. The highlighted value indicates the same result obtained in the earlier method.

**Read More: ****How to Calculate Absolute Difference between Two Numbers in Excel**

## Things to Remember

- Make sure you are using the proper arguments that correspond to your dataset while using the function. Otherwise, you might end up with erroneous results.
- Select the proper analysis tool based on your dataset while using the
**Data Analysis**window. - The
**TTEST function**is the earlier version of the**T.TEST function**.

## Conclusion

Now you know how to calculate the significant difference between two means in excel. Please let us know if this article has provided you with the desired solution to your problem. You can also use the comment section below for further queries or suggestions. Do visit our **ExcelDemy** blog to explore more about excel. Stay with us and keep learning.

## Related Readings

**Calculate Difference Between Two Rows in Pivot Table (with Easy Steps)****Positive or Negative Difference Between Two Numbers in Excel****Pivot Table: Percentage Difference between Two Columns****Excel Pivot Table: Difference between Two Columns (3 Cases)****How to Calculate Difference Between Two Dates in Days in Excel****Calculate Time Difference in Numbers (5 Easy Ways)**