Excel ANOVA function – Analysis of Variance

In this simple guide, we will run through how to perform the built in Excel ANOVA function to perform an Analysis of Variance with some examples.

This guide will cover:

  • What is Analysis of Variance (ANOVA)?
  • How to enable the Analysis Toolpak in Excel
  • One Way ANOVA in Excel with examples

What is Analysis of Variance (ANOVA)?

Put simply – an Analysis of Variance is a common statistical technique that is used to compare separate groups of data.

When comparing just two sets of data we also have the option of performing a t-test, however once we move on to three and above this is where the ANOVA becomes the best option to work with.

In this guide, we are going to focus on the One-Way ANOVA functions that can be performed in Excel. There is also the Two-Way ANOVA which includes a wider range of variables that need to be factored in to our analysis, however that will be covered separately in it’s own post.

One-Way ANOVA

A One-Way ANOVA takes our series of 3 or more data sets, and only factors in a single variable to differentiate them. Because of this the data sets can be relatively simple. It could range from anything to age groups, income levels, locations or more where we have a set of values associated with them.

How to enable the Analysis Toolpak in Excel

Before we get started on performing the Excel ANOVA function, we will need to make sure that the Analysis Toolpak is activated.

The Data Analysis button sits on the very right of the top ribbon in Excel under the Data sub heading, and looks like the below:

If you do not have this button available, we will need to activate the Data Tookpak.

To do so, enter the Options menu under the File header found below:

Next, under the Add-Ins section, select Go under the Manage Excel Add-ins button at the bottom:

Finally, select the check box for Analysis Toolpak and press OK:

Excel Data Tookpak

Now that we have the right tools activated, let’s run through how to perform an ANOVA.

How to perform a One-Way ANOVA in Excel

For this example, let’s look at a range of salaries across three age groups – people in their 20s, 30s and 40s, to see if there is a significant difference in the averages across them.

Our data set is below:

Excel ANOVA Dataset

The steps to perform the ANOVA itself are quite simple thanks to the built in function.

First, select the Data Analysis button highlighted above under the Data sub heading.

In the box that appears, select Anova: Single Factor, and press OK

Excel Data Analysis Anova Single Factor

In the following menu, select the range of data, in this case we have the cells from B3 across and down to D12.

Our data is a series of columns, so select that under grouped by.

If your columns have headings like ours does below, make sure to tick Labels in First Row, otherwise leave blank.

Finally, alpha determines what we would like our significance level to be. 0.05 is generally considered the standard so let’s leave it as is.

Leave the output options as is and press OK.

Excel Anova Single Factor

Our output analysis will appear on a new worksheet and look like the below:

Excel Anova Single Factor Results

There are two measures we can look at here, both of which come to the same conclusion:

  • The F value is higher than the F crit value
  • The p-value is lower than 0.05 at only 0.01

Within the ANOVA function we are measuring the validity of a null hypothesis – that there is no difference between the groups. What this means, is that at least one of the values in the average column (means) varies from the others, and therefore the average salaries across the three age groups are not the same.

This sums up our guide on how to use the Excel ANOVA function. There are a wide range of functions available within the wider Data Analysis toolpak, which we will cover off in future posts.

For more tips and tutorials on using Excels built in functions be sure to check out our Excel Tips page

Similar Posts