Use one-way ANOVA to determine whether the means of at least three groups are different. Excel refers to this test as Single Factor ANOVA. This post is an excellent introduction to performing and interpreting one-way ANOVA even if Excel isn’t your primary statistical software package.
In my previous post, we looked at using Excel to perform t-tests, which compare two means at most.
One-way ANOVA is a hypothesis test that allows you to compare more group means. Like all hypothesis tests, one-way ANOVA uses sample data to make inferences about the properties of an entire population.
In this post, I provide step-by-step instructions for using Excel to perform single factor ANOVA and how to interpret the results. Importantly, I also include links to many additional resources I’ve written. This supplemental content presents clear explanations of relevant one-way ANOVA and F-test concepts that you won’t find in Excel’s documentation.
While this post focuses on using Excel to run a one-way ANOVA and interpreting the results, I’ve written a companion post that uses the same dataset to illustrate graphically how the F-test works. These are two useful posts to read together.
Before proceeding, verify that Excel’s Data Analysis ToolPak is installed with your copy of Excel. Click Data in Excel’s menu across 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!
One-Way ANOVA in Excel
One-way Analysis of Variance (ANOVA) requires one categorical factor for the independent variable and a continuous variable for the dependent variable. The values of the categorical factor divide the continuous data into groups. The test determines whether the mean differences between these groups are statistically significant. For example, if fertilizer type is your categorical variable, you can determine whether the differences between plant growth means for at least three fertilizers are statistically significant.
To perform one-way ANOVA in Excel, choose the option shown below.
Technically, you can use one-way ANOVA to compare two groups. However, if you have two groups, you’ll typically use a two-sample t-test.
The standard hypotheses for one-way ANOVA are the following:
- Null: All group means are equal.
- Alternative: Not all group means are equal.
If the p-value is less than your significance level (usually 0.05), reject the null hypothesis. Your sample data support the hypothesis that the mean of at least one population is different from the other population means. Again, hypothesis tests use sample data to draw conclusions about populations.
For more information about the null and alternative hypotheses and other hypothesis testing terms, see my Hypothesis Testing Overview.
Also, learn about the difference between descriptive statistics and inferential statistics and learn more about interpreting p-values.
Step-by-Step Instructions for Running a One Factor ANOVA in Excel
Let’s conduct a one-way ANOVA! Our example scenario is that we are comparing the strength of raw material from four suppliers. Supplier is our categorical independent variable (factor) while strength is the continuous dependent variable. We draw a random sample of 10 units of material from each supplier and measure the strength of all units. Now, we want to determine whether the mean strengths of the material from the four suppliers are different.
To perform a one-way ANOVA in Excel, arrange your data in columns, as shown below. For our example, each column represents raw material from one supplier. Download the CSV dataset for this example: OneWayANOVAExcel.
In Excel, do the following steps:
- Click Data Analysis on the Data tab.
- From the Data Analysis popup, choose Anova: Single Factor.
- Under Input, select the ranges for all columns of data.
- In Grouped By, choose Columns.
- Check the Labels checkbox if you have meaningful variables labels in row 1. This option helps make the output easier to interpret. Ensure that you include the label row in step #3.
- 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.
Here’s how the popup should look:
After Excel creates the output, I autofit the width of column A to display all of the text in it.
Interpreting the One-Way ANOVA Results
The Summary table indicates that the mean strengths range from a low of 88 for supplier 4 to a high of 112 for supplier 1. Our sample means are different. However, we need to determine whether our data support the notion that the population means are not equal. The differences we see in our samples might be the result of random sampling error.
In the ANOVA table, the p-value is 0.031054. Because this value is less than our significance level of 0.05, we reject the null hypothesis. Our sample data provide strong enough evidence to conclude that the four population means are not equal.
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.
What Excel’s One Factor ANOVA Does Not Include
Using the same dataset, Excel’s one-way ANOVA results match those from my dedicated statistical software package, as you can see in my post about the F-test. That’s great! However, Excel leaves out a particularly important feature—post hoc tests!
When using ANOVA, statistically significant results indicate that not all means are equal. However, ANOVA does not determine which means are different from the others. To make that determination, you need to perform post hoc tests, also known as multiple comparisons. In Latin, post hoc means “after this.” You conduct post hoc analyses after obtaining statistically significant ANOVA results. For more information, read my post about Using Post Hoc Tests with ANOVA.
Finally, lest we forget, ANOVA is a linear model, and we should check the residual plots for problems. That’s not possible in Excel!
The name one-way ANOVA is based on the fact that this analysis requires one categorical factor. In my next post, I’ll cover two-factor, or two-way ANOVA, which is the other type of ANOVA that Excel includes.