Use two-way ANOVA to assess differences between the group means that are based on two categorical factors. In this post, we’ll work through two-way ANOVA using Excel. Even if Excel isn’t your main statistical package, this post is an excellent introduction to two-way ANOVA. Excel refers to this analysis as two factor ANOVA.

Two-way ANOVA is a hypothesis test that allows you to compare group means. Like all hypothesis tests, two-way ANOVA uses sample data to infer properties of an entire population.

In this post, I provide step-by-step instructions for using Excel to perform two factor ANOVA and then interpret the results. Importantly, I also include links to many additional resources I’ve written that you won’t find in Excel’s documentation.

Before proceeding, ensure that Excel’s Data Analysis ToolPak is installed for Excel. Click Data in Excel’s menu along the top and look for Data Analysis in the Analyze section.

If you don’t see Data Analysis, you need to install it. My post about using Excel to perform t-tests shows how to install the Data Analysis ToolPak. It’s free!

**Related post**: Hypothesis Testing Overview

## Two-Way ANOVA in Excel

To perform this analysis, you’ll need two categorical variables, which analysts also refer to as factors. These factors are your independent variables. The number of factors in your analysis determines the name of the ANOVA analysis. One-way ANOVA uses one factor. Two-way ANOVA has two. And, so on. Each factor has a finite number of possible values, which are known as levels. For example, gender is a categorical factor that has the two levels of male and female.

You also need one continuous outcome variable, which is the dependent variable. The various combinations of values for the two categorical variables divide the continuous data into groups. Two-way ANOVA determines whether the mean differences between these groups are statistically significant.

Additionally, two-way ANOVA determines whether the interaction effect between the two factors is statistically significant. When significant interaction effects are present, it’s crucial to interpret them correctly. Because they can be tricky to understand, I’ll spend a little extra time covering interaction effects.

I’ll run through two examples to explain the differences between cases where the interaction effect is and is not significant. Download the CSV dataset for both examples: Two-Way ANOVA. These data are fictional.

## Choose Two Factor ANOVA in Excel

To perform two-way ANOVA in Excel, choose one of the two factor ANOVA options, as shown below. You can choose analyses for designs that either have replications or do not have replications.

Replication in this context refers to collecting multiple observations for each group. For instance, in the first analysis example in this post, we’ll look at gender and college major to determine whether these factors correspond to statistically significant income differences. If we collect only one observation per group (e.g., one female who majors in statistics), we’d use the “without replication” analysis. However, that produces a tiny sample size. Therefore, we’ll use the “with replication” option so we can have multiple observations per group. For both of the examples, we’ll have 20 observations per group.

Excel can only perform balanced two-way ANOVA. In balanced designs, all groups have an equal number of observations.

**Related post**: How to do One-Way ANOVA in Excel

## Example Two-Way ANOVA without a Significant Interaction

Imagine that we are assessing annual salaries, which is our continuous dependent variable. Our two categorical factors are gender and college major. For this analysis, we’ll use the three majors of statistics, psychology, and political science. The combination of these two factors (2 genders X 3 majors) produces the following six groups. Each group contains 20 observations.

Male / Statistics: $77,743 | Female / Statistics: $74,074 |

Male / Psychology: $69,766 | Female / Psychology: $65,320 |

Male / Political Science: $62,015 | Female / Political Science: $55,195 |

The dollar amount indicates the average income for each group. Two-way ANOVA determines whether the observed differences between means provide strong enough evidence to conclude that the population means are different. Let’s perform the analysis!

In Excel, do the following steps:

- Click
*Data Analysis*on the Data tab. - From the Data Analysis popup, choose
*Anova: Two-Factor With Replication*. - Under
*Input*, select the ranges for all columns of data. - In
*Rows per sample*, enter*20*. This represents the number of observations per group. - Excel uses a default Alpha value of 0.05, which is usually a good value. Alpha is the significance level. Change this value only when you have a specific reason for doing so.
- Click OK.

For this example, the popup should look like this:

### Interpreting Excel’s Two-Way ANOVA Results

First, look in the P-value column in the ANOVA Source of Variation table at the bottom of the output. Because the p-values for both Gender and Major are less than our significance level, these factors are statistically significant. These are the main effects in the model. Note that these p-values are so low that Excel uses scientific notation to represent them.

On the other hand, the interaction effect is not significant because its p-value (0.151) is greater than our significance level. Because the interaction effect is not significant, we can focus on only the main effects and not consider the interaction effect.

The main effects are the portion of the relationship between an independent variable and the dependent variable that does not change based on the values of the other variables in the model. For example, gender’s main effect on average income does not change from one major to another. It’s a consistent effect across majors. Males have a higher average income, and that effect is consistent (plus or minus random error) across majors.

### Summary Table Information

The Summary table in the top portion displays mean incomes for our six groups. These means range from a low of $55,191 for female political science majors to a high of $77,743 for male statistics majors. Our sample means are different. Importantly, the significant p-values indicate that our sample data provide strong enough evidence to conclude that the population means of these six groups are not equal.

Overall, males tend to have higher average incomes across the majors. Additionally, statistics majors tend to have the largest average salary.

If we had obtained insignificant p-values for Gender and Major, the sample differences between group means might represent random sampling error rather than differences between the population means.

For more information about the other statistics in the output, click the links for F-value and F critical value, degrees of freedom (df), average (mean), variance, p-values, and significance level.

## Two-Way ANOVA Example 2 with a Significant Interaction

In the previous example, the interaction effect was not significant. Consequently, it was a simple matter to interpret the main effects. How do you understand interaction effects? To emphasize how these effects work, I’ll use an intentionally silly example to make it more intuitive.

Imagine that we’re performing a taste test, and the dependent variable is Enjoyment. Our two factors are Food and Condiment. We want to determine which condiment produces the most enjoyment. To keep things simple, we’ll include only two foods (ice cream and hot dogs) and two condiments (chocolate sauce and mustard) in our analysis.

I think of interaction effects as an “it depends” effect.

Interaction effects indicate that another variable influences the relationship between an independent and dependent variable. This type of effect makes the model more complex, but if it accurately reflects the real world, it is critical to know how to interpret it. For example, the relationship between condiments and enjoyment probably depends on the type of food.

In Excel, do the following steps:

- Click
*Data Analysis*on the Data tab. - From the Data Analysis popup, choose
*Anova: Two-Factor With Replication*. - Under
*Input*, select the ranges for all columns of data. - In
*Rows per sample*, enter*20*. This represents the number of observations per group. - Excel uses a default Alpha value of 0.05, which is usually a good value. Alpha is the significance level. Change this value only when you have a specific reason for doing so.
- Click OK.

For this example, the popup should look like this:

### Interpreting Excel’s Two-Way ANOVA Results

First, look in the P-value column in the ANOVA Source of Variation table at the bottom of the output. The p-values indicate that Food is not significant (p = 0.801) , while Condiment is statistically significant (p = 0.001). These are the main effects. The significance of Condiment suggests that a portion of the relationship between Condiment and Enjoyment does not change based on the value of the other independent variable.

However, the extremely low p-value for the interaction effect is also statistically significant. In general, interaction effects indicate that the relationship between an independent variable and a dependent variable changes based on the value of another variable.

For our ANOVA model, the significant interaction indicates that the relationship between Condiment and Enjoyment *depends* on the type of food. That makes sense when you think about it. You might like chocolate sauce quite a bit, but I bet you’d find it disgusting on hot dogs!

### Interaction Effects in Depth

If only the main effects are significant, you’d be able to state that either chocolate sauce or mustard always increased your satisfaction the most and by a consistent amount regardless of the food. But that’s not how it works with food and condiments!

When you have statistically significant interaction effects, you can’t interpret the main effects without considering the interactions. In the previous example, you can’t answer the question about which condiment is better without knowing the type of food. Sometimes mustard is better while other times chocolate sauce is better. Again, “it depends.”

It’s often easiest to interpret an interaction effect using specialized plots known as interaction plots. Unfortunately, Excel can’t create these plots. However, you can look at the means for each group to get an idea of how it works. The hot dog/mustard group (89.6) and the ice cream/chocolate sauce group (93.0) each have relatively high enjoyment scores. Conversely, hot dog/chocolate sauce group (65.3) and ice cream/mustard group (61.3) have lower scores. That makes sense intuitively. However, to explain the changing relationship between Condiment and Enjoyment statistically, you need to include the interaction effect.

To learn more about interaction effects in more detail and with graphs, see my post Understanding Interaction Effects.

## What Excel Does Not Include

While it’s fantastic that Excel allows you to perform two-way ANOVA, it doesn’t have all the features that a full-blown statistical package provides. As I mentioned, the easiest way to understand interaction effects is by using interaction plots. Unfortunately, Excel can’t create them.

Excel can analyze only balanced designs. If your groups have an unequal number of observations, you’ll need to use other software.

Additionally, significant ANOVA results only indicate that not all group means are equal. If you want to determine whether the mean differences between specific pairs of groups are statistically significant, you’ll need to use a post hoc analysis.

It’s always good practice to check the residual plots to be sure you’re not violating an assumption. Unfortunately, Excel can’t create them.

biruk says

nice

André SERVILLE says

Hi Jim!

I appreciate it.

Very nice

Dr. d p singh says

Thanks Prof Jim

Very good exposition to Two way anova