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**.

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

### 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.

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.

- 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.

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

**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.

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.

- 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.

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

**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.

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.

- 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.

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

## 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.

**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.

**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.

**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.