An analysis of variance (ANOVA) analysis is a useful comparison between multiple data sets. In short, it tests whether the mean (average) of multiple data sets are equal to each other, which is referred to as your null hypothesis.
Needless to say, it’s tricky to work these on paper. Let’s learn how to perform an ANOVA comparison in Microsoft Excel.
How to Perform an ANOVA Comparison in Excel
We’ll start with three sets of data like this. These represent test scores for students in three different class periods.
Imagine a real-world use for the data: one is a morning class, one is in the afternoon, and one is in the evening. Suppose you’re an instructor, and want to know which class time scores the highest on a given exam.
This is where ANOVA comes in. On Excel’s ribbon, go to the Data tab, then click on Data Analysis on the right side.
For this exercise, select Anova: Single Factor from the list of Analysis Tools. Click OK. Inside the Input Range box, input your data set by clicking and highlighting the appropriate cells on your worksheet.
Under Output Range, select a blank cell on your worksheet. (Hint: Excel will return a multi-cell report, so be sure you have several open cells adjacent to the one you choose). Notice your alpha level (p-value) is set to the commonly-used 0.05.
Click OK, and Excel will perform the ANOVA in a few seconds.
Scroll down to the report. You’ll see multiple columns, one labeled F, and another called F crit. This is the heart of your ANOVA.
Notice your p-value is 0.494. Since 0.494 > 0.05, you cannot reject your null hypothesis.
As you can see, this is another great example of how Microsoft Excel makes statistics a breeze. There’s no need to perform complicated analyses on paper when you can find solutions in just a few clicks.
Group Worksheets in Excel