How to Calculate Significant Difference Between Two Means in Excel

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.

What Is the Significant Difference and T-Test?

Significant Difference:
The significant difference simply means that 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. However, the variances are unknown and assumed to be equal.
William Sealy Gosset devised the T-Test in around 1908 while working at 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.

How 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.

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:

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 the same sources. Use Two-sample equal variance for two independent datasets with equal variances and Two-sample unequal variance for unequal variances.

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.

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.

Download Practice Workbook

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

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. Stay with us and keep learning.

<< Go Back to Statistical Significance in Excel | Excel for Statistics | Learn Excel