ANOVA table in Excel: how to create and interpret it
In the following article you will learn everything you need to know about an ANOVA table, its elements and its application with one and two factors.
Key information
To compare averages of different data groups we use an analysis of variance table: ANOVA (Analysis of Variance). This table contains the elements that analyze the variation of the data and helps us compare whether two or more groups are statistically different or not.
The basics of SEARCH in Excel
Purpose: The objective of an ANOVA table is to test whether two or more groups are statistically equal or different from each other.
Initial hypothesis: Also known as the ânull hypothesis.â It is the initial belief that we have about the groups we have. In statistics this hypothesis is that the averages of all groups will be statistically equal.
Test F: Test made by the ANOVA table to verify whether the initial hypothesis is met, that is, whether the averages of the groups are equal or not. If the value of our âF valueâ is greater than a critical value, we will say that the initial hypothesis is not met, that is, the averages of the groups are statistically different.
Install the data analysis package
Before we can start analyzing an ANOVA table in Excel, we must install the data analysis package to create an ANOVA table.
Step 1: In the toolbar, click on âFileâ and then on âOptionsâ (in the lower left corner).
Step 2: In the new options window, we must select âPluginsâ. In the âManageâ section, we select âExcel Add-insâ and then click âGoâ.
Step 3: A new window will appear, in which we must check âAnalysis Toolsâ and then click âOKâ.
If we go to the âDataâ tab on the toolbar, we will see that a new section called âAnalysisâ has been added, which includes the âData Analysisâ tool.
Ninja Tip: To install this package on Mac we must go to the toolbar, click on âToolsâ and then on âExcel Add-insâ. Then, a new window will appear, in which we must check âAnalysis Toolsâ and then click âOKâ. With this plugin you can do linear regressions, obtener correlation coefficients, do t tests and use many other data analysis tools.
One-way ANOVA
To understand what a one-factor ANOVA table is, we will see an applied example. In this case, we have a student's grades for three different subjects: Mathematics, History and Natural Sciences.
Our objective is to know if the average you have in each subject is statistically equal to the other subjects. That is, see if the average in Mathematics is the same as the average in History and Natural Sciences.
For this we must:
- We go to the âDataâ tab and click on âData Analysisâ.
- In the window that opens we must select âVariance Analysis of a factorâ.
- In âInput rangeâ we select our table with data. In this case it is $B$2:$D$6 and since we include the titles we must select the option âLabels in the first rowâ.
- In the âGrouped byâ section, we must choose the meaning of each group: since each group is a different column, we select âColumnsâ. This is because we want to compare one column with another.
- For the âAlphaâ value in general 5% is used, which means that it is estimated with a confidence of 95%. If we wanted to be more strict we can choose a lower value, such as 1% (to have a reliable 99%). You can delve deeper into this concept in this article.
- In âOutput rangeâ we must choose the cell where the variance analysis information will begin, that is, the upper left corner. In this case we will select $A$8. Finally we click on âAcceptâ.
Thus, we obtain as a result a summary table of observations, average and variance of each group and an ANOVA table.
Ninja Tip: As we can see, the results are part of the spreadsheet and will be overwritten over what was previously there. This is why it is important that we worry about having enough space on the spreadsheet so as not to lose data.
Now, when analyzing the ANOVA table our objective is to see if our initial hypothesis is met: if the F value is greater than the critical value of F, we are going to reject the hypothesis that the averages of the groups are equal. That is, if F is greater than the critical value, we are going to say at least one of the averages of these groups is statistically different.
To see this in the ANOVA table itself we go to the row that says âBetween groupsâ and see that the F value is 6.06, while the critical F value is 4.25. As this means that the averages in Mathematics, History and Natural Sciences are not all equal to each other, that is, at least one average is different.
Ninja Tip: The critical value for F will decrease as we have more data from each group. This means that the more data we have, the more certain we can be of a statistical difference.
However, an ANOVA type analysis does not tell us which group (or groups) is (are) the different one(s) or if it is greater or less than the rest. For this we must do a t-test to see each pair of groups if they are the same or different from each other.
Ninja Tip: We do not need to have the same amount of data for each group to compare averages with an ANOVA table. For example, in this case we select $B$2:$D$9, leaving those cells that do not have data blank.
Obtaining the following result:
Two-way ANOVA with one sample per group
A two-factor ANOVA table consists of two types of groups and we want to study if there are differences between groups for both types. This is better understood with an example: let's think about a course that has several students and several tests have been taken.
In this case we want to see if the averages of the students are statistically equal to each other, and in addition, we want to see if the averages of the tests are statistically equal to each other. This is why it is a two-factor ANOVA: we want to study the averages of the students and the tests.
In this way we follow steps similar to those for a one-way ANOVA table:
- We go to the âDataâ tab and click on âData Analysisâ.
- In the window that opens we must select âVariance Analysis of two factors with one sample per groupâ.
- In âInput rangeâ we select our table with data. In this case it is $B$2:$D$7 and since we include the titles we must select the âLabelsâ option.
- For the âAlphaâ value in general 5% is used, which means that it is estimated with a confidence of 95%.
- In âOutput rangeâ we must choose the cell where the variance analysis information will begin, that is, the upper left corner. In this case we will select $A$9. Finally we click on âAcceptâ.
Thus, we obtain as a result a summary table of observations, average and variance of each group of each type, that is, for each test for each student.
Now, when analyzing the two-factor ANOVA table we have two aspects that we want to study: the difference in averages between tests and between students. When viewing the âRowsâ section it is being tested between rows, that is, you are comparing whether the tests have the same averages or not. As we see, the F value is 1.12, it is less than the critical value of F (6.38). We see that the initial hypothesis is accepted in which the averages of the tests are statistically the same.
Then, for the âColumnsâ section, they are comparing between columns, that is, you are comparing the averages of JosĂ© TomĂĄs and CristĂłbal. In the ANOVA table we see that the F value is 60.80, which is much higher than the critical value for F, which is 7.70. In this way we reject that they have the same average statistically.
Two-way ANOVA with multiple samples per group
A two-factor ANOVA table with several samples per group, like the previous case, consists of two types of groups and we want to study if there are differences between groups for both types. However, in this case we have several data for each group. Let's look at an example: let's think that we have two schools and two subjects and that for each subject within a school we have several grades.
In this case we want to see if the averages of each school are statistically equal to each other, and in addition, we want to see if the averages of the subjects are statistically equal to each other. This is why it is a two-factor ANOVA: we want to study the averages of each school and each subject. Finally, since there are several samples by groups, we can study whether the âschool-subjectâ interactions are equal between them.
In this way we follow steps similar to those for a one-factor ANOVA table in Excel:
- We go to the âDataâ tab and click on âData Analysisâ.
- In the window that opens we must select âTwo-factor Variance Analysis with several samples per groupâ.
- In âInput rangeâ we select our table with data. In this case it is $B$2:$D$12, including the table titles.
- In âRow per sampleâ we must put how many observations we have for each group. In this case, we have 5 grades for each school, given a subject.
- For the âAlphaâ value in general 5% is used, which means that it is estimated with a confidence of 95%.
- In âOutput rangeâ we must choose the cell where the variance analysis information will begin, that is, the upper left corner. In this case we will select $F$12. Finally we click on âAcceptâ.
Finally, we obtain:
Thus, we obtain a summary table for School A, for School B and for both as a set, doing an analysis for each subject.
Then, in the ANOVA table we see that in the âSampleâ section columns are compared to each other, so it compares Mathematics and History and we see that we did not reject the initial hypothesis (0.20<4.49), so the averages are statistically equal. Regarding the âColumnsâ section, compare the average for School A with School B and we see that there is a difference between schools (7.73>4.49).
Finally, we see the interaction: is School A in Mathematics, School A in History, School B in Mathematics and School B in History the same? Seeing that the value of F (9.69) is greater than the critical value of F (9.69>4.49), we see that there is at least one group that is different.
Frequent questions
To compare averages of different data groups we use an analysis of variance table: ANOVA (Analysis of Variance). This table contains the elements that analyze the variation of the data and helps us compare whether two or more groups are statistically different or not.
To compare averages of different data groups we use an analysis of variance table: ANOVA (Analysis of Variance). This table contains the elements that analyze the variation of the data and helps us compare whether two or more groups are statistically different or not.