How to Use ANOVA in Excel: 4 Simple Steps

Note: This tutorial on ANOVA in Excel is suitable for all Excel versions including Office 365. 

If you are wondering how to do ANOVA in Excel, you have come to the right place. In this guide, I’ll explain ANOVA from scratch. We will get into it without jargon and I will show you how you can easily do this in Excel. 

You’ll learn:

1. What is ANOVA?
2. How to Do an ANOVA in Excel? (Single Factor ANOVA
   • Step 1: Install Data Analysis Toolpak in Excel
   • Step 2: Get Your Data and Hypothesis Ready for ANOVA 
   • Step 3: Run the ANOVA Excel Data Analysis Tool
   • Step 4: Interpret the ANOVA Results
3. How to Use the Two Factor ANOVA Excel Tool?
   • Step 1: Install Data Analysis Toolpak in Excel
   • Step 2: Get Your Data and Hypothesis Ready for Two Factor ANOVA 
   • Step 3: Run the Two Factor ANOVA Excel Data Analysis Tool
   • Step 4: Interpret the Two Factor ANOVA Results

Related:

How to Use the Chi Square Test Excel Function? 4 Easy Steps

The Excel SMALL Function – 3 Best Examples

How to Use e in Excel Using the EXP Function? 2 Easy Examples

What Is ANOVA?

Analysis of variance (ANOVA) is a statistical analysis that checks if the means of two or more categories are significantly different from each other. Use the ANOVA test to see how a categorical variable affects the sample’s mean. Simply put, ANOVA tests the impact of factors by comparing their corresponding sample means. 

You’ll understand this better with the help of this scenario. Let’s say, for example, you have collected a set of salary data of some employees and their corresponding levels of education (undergraduate, postgraduate, doctorate). Now, you want to find out whether a person’s level of education has any effect on his/her salary. 

You can easily find this out using ANOVA. Here, ANOVA will compare the mean salaries of people in all three groups and check whether they are significantly different. If they are significantly different, that means the level of qualification does have an impact on salaries. If the means are not significantly different, the level of qualification does not have a significant impact on the salaries. In the next section, I’ll illustrate how to use both the single factor and the two factor ANOVA in Excel.

How to Do an ANOVA in Excel? (Single Factor ANOVA)

Using ANOVA in Excel is very straightforward, because of the data analysis Toolpak. In this section, I’ll explain how to run the single factor ANOVA tool in Excel. Use this only if you have one independent factor in your data set (i.e the effect of educational attainment on employee salary). All you have to do is follow these simple steps:

Step 1: Install Data Analysis Toolpak in Excel

1. Open the Excel Options window by clicking on File > Options or using the shortcuts Alt + T + O or Alt + F + T.

2. Under the Excel Options windows switch to the Add-ins tab and select Analysis Toolpak under the Inactive Applications Add-ins section. 

3. In the Add-ins pop-up window which appears next, tick all the add-ins options and click OK. 

4. Once the installation is complete, the Solver and Data Analysis options will appear in the Data tab of the Excel ribbon. 

Step 2: Get Your Data and Hypothesis Ready for ANOVA 

1. As mentioned earlier, you should have at least one categorical variable for using ANOVA. The categorical variable signifies the independent variable (factor). Independent variables are those that may or may not have a significant effect on the dependent variable. In our example, levels of education (undergraduate, postgraduate, and doctorate) is the categorical variable. 

2. The data should also contain the values of their corresponding continuous dependent variable. This is the variable that you suspect might be affected by the independent variables. In this example, salary is the dependent variable. 

3. In short, your data set should look something like this. 

4. Before using the ANOVA Excel data analysis tool, state the null and alternative hypotheses clearly, somewhere in the spreadsheet. This is only for your clarity. 

The general pattern is:

Null Hypothesis (H₀): All sample means are not different. (𝛍₁= 𝛍₂= 𝛍₃)

Alternate  Hypothesis (H₁): At Least one sample mean is significantly different.

In this example: 

Null Hypothesis(H₀): Salary doesn’t vary based on the level of education (𝛍₁= 𝛍₂= 𝛍₃)

Alternate  Hypothesis (H₁): Salary varies based on the level of education. 

Step 3: Run the ANOVA Excel Data Analysis Tool

1. Go to the Data tab and click on the Data Analysis option in the Analyze group. 

2. In the Data Analysis pop-up window, choose Anova: Single Factor and click OK. 

3. In the Anova: Single Factor window, select the Input data range and a suitable output range. Also, set the Grouped by option to ‘Columns’, tick the Labels in the first-row checkbox and click OK. 

Step 4: Interpret the ANOVA Results

1. The ANOVA table will now appear in your chosen output range. 

2. In the ANOVA results table, if the F value is greater than Fcritical, reject the Null hypothesis (H₀). 

3. On the other hand, if the F value is smaller than Fcritical, accept the Null hypothesis (H₀). 

In this example, since the F (6.02) > F_critical (3.4), we can reject the null hypothesis and safely conclude that the employees’ salary varies based on their level of education.  However, it won’t tell you anything about where the variance is actually arising from. 

That is, it will not indicate which one amongst postgraduate or doctoral degrees actually helps increase the salary of the employees. You would have to use a T-test to find that out. 

Also Read:

How to Delete a Pivot Table in Excel? 4 Best Methods

How to Indent in Excel? 3 Easy Methods

How to Use the Format Painter Excel Feature? — 3 Bonus Tips

How to Use the Two Factor ANOVA Excel Tool?

Use the two factor ANOVA Excel data analysis tool if you have more than one independent factor that might affect your results (example: the effect of age group and level educational attainment on employees’ salary) 

Step 1: Install Data Analysis Toolpak in Excel

To run the two factor ANOVA in Excel, install the data analysis Toolpak if not already done. Please follow the same instructions provided in the previous section. 

Step 2: Get Your Data and Hypothesis Ready for Two Factor ANOVA 

1. You should have at least two categorical variables for using the two factor ANOVA Excel tool. The categorical variables signify the independent variables (factors). In this example, levels of education (undergraduate, postgraduate, and doctorate) and age group (junior, middle, senior) are the two categorical variables. 

2. The data should also contain the values of their corresponding continuous dependent variable. In our example, salary is the dependent variable. 

3.  In short, your data set should look something like this. 

4. Before using the two factor ANOVA Excel data analysis tool, state the hypotheses clearly, somewhere in the spreadsheet. This is for your convenience and understanding. Unlike single factor ANOVA, we have three hypotheses for two-factor ANOVA. 

The general pattern is:

Hypothesis 1 (H₁): All sample means are not different for Factor 1. (𝛍₁= 𝛍₂= 𝛍₃)

Hypothesis 2 (H₂): All sample means are not different for Factor 2. (𝛍₁= 𝛍₂= 𝛍₃)

Hypothesis 3 (H₃): There is no interaction between the factors

In this example: 

Hypothesis 1 (H₁): Salary doesn’t vary based on the level of education. (𝛍₁= 𝛍₂= 𝛍₃)

Hypothesis 2 (H₂): Salary doesn’t vary based on the age group. (𝛍₁= 𝛍₂= 𝛍₃)

Hypothesis 3 (H₃): There is no interaction between level of education and age group. 

Step 3: Run the Two Factor ANOVA Excel Data Analysis Tool

1. Go to the Data tab and click on the Data Analysis option in the Analyze group. 

2. In the Data Analysis pop-up window, choose Anova: Two-Factor with replication and click OK. 

3. In the Anova: two-factor window, select the Input data range and a suitable output range. Also, input the number of rows per sample. This is nothing but the number of samples in each group. In this example, since there are three sample salaries for each educational qualification, the rows per sample is 5. Finally, click OK. 

Step 4: Interpret the Two Factor ANOVA Results

1. The ANOVA table will now appear in your chosen output range. This table will display the F-values for each factor and the F-value for interaction between the factors. 

2. In the ANOVA results table, if the F value is greater than Fcritical for factor 1, reject the hypothesis (H₁). On the other hand, if the F value is smaller than Fcritical, accept the hypothesis (H₁). 

3. Similarly, if the F value is greater than Fcritical for factor 2, reject the hypothesis (H₂). On the other hand, if the F value is smaller than Fcritical, accept the hypothesis (H₂). 

4. Similarly, if the F value is greater than Fcritical for the interaction effect, reject the hypothesis (H₃). On the other hand, if the F value is smaller than Fcritical, accept the hypothesis (H₃).

In this example, since the F (9.75) > F_critical (3.55) for factor 1 (level of education) we can reject hypothesis H₁ and conclude that the employees’ salary varies based on their level of education. 

Similarly, since the F (29.13) > F_critical (3.55) for factor 2 (age group), we can reject hypothesis H₂ and conclude that the employees’ salary also varies based on their age.

However, since the F (0.337) < F_critical (2.92) for the interaction effect, we can accept hypothesis H₃ and conclude that there is no significant interaction between the factors ( education and age).

FAQs

Suggested Reads:

How to Make a Pareto Chart Excel Dashboard? 4 Easy Steps

How to Make a Sankey Diagram Excel Dashboard? A Step-by-Step Guide

How to Use the NPER Excel Formula: 2 Easy Examples

Closing Thoughts

In this guide, I have explained how to calculate ANOVA in Excel in a step-by-step manner. I have included detailed illustrations and examples to help you understand the concept better.

Keep checking this blog for more information and updates in the future. 

If you have any questions about this or any other Excel feature, please feel free to ask in the comments section. 

Want more high-quality guides for Excel? Check out our free Excel resources centre. 

Click here to access in-depth Excel training courses and master in-demand advanced Excel skills. 

Simon Sez IT has been teaching critical IT software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses by seasoned professionals.

Adam Lacey

Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats.When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.