How to Use ANOVA in Excel: 4 Simple Steps How to Use ANOVA in Excel : 4 elementary Steps

# 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 field place. In this guide, I ’ ll explain ANOVA from boodle. We will get into it without jargon and I will show you how you can easily do this in Excel .

You ’ ll determine :

**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 early. Use the ANOVA test to see how a categoric variable affects the sample ’ south intend. Simply put, ANOVA tests the impact of factors by comparing their correspond sample means .

ANOVA tests if the means of two or more populations are statistically different from each other You ’ ll understand this better with the help of this scenario. Let ’ s say, for case, you have collected a adjust of wage data of some employees and their match levels of education ( undergraduate, graduate, doctor’s degree ). immediately, you want to find out whether a person ’ mho level of department of education has any effect on his/her wage .

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 degree of qualification does have an impingement on salaries. If the means are not significantly unlike, 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 identical straightforward, because of the datum analysis Toolpak. In this section, I ’ ll explain how to run the single component ANOVA tool in Excel. Use this merely if you have one autonomous factor in your data arrange ( i.e the effect of educational attainment on employee wage ). All you have to do is follow these simple steps :

### Step 1: Install Data Analysis Toolpak in Excel

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

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

Install the Analysis ToolPak in the Add-ins section of Excel Options window to use ANOVA in Excel

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

Tick all the Checkboxes and Click OK

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

Excel Data Analysis Tool

### Step 2: Get Your Data and Hypothesis Ready for ANOVA

- 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.

- 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.

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

Input data for single factor ANOVA in Excel

- 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 cosmopolitan model is :

null Hypothesis ( H0 ) : All sample means are not different. ( 𝛍1= 𝛍2= 𝛍3 )

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

In this model :

null Hypothesis ( H0 ) : wage doesn ’ t deviate based on the horizontal surface of education ( 𝛍1= 𝛍2= 𝛍3 )

Alternate Hypothesis ( H1 ) : wage varies based on the degree of education .

### Step 3: Run the ANOVA Excel Data Analysis Tool

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

Click on the Data Analysis button

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

Choose the single factor ANOVA option and click OK

- 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.

Set the input and output ranges. Also, tick the Grouped by columns and Labels in the first row options

### Step 4: Interpret the ANOVA Results

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

- In the ANOVA results table, if the F value is greater than Fcritical, reject the Null hypothesis (H0).

- On the other hand, if the F value is smaller than Fcritical, accept the Null hypothesis (H0).

Compare the F-value against the F-critical value to interpret ANOVA in Excel In this model, since the F ( 6.02 ) > Fcritical ( 3.4 ), we can reject the null hypothesis and safely conclude that the employees ’ wage varies based on their level of education. however, it won ’ deoxythymidine monophosphate assure you anything about where the variability is actually arising from .

That is, it will not indicate which one amongst graduate student or doctoral degrees actually helps increase the wage 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

Read more : How to Lower Your Electric Bill – NerdWallet

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

## How to Use the Two Factor ANOVA Excel Tool?

Use the two divisor ANOVA Excel data analysis creature if you have more than one freelancer factor that might affect your results ( case : the effect of senesce group and degree educational attainment on employees ’ wage )

### Step 1: Install Data Analysis Toolpak in Excel

To run the two agent 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

- 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.

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

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

Input Data for Two Factor ANOVA in Excel

- 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 blueprint is :

guess 1 ( H1 ) : All sample distribution means are not different for Factor 1. ( 𝛍1= 𝛍2= 𝛍3 )

Hypothesis 2 ( H2 ) : All sample distribution means are not different for Factor 2. ( 𝛍1= 𝛍2= 𝛍3 )

guess 3 ( H3 ) : There is no interaction between the factors

In this case :

hypothesis 1 ( H1 ) : wage doesn ’ t vary based on the floor of department of education. ( 𝛍1= 𝛍2= 𝛍3 )

Hypothesis 2 ( H2 ) : wage doesn ’ triiodothyronine change based on the historic period group. ( 𝛍1= 𝛍2= 𝛍3 )

hypothesis 3 ( H3 ) : There is no interaction between level of education and age group .

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

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

Click on the Data Analysis tool in the Data tab

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

Choose the Two Factor ANOVA option to run two way ANOVA in Excel

- 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.

Set the Input, Output ranges and Rows per sample

### Step 4: Interpret the Two Factor ANOVA Results

- 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.

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

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

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

Compare the F-values against the F-critical values to interpret results of two factor ANOVA in Excel In this example, since the F ( 9.75 ) > Fcritical ( 3.55 ) for factor 1 ( level of education ) we can reject hypothesis H1 and conclude that the employees ’ wage varies based on their level of education .

similarly, since the F ( 29.13 ) > Fcritical ( 3.55 ) for divisor 2 ( long time group ), we can reject hypothesis H2 and conclude that the employees ’ wage besides varies based on their age .

however, since the F ( 0.337 ) < Fcritical ( 2.92 ) for the interaction consequence, we can accept guess H3 and conclude that there is no significant interaction between the factors ( education and age ) .

## FAQs

### What is the difference between ANOVA and T-test?

ANOVA determines whether three or more population means are importantly different from each early. On the early hand, the T-test checks only two populations and determines whether they are significantly different from one another .

### How do you interpret the results of Anova in Excel?

To interpret the results of ANOVA in Excel, compare the F-value against its corresponding F-critical respect. If it is greater, reject the null hypothesis, else, accept the nothing hypothesis .

**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 template, I have explained how to calculate ANOVA in Excel in a bit-by-bit manner. I have included detail illustrations and examples to help you understand the concept better.

Keep control this blog for more data 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 center.

Read more : How to Lower Your Electric Bill – NerdWallet

Click here to access in-depth Excel aim courses and chief in-demand advanced Excel skills .

Simon Sez IT has been teaching critical IT software for over ten years. For a humble, monthly tip you can get access to 100+ IT train courses by season professionals .

##### Adam Lacey

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