How to use Anova Analysis in Excel

Anova means Analysis of Variance. In this Excel tutorial you will teach yourself how to perform Anova.

ANOVA is used to test for the significance of the difference between the means of two or more samples.

In order to be able to perform the analysis of variance, 3 conditions must be met:

  1. independent samples;
  2. from a normal population;
  3. homogeneity of variance.

Anova Single Factor test

Data preparation

Let's start from preparing data table. Here is an example:

Anova data table

Data analysis

Next go to the ribbon to the Data tab. Click Data Analysis button.

Data Analysis ribbon button

If you don't have this button it means that you have to install Analysis ToolPak add-in.

Anova calculations

New dialog box appears. Click Anova: Single Factor option.

Anova Single Factor

Select Input Range which is your data table (only numbers). In my sheet it is $B$2:$F$6

Anova Single Factor input

Anova test appears in the new worksheet.

Anova test ready

Anova test could be useful for statistics of some sets of data. Excel does it really well.

Anova two factor without replication test

Let's learn how to perform Anova two factor without replication test.

Data preparation

Let's start from preparing data table. Here is an example:

Excel Anova Two Factor With Replication data table

Data analysis

Next go to the ribbon to the Data tab. Click Data Analysis button.

Data Analysis ribbon button

New dialog box appears. Click Anova: Two-Factor Without Replication option.

Excel Anova Two Factor Without Replication data analysis

Select Input Range which is your data table (only numbers). In my sheet it is $A$1:$F$5. My data table do have labels. As Alpha I left default 0.05.

Excel Anova Two Factor With Replication input range

Anova test appears in the Output Range I defined.

Excel Anova Two Factor Without Replication

 

Anova two factor with replication in Excel

Let's learn how to perform Anova two factor with replication test.

Data preparation

Let's start from preparing data table. Here is an example:

Excel Anova Two Factor With Replication data table

Data analysis

Next go to the ribbon to the Data tab. Click Data Analysis button.

Anova Data Analysis ribbon button

New dialog box appears. Click Anova: Two-Factor With Replication option.

Excel Anova Two Factor With Replication data analysis

Select Input Range which is your data table (only numbers). In my sheet it is $A$1:$F$5. In each example (group of students) I have 3 samples. As Alpha I left default 0.05.

Excel Anova Two Factor With Replication input range

Anova test appears in the Output Range I defined.

Excel Anova Two Factor With Replication

Anova two factor with replication test could be useful for statistics of some sets of data. Excel does it really well.

Further reading: 
Anova two factor with replication
Anova two factor without replication
How to create KPI dashboard?
Interactive Charts
Power Query
How to run f test in Excel