Use one-way ANOVA to test 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 a one-way ANOVA test 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!
Related post: Use the F-Test to Assess Variances in Excel
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.
While the means can differ between your groups, their variability should be roughly equal to obtain valid results. Learn more about heterogeneity.
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.
To learn more about ANOVA tests, including the more complex forms, read my ANOVA Overview and One-Way ANOVA overview.
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 8.837952 for supplier 4 to a high of 11.20252 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!
If you need to control for a covariate, you’ll need to perform an Analysis of Covariance (ANCOVA) instead. Unfortunately, that’s not an option in Excel.
The name one-way ANOVA is based on the fact that this analysis requires one categorical factor. In my next post, I cover two-factor, or two-way ANOVA, which is the other type of ANOVA that Excel includes.
The Kruskal Wallis test is the nonparametric equivalent of one-way ANOVA.
Share this:
Reader Interactions
Comments
Comments and Questions
Hi Jim
Would you please help me with the type of stats if I am studying the effect of two sampling methods on the color of particles (how many blue particles, how many white particles, how many red particles etc collected in each sampling method and how does the count differ between the two methods)? Color is the dependant variable and includes 8 type of colors.
Many thanks
Hello,
When performing a nova one way in excel, it is giving me the wrong degrees of freedom (almost double the actual df ) why would this happen and how do I fix it? Thank you!
Hi, Jim,
I have been using single factor ANOVA testing via Microsoft Excel to validate the statistical data that measures whether or not participants in a user study learned CS concepts from a virtual reality application that I built. Specifically, I am applying the ANOVA on the average post-test scores achieved by three distinct groups of people (two experimental, one control) to determine if there is any difference the mean scores. I have two questions about this analysis:
(1) What pieces of information in the summary data that is generated once ANOVA has been performed should I use to accept or reject the alternative hypothesis?
(2) Could I use the t-Test to accept or reject the alternative hypothesis?
Many Thanks,
Jim
Hi Jim, nice name!
As I mention in this post, the key output is the p-value, which you use to determine whether your results are significant. If the p-value is less than your significance level, you reject the null hypothesis. Look in the interpretation section to see what that means. It tells you that not all the group means are equal.
You can also look at the table with the group means That’ll give you an indication of where each group ends up. Significant results indicate that not all those group means are equal at the population level.
Determining which differences between specific pairs of groups are statistically significant is another matter. To make that determination, you need to use post hoc tests. Unfortunately, Excel doesn’t provide post hoc tests. I mention that in the last section where I also provide a link to my post about those tests. I’d read that post so you know what the normal next procedure involves and why.
Because Excel doesn’t provide post hoc tests, you can perform a simple version of it using the Bonferroni correction and a series of 2-sample t-tests.
For the Bonferroni correction, take your significance level and divide it by the number of comparisons. If you compare all possible pairs of groups for three groups, that’s three comparisons. So, 0.05 / 3 = 0.0167. That value is your Bonferroni corrected significance level.
Next, you’ll need to perform 2-sample t-tests between those pairs of groups. However, instead of using a significance level of 0.05, you’d use the significance level of 0.0167. The differences between specific groups with p-values less than 0.0167 are statistically significant in this context.
My post about post hoc tests explains why you need to make that correction.
Bonferroni is not the ideal post hoc test because it is conservative (meaning it errs on the side of failing to reject the null more frequently than needed). But, using Excel, that’s your best bet.
I hope that helps!
Hi Jim,
I am trying to work on an ANOVA two-way factor with replication in Excel. I am having some issues. I keep getting the error “ANOVA: Two-factor with replication – each sample must contain the same number of rows.”. I have four samples of data and each sample contains 20 rows. I highlighted all four rows and used those as the range for the ANOVA analysis. I’m not sure what I’m doing wrong.
That’s brilliant advice Jim, it’s a pity that Microsoft et al. don’t provide advice on what their products won’t do!
Hi Jim, I have some big data sets with some missing values for 2-way ANOVA. Excel returns the message “input range contains non-numeric data”. Have tried #N/A to no avail. Trawled the net looking for a solution but……Help!!
Hi John,
When you have missing values, you need to delete the entire row in your spreadsheet. Each row represents a single observation. When a row has a single missing value, the entire row is unusable. There are methods to estimate the missing value (imputation), but I won’t cover those here. If you have large datasets, deleting some rows shouldn’t be a problem.
Additionally, Excel presents another complication. It can only perform balanced ANOVA, which means that the groups sizes must be equal. Consequently, if you delete observations, the groups might have unequal numbers of observations, assuming you started with an equal number in each. You’d need to delete some good observations in the other groups down the point where they all have the same sample size as the smallest groups.
Deleting good data like that is worrisome! You might want to consider using a statistical package that can perform unbalance (unequal group sizes) ANOVA. Most statistical packages can do that without problem. I’m not sure why Excel can’t!
But you could randomly select observations from some groups to delete until you have groups of equal sizes and then perform the analysis in Excel. If you have large datasets, it might not be a problem.
I hope that helps!
hello Sir,
There is any way to do one way anova without mean. I have number of days taken by plants to reach anthesis and I have 4 varieties . So I have only one replication for each variety. Is any way to compared such type of data
Hi Sandeep,
ANOVA only works with group means. What type of data do you have? What summary measure do you use for the four varieties?
Thanks for that timely and clear explanation. have been looking for this and glad to find it here. Thank you
Hi Jim, I have no clue with statistics and would greatly appreciate some advice. I am performing a quantitative systematic review for my thesis, comparing clinical outcomes between 5 different surgical procedures. My null hypothesis states that each surgical procedure provide equal results. For each of the five surgical procedures I have collected data from multiple studies and produced a weighted mean score. To compare the weighted means of the 5 procedures should I perform the one-way ANOVA or the two-factor ANOVA, or any other statistical tests that you would recommend?
Many thanks in advance,
Rob
I need to write an APA style results section of a study. I have no idea how to even begin. The study is a one-way ANOVA. What needs to be included?
Thanks for the help, but I’m really stuck and I need to get this done by today. I’m looking at mould growth rate in three different types of bread in three different conditions (in a plastic bag, paper bag and exposed to the air) over the course of a week but I’m so confused as to which variable is categorical and all that and I honestly don’t know where all these numbers are supposed to come from. The way I’m supposed to analyse the rate of growth is looking at the surface area of the bread that was colonised by mould at the end of the week
Hi Aioli,
The categorical variable is what divides your data into groups. In your case, it something like “Bag Type.”
You also need some outcome measure. Some measurement of mold growth. I don’t what measurement is used for mold growth, but I’m sure in the field they have devised such a measurement. That’s probably a continuous variable.
One-way ANOVA will tell you if the group means for mold growth were significantly different by bag type.
I hope that helps!
I am using MS Office 2010. Where is ANOVA option in it?
Hi Sohail,
In Excel 2010, if you haven’t already, you’ll need to activate the analysis Toolpak by going to File / Options / Add-Ins. Then check Analysis Toolpak.
You should see Data in the menu bar across the top (not in the Ribbon). Choose Data in the menu, and then you should see Data Analysis in the far-right side of the ribbon. When you click Data Analysis, a box appears with the list of statistical analyses.
I hope that helps!
Hello, thank you for the tutorial. I have done a simple experiment to introduce exercise behaviour (n = 1) over a 5-week period. The first week was baseline, zero minutes exercising. I then want to compare the following 4 weeks of treatment to the baseline week. Can I use ANOVA to compare baseline, week 1, week 2, week 3 and week 4? Or should I do 4 separate paired samples t-test? Or can I simply do a paired sample t-test comparing the baseline week with the four-week treatment average? Thank you.
can anova be used for quality of life measures using single group
Hi Muneeba,
Use ANOVA when you have three or more groups to compare. With a single group, you’d need to use something like a 1-sample t-test.
If there a way to change the null hypothesis in excel?
Hello Sir
I have done B. Sc in Statistics in July 2019.
I want to make my career in Data Analytics field.
Sir, but my mathematics is not so strong and I have average knowledge of Statistics.
Can I go for this career?
Please tell me Sir.
Waiting for your response
With regards
Dilshad Hashmi
Thanks Prof Jim for nice introduction about One way anova in excel.