How to Make an ANOVA Table in Excel (3 Suitable Ways)

This article illustrates how to make an ANOVA Table in Excel. An ANOVA table is helpful to decide whether you can accept or reject the Null Hypothesis for a dataset. You can use the Data Analysis tool in Excel to easily create an Anova table. Follow the article to use the analysis tool with your datasets.


Download Practice Workbook

You can download the practice workbook from the download button below.


Introduction to ANOVA in Excel

ANOVA is the abbreviation of Analysis of Variance. In Excel, it is a method to obtain the values required to test the Null Hypothesis. Excel allows us to apply this method for the following three different cases.

  • ANOVA: Single Factor
  • ANOVA: Two-Factor With Replication
  • ANOVA: Two-Factor Without Replication

The Single Factor is applicable when there are multiple data samples and you need to perform the hypothesis test for differences in those samples only. The Two-Factor With Replication applies when each sample data is divided into similar groups, and you need to perform the hypothesis test for differences within those groups and within different samples. The Two-Factor Without Replication applies when you need to perform the hypothesis test difference within each sample and within different samples.


3 Ways to Make an ANOVA Table in Excel

You can make an Anova table using the Data Analysis tool in Excel. But you may need to activate the tool to be able to access it. The following steps will be sufficient for that.

  • Go to File>>Options or press ALT+F+T.
  • Select the Add-ins tab>>Manage Excel Add-ins>>Go.
  • Check the Analysis ToolPak checkbox>>Click OK.

Enable Analysis ToolPak

After that, you can access the Data Analysis tool from the Data tab as shown below.

Select Data > Data Analysis


1. Using ‘ANOVA: Single Factor’ Option

Assume you have a dataset containing the marks obtained by a group of students on two different tests as follows. Now you want to analyze the dataset to find out if there are any significant differences between the test results.

dataset for single factor Anova Table

Follow the steps below to perform a one-way or single-factor Anova on the dataset in Excel for that.

📌Steps:

  • First, select Data>>Data Analysis. Then choose Anova: Single Factor from the analysis toolbox and click OK.

choose Anova: Single Factor

  • Next, you will see the Anova: Single Factor dialog box. Now select the entire dataset including the labels (B4:C12) for Input Range. Then mark the radio button for Columns beside the Grouped By option as the data in this dataset are grouped by columns. Next, check the Labels in first row Keep the Alpha value to 0.05. After that, mark the radio button for Output Range, enter the cell reference where you want to make the Anova table, and click OK.

input data to make an Anova Table

  • Finally, you will see the following table in the specified output location.

Anova Table for single factor

Read More: How to Interpret ANOVA Single Factor Results in Excel


2. Using ‘Anova: Two-Factor With Replication’ Option

Assume you have a similar dataset where the students are divided into two different groups. Now you want to see if there is any significant difference in performance between the two groups.

dataset for two-factor with replication anova test

Follow the steps below to perform a two-way or two-factor Anova with replication on the dataset in Excel for that.

📌Steps:

  • First, select Data>>Data Analysis. Then choose Anova: Two-Factor With Replication from the analysis toolbox and click OK.

select Anova: Two-Factor With Replication

  • Next, you will see the Anova: Two-Factor With Replication dialog box. Now select the entire dataset including the labels (B4:D12) for Input Range. Then enter 4 in the Rows per sample field as each group has 4 students. Keep the Alpha value to 05. After that, mark the radio button for Output Range, enter the cell reference where you want to make the Anova table, and click OK.

input data to make the anova table

  • Finally, you will see the following table in the specified output location.

 2-factor Anova Table

Read More: Two Way ANOVA in Excel with Unequal Sample Size (2 Examples)


3. Using ‘Anova: Two-Factor Without Replication’ Option

Assume you have a similar dataset as follows and you want to find out if there are significant differences among individual performances along with the test-wise performances.

dataset for 2-factor Anova

Follow the steps below to perform a two-way or two-factor Anova without replication on the dataset in Excel for that.

📌Steps:

  • First, select Data>>Data Analysis. Then choose Anova: Two-Factor Without Replication from the analysis toolbox and click OK.

select Anova: Two-Factor Without Replication

  • Next, you will see the Anova: Two-Factor Without Replication dialog box. Now select the entire dataset including the labels (B4:D12) for Input Range. Then check the Labels Keep the Alpha value to 0.05. After that, mark the radio button for Output Range, enter the cell reference where you want to make the Anova table, and click OK.

input data for Anova

  • Finally, you will see the following table in the specified output location.

create anova table


How to Interpret an ANOVA Table in Excel

We perform variance analysis to test if the Null Hypothesis is true for a dataset or not. The Null Hypothesis suggests that two sets of data are the same and the difference between them is insignificant or purely by chance. We can decide whether this hypothesis is true or not for a dataset using the Anova table obtained by performing Anova on that dataset.

The following three properties in the Anova table will help us to do that.

  • F-value
  • F crit-value
  • P-value

First, we need to check if the F-value is higher or lower than the F critical value. F > F crit rejects the Null Hypothesis and indicates that the two possibilities are not the same. Therefore there is a difference between the two sets of data. P-value<Alpha (0.05) also rejects the Null Hypothesis indicating that the difference between the two sets of data is statistically significant.


1. Read Single Factor Analysis Results

Observe the Anova table obtained from the Single Factor Anova test.

single factor anova table

  • F(4.793823)>F crit(4.60011) suggests that there is a difference between individual performances. P-value<0.05 suggest that the difference between individual performances is statistically significant.

Read More: How to Calculate P Value in Excel ANOVA (3 Suitable Examples)


2. Read Two-Factor With Replication Analysis Results

Observe the Anova table obtained from the Two-Factor With Replication Anova test.

two-factor anova table

  • F(2.321302)<F crit(4.747225) for Sample accepts the Null Hypothesis and indicates that group performances are the same. P-value>>0.05 also accepts the Null Hypothesis and indicates that the difference in group performances is statistically insignificant.
  • F(1.957783)<F crit(4.747225) for Columns accepts the Null Hypothesis and indicates that group performances between the two tests (Math & Science) are the same. P-value<<0.05 suggest that the difference in group performances between the two tests is statistically insignificant.

Read More: How to Interpret Two-Way ANOVA Results in Excel


3. Interpret Two-Factor Without Replication Analysis Output

Observe the Anova table obtained from the Two-Factor With Replication Anova test.

2-factor without replication anova table

  • F(7.895361)>F crit(3.787044) for Rows suggests that is a difference between individual performances. P-value<<0.05 suggest that the difference between individual performances is statistically significant.
  • F(19.64078)>>F crit(5.591448) for Columns suggests that there is a huge difference in performances between the two tests (Math & Science). P-value<<0.05 suggest that the difference in performance between the two tests is statistically significant.

Read More:  How to Interpret ANOVA Results in Excel (3 Ways)


Things to Remember

  • You must enable the Analysis ToolPak add-in to access the Data Analysis tool.
  • Don’t forget to organize the dataset as required before performing the analysis.

Conclusion

Now you know how to make an Anova table in excel for three types of datasets and also how to read or interpret them. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo