What is ANOVA in Excel?
ANOVA in Excel is a built-in statistical test that is used to analyze the variances. For example, when you buy a new item, we usually compare the available alternatives, which eventually helps us choose the best from all the available alternatives. Using the ANOVA test in excel can help us test the different data sets against each other to identify the best from the lot.
Assume you conducted a survey on three different flavors of ice creams, and you have collated opinions from users. Now you need to identify which flavor is best among opinions. Here we have three different flavors of ice creams, and these are called alternatives, so by running the ANOVA test in excel, we can identify the best from the lot.
Where is ANOVA in Excel?
ANOVA is not a function in excel. If you have already tried to search for ANOVA in excel, you must have failed miserably because ANOVA is part of the “Data Analysis” tool in excel.
Data Analysis is available under the DATA tab in excel.
If you are not able to view this in your excel, follow the below steps to enable “Data Analysis” in your excel workbook.
- Click on FILE and Options.
- Click on Add-Ins.
- Under Add-Ins, select “Excel Add-InsExcel Add-InsAdd-ins are different Excel extensions that can be found in the options section of the file tab. The first box displays the system's enabled add-ins, and if the user wishes to enable more, they must click on manage add-ins.” from manage options and click on Ok.
- Now, from the below window, select “Analysis ToolpakAnalysis ToolpakExcel's data analysis toolpak can be used by users to perform data analysis and other important calculations. It can be manually enabled from the addins section of the files tab by clicking on manage addins, and then checking analysis toolpak.” and click on OK to enable “Data Analysis.”
Now you should see “Data Analysis” under the “DATA” tab.
How to do ANOVA Test in Excel?
Following is the example to understand how to do an ANOVA test in excel.
For this example, consider the below data set of three students marks in 6 subjects.
Above are the scores of students A, B, and C in 6 subjects. Now we need to identify whether the scores of three students are significant or not.
- Step 1: Under the Data tab, click on “Data Analysis.”
- Step 2: In the “Data Analysis” window, select the first option, “Anova: Single Factor.”
- Step 3: In the next window for “Input Range,” select student scores.
- Step 4: Since we have selected the data with headers, check the box “Labels in First Row.”
- Step 5: Now select the output range as one of the cells in the same worksheet.
- Step 6: Click on Ok to complete the calculation. Now we will have detailed Anova: Single Factor analysis.
Before we interpret the results of ANOVA, let’s look at the hypothesis of ANOVA. To compare the results of the excel ANOVA test, we can frame two hypotheses, i.e., “Null Hypothesis” and “Alternative Hypothesis.”
The Null Hypothesis is “there is no difference between scores of three students.”
The alternative Hypothesis is “there is at least one of the mean is different.”
If “ F value > FCrit value,” then we can reject the null hypothesis.
If “F value < FCrit value,” then we cannot reject the null hypothesis.
If “P-value < Alpha value,” then we can reject the null hypothesis.
If “P-value > Alpha value,” then we cannot reject the null hypothesis.
No go back and look at the result of ANOVA.
First, look at the “P-Value,” i.e., 0.511, which is greater than alpha or significance value (0.05), so we cannot reject the null hypothesis.
Next, F value 0.70 is less than the FCrit value 3.68, so we cannot reject the null hypothesis.
So, we can conclude the test as “Scores of Students A, B, and C are not significantly different.”
Things to Remember
- You need strong ANOVA knowledge to understand things better.
- Always frame the Null Hypothesis and the Alternative Hypothesis.
- If the F value is > FCrit value,” then we can reject the null hypothesisNull HypothesisNull hypothesis presumes that the sampled data and the population data have no difference or in simple words, it presumes that the claim made by the person on the data or population is the absolute truth and is always right. So, even if a sample is taken from the population, the result received from the study of the sample will come the same as the assumption..
- “If F value is < FCrit value,” then we cannot reject the null hypothesis.
This has been a guide to ANOVA in Excel. Here we discuss how to do the ANOVA test in excel with the help of an example and downloadable excel sheet. You can learn more about excel from the following articles –