ANOVA in Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What is ANOVA in Excel?

ANOVA in Excel is a built-in statistical test used to analyze the variances. For example, we usually compare the available alternatives when buying a new item, which eventually helps us choose the best from all the available options. 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 the views. Here, we have three different flavors of ice creams. These are called alternatives, so we can locate the best from the lot by running the ANOVA test in Excel.

ANOVA in Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: ANOVA in Excel (wallstreetmojo.com)

Where is ANOVA in Excel?

ANOVA is not a function in Excel. However, if you have already tried to search for ANOVA in Excel, you may have failed because ANOVA is part of Excel’s “Data Analysis” tool.

Data Analysis is available under the “DATA” tab in Excel.

example 1.1

If you cannot view this in your Excel, follow the below steps to enable “Data Analysis” in your Excel workbook.

  1. Click on “FILE” and “Options”.


    Excel Anova Example 1.2

  2. Click on “Add-Ins.”


    Excel Anova Example 1.3

  3. Under “Add-Ins”, select “Excel Add-Ins” from the “Manage” options and click on “Ok.”


    Excel Anova Example 1.4

  4. Now, from the below window, select “Analysis Toolpak” and click on “OK” to enable “Data Analysis”.


    Excel Anova Example 1.5

You should see “Data Analysis” under the “DATA” tab.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to do ANOVA Test in Excel?

Following is an example of how to do an ANOVA test in Excel.

You can download this ANOVA Excel Template here – ANOVA Excel Template

For this example, consider the below data set of three students’ marks in 6 subjects.

Excel Anova Example 2.1

Above are students’ A, B, and C scores in 6 subjects. Now, we need to identify whether the scores of three students are significant or not.

  • Step 1: Click on “Data Analysis ” under the Data tab.”
excel anova example 2.2
  • Step 2: In the “Data Analysis” window, select the first option, “Anova: Single Factor.”
Excel Anova Example 2.3
  • Step 3: In the next window for “Input Range,” select student scores.
Excel Anova Example 2.4
  • Step 4: Since we have selected the data with headers, check the box “Labels in First Row.”
Excel Anova Example 2.5
  • Step 5: Now select the “Output Range” as one of the cells in the same worksheet.
Excel Anova Example 2.6
  • Step 6: Click on “OK” to complete the calculation. Now we will have a detailed “Anova: Single Factor” analysis.
Excel Anova Example 2.7

Before we interpret the results of ANOVA, let us look at the hypothesis of ANOVA. Then, to compare the Excel ANOVA test results, we can frame two hypotheses – the “Null hypothesis” and “Alternative hypothesis.”

The null hypothesis is “there is no difference between scores of three students.”

The alternative hypothesis is that “at least one of the mean is different.”

If “F value > F critical value,” we can reject the null hypothesis.

If “F value < F critical value,” we cannot reject the null hypothesis.

If “p-value < alpha value,” we can reject the null hypothesis.

If “p-value > alpha value,” we cannot reject the null hypothesis.

Note: alpha value is the significance level.

You may not go back and look at the result of ANOVA.

Excel Anova Example 2.8

First, look at the “p-value,” which is 0.511. Unfortunately, it is greater than the alpha or significance value (0.05), so we cannot reject the null hypothesis.

Next, the F value of 0.70 is less than the F critical value of 3.68, so we cannot reject the null hypothesis.

Next, the F value of 0.70 is less than the F critical value of 3.68, so we cannot reject the null hypothesis.

Things to Remember

Recommended Articles

This article 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 a downloadable Excel sheet. You can learn more about Excel from the following articles: –