While working with Microsoft Excel, sometimes we need to find the significant differences. It simply proves that if no difference occurs, the outcomes produced have only a minimal possibility of occurring. This significant difference between two numbers is acquired from the t-test. After doing this test, we will be able to say if the difference between two numbers is significant or not. If the result is less than 0.05 only then we can say that the differences are significant. In this article, we will demonstrate how to find the significant difference between two numbers in Excel.
T-Test to Find Significant Difference
A t-test is a dataset that is used to see if there is a significant difference between two different groups that are connected in some way. The t-test is one of several tests used in statistics for hypothesis testing.
Three important data values are required to calculate a t-test.
Hypothesis tests that evaluate the values of one or two samples are known as t-tests. Based on the t-test and how it’s set up, it can tell you if:
- The means of the two groups differ.
- Paired means are not the same.
- A goal value is not the same as a mean.
A t-test is a dataset that is used to see if there is a mean difference between 2 categories that are connected in some way.
How to Find Significant Difference Between Two Numbers in Excel
To find the significant difference between the two numbers, we consider some students’ marks on their First Terminal and Last Terminal in nine different subjects. So, we put the Subject Code in column B, First Terminal marks for each subject and the Last Terminal marks for each subject. Now, we find the significant difference between the two terminal marks.
1. Find Significant Difference Between Two Numbers by Using ‘Data Analysis’ Tool in Excel
Systems and applications that gather and process data are known as Data Analysis tools. Microsoft Excel allows data analysts to generate customizable data aggregations, visualize data, compute percentages and other standard statistics, and more. We need to use the Data Analysis tool to do a t.test to find if the differences between two numbers are significant or not. For this, we need to follow the procedures down.
Step 1: Download and Install Data Analysis ToolPak
Firstly, to perform statistical tests, our edition of Excel should have the Data Analysis add-in downloaded. If we see the ‘Data Analysis’ option when we click on the ‘Data’ navigation pane, the add-in is already installed. We will have to download and install the add-in if we do not see the ‘Data Analysis’ tab. To do so, we need to follow the substeps below.
- To begin with, go to the top left corner of Excel and click on the File tab.
- Then, click on Option, which is the below left on the file.
- The Excel Options menu will appear. Now, click on the Add-ins option and pick Analysis Toolpak.
- After that, on the Manage drop-down menu bar, choose Excel Add-ins, and click Go.
- The Add-ins pop-up window will appear.
- Now, checkmark the Analysis ToolPak, under Add-ins available check box.
- Further, click on the OK button to enable the Data Analysis menu.
- As indicated above, the ‘Data Analysis’ menu should appear in the Data tab.
Note: In Excel for Mac, the Analysis ToolPak is no longer available. To do some different measures, you’ll need to download a third-party analytic tool.
Step 2: Evaluate Two-Sample T-Test Statistics
To evaluate two-sample t-test statistics we need the Data Analysis feature that we just launched in step 1.
- Firstly, go to the Data tab from the ribbon.
- Secondly, click on Data Analysis, under the Analysis group.
- This will open the Data Analysis dialog box.
- Now, scroll down to the dialog, and find the t-test options.
- Then, select t-test: Two-Sample Assuming Equal Variances.
- After that, click OK.
- Next, fill up the cells with your data.
- Pick the Variable 1 Range box in the Input option, then use the cursor to select the column holding the data for First Terminal. The range should display in the box when you choose cells in our worksheet.
- Repeat the procedure for Variable 2 Range and any other data column Last Terminal. We can adjust the output settings and alpha value if necessary, although the default alpha of 0.05 is generally sufficient.
- Select the Output options, New Worksheet Ply.
- Click OK. The results of our statistical test will now be visible.
- This will show the result of the t-test: Two-Sample Assuming Equal Variances in another worksheet.
Final Result of Significant Difference
You can see in cell B13, that the P(T<=t) two-tail is less than the alpha value which is 0.05. We may conclude that the means of our two trials differ by a statistically significant amount. So, we can say the difference is significant.
Note: T-tests are resilient to discrepancies among variances when both categories have an equal or approximately equivalent number of instances and standard portion size. If one group’s variance is double that of others, it’s reason to be concerned! Minor deviations, on the other hand, are unimportant.
Many experts recommend that you always use the t-test with uneven variances. Whenever the variances are similar, you gain a small bit of predictive significance, however, when the variances are not similar, you will be better off.
Use the uneven variances variant of the 2-sample t-test if you already have differences with regard and a different number of respondents.
2. Use Formula to Get Significant Difference Between Two Numbers in Excel
Excel formulas allow us to perform many calculations. Formulas can crib into multiple cells and even spreadsheets. As a result, if users wish to execute the same actions on many sets of data, they don’t have to rebuild the formula. We can apply Formulas to a pair of two integers or a group of data cells. Instead of using the Data Analysis tool, we can use a formula to do the t.test. This is the shortcut way for doing the t.test to find whether a difference between two numbers is significant or not.
Step 1: Select Range
For the formula, in the first place, we need to select the range of the data which is an array in the T.TEST function in excel.
- If we just care about the p-value, you can quickly compute it by typing the following syntax right into a cell.
=T.TEST(array1, array2,tails,type)- Here, The first set of data is referred to as array1 while the second set of data is referred to as array2.
Step 2: Choose Tailed Distribution
- After that, we have to choose the tailed distribution.
- The type pertains to whether you want to operate a one- or two-tailed test. In the instance at left, we input the second option, which implies a two-tailed test; it really would be 1 for a one-tailed test.
Step 3: Choose Type of T-Test
- Now, we have to select the t.test type. As showing the uses of each type of t.test in the first method, we will input the option.
- So, we select the number 2 option because it’s the Two-sample equal variance.
Final Result of Significant Difference
And, finally, if we click on the resulting cell, we will see the formula for the t.test in the formula bar.
=T.TEST(C5:C13,D5:D13,2,2)And the result of the first method matches this result. So we can easily say that the difference between the two numbers is significant.
Note: The t-test determines the likelihood that the difference in the two values is due to randomness. It is common practice to declare that if the probability is less than 0.05, the difference is significant,’ indicating that it is not due to chance.
Read More: How to Calculate Significant Difference Between Two Means in Excel
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will assist you to find a significant difference between two numbers in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.
<< Go Back to Statistical Significance in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Good day
This has been helpful.
However, I am not from a statistic background I am an agronomist and normally just outsource.
I would like to find out how I can then use this significant value in drawing my graphs.
For instance, the mean values are made clear by the ANOVA and I can easily pick them and draw my graph showing the various means from my treatments. Now I would like to put asterisks on graphs to show which one did better than the other and which did poorly.
Can I then just look at the largest mean then put an “a” asterisk on top of the bar, then subtract the TTest value and find which treatment (I have 4 treatments) mean is closest then denote it with a “b” and so forth until my treatments are all covered?
Please I have minimal background in stats.
Regard
Hello OLWETU,
I hope you are doing well. Well, thank you for your query. Adding asterisks to graphs is a significant way to show the difference between two or more groups. You can get the mean value of the treatments using the ANOVA method. Then plot the mean values in the graph. This part is relatively easy and already shown in the article. Now, if you want to add the asterisks to the chart to define the largest mean then select the chart and check the Data levels as below.
Therefore, write down “a” in a cell and double-click the value of the highest mean value. Once you click on the data levels you will see the options of selecting data levels. Finally, select Choose Cell to get the asterisks as “a”.
Now, apply the MIN function to get the minimum value of the mean values and inter asterisks as “b” using the same process.
