In the era of Data Analysis, you may need to do variance analysis in Excel. You can use One Way ANOVA or Two Way ANOVA to do variance analysis in Excel. I am showing the procedure in this article with clear steps and illustrations and explanations of the outcomes of ANOVA so you can easily do variance analysis in Excel and make a variance report.

## Download Practice Workbook

You can download the practice workbook from here:

## What Is ANOVA (Analysis of Variance) in Statistics?

Analysis of Variance (ANOVA) is a statistical method that divides the observed possible **factors** within a dataset into two sections:

**Systematic Factors**: They have a statistical impact on the supplied data set.**Random factors**: They have no statistical impact on the supplied data set.

**Data Analysts** employ the **ANOVA **test to examine the impact of **independent factors** on the dependent variable. So, they can decide which factors to take for further analysis and which to reject.

**The Formula for ANOVA Is:**

**F= MSE / MST**

where:

**F**=ANOVA coefficient

**MST**=Mean sum of squares due to treatment

**MSE**=Mean sum of squares due to error

## The Outcome of Analysis of Variance ANOVA

ANOVA is the first stage in determining which **factors **influence a particular data set. After the test is completed, an analyst does extra testing on the **methodological factors **that measurably impact the inconsistency of the data set. And he uses the ANOVA test findings in an** f-test** to create extra data that is relevant to the proposed **regression analysis**. The ANOVA test compares more than two data sets at the same time to see whether there is a link between them.

## One-Way ANOVA vs. Two-Way ANOVA

ANOVA is of two types: One-way or unidirectional and Two-way. The way relates to the number of independent variables in a variance analysis test.

- In
**Two-Way ANOVA**, there are**multiple dependent**variables and in**O****ne-Way**, there will be**one dependent**variable. **One-Way ANOVA**calculates the effect of**one single factor on a single variable**. And it checks whether all the sample data sets are of same or not.**One-way ANOVA**determines the**significant differences**between the average means of multiple independent variables.**Two-way ANOVA**determines the**effect**of multiple independent factors on a single dependent factor. So, you can decide which factors you can choose for further analysis.

**For Example: **

- An educational institution evaluates the
**student performance**compared with the quality of teachers then it will use**One-way ANOVA**. - If you calculate
**satisfaction level**s among certain types of professions and want to see the variance then you will use**One-way ANOVA**. - If you want to know the effect of factors on the satisfaction level of a profession, you will use
**Two-Way ANOVA**. Here satisfaction level is the**dependent variable**and the factors like work environment, work pressure, employment type, etc will be the**independent**factors here.

## Steps to Do Variance Analysis in Excel with One Way ANOVA

To do variance analysis, you should have a data set first. Here, suppose you have a dataset that contains data on the satisfaction level of people in various professions. Here is a list is shown of 9 people from each profession.

### Step 1: Go to Data Analysis Option

- At first, go to the
**Data**tab in the top ribbon, - Select the
**Data Analysis**tool

**Note:**If you don’t find the Data Analysis tool, then go to the last section of the article. Here you will find how to enable the

**Data Analysis Toolpak.**

### Step 2: Select ‘Anova: Single Factor’

- Now, the
**Data Analysis**window will appear. - Here, select the
**Anova: Single Factor**option. - Then press
**Ok.**

### Final Step: Set Input Range and Press OK

- Now, the
**Anova: Single Factor**window will open. - Here, select the cells in the Input Range box.
**Mark**the box named “**Labels in First Row”**- And press
**OK**.

- As a result, a new worksheet will create and the
**ANOVA**outcomes will be here.

**One-Way ANOVA Results Explanation:**

- In the Summary table, you will find the
**average**and the**variances**of each profession. Here you can see the**average satisfaction**level is**6.11**for**Doctors**but the**variance**is**12.11**which is very high than others. That means, in the Doctors profession, some are highly satisfied and some are very less. - Here,
**ANOVA**results are not that significant as you are calculating only the variances. - Here,
**P-values**interpret the relation between the columns and the values are greater than**0.05**so this is**not****statistically significant**. And there shouldn’t relation between the columns also.

**Read More: ****How to Calculate Variance in Excel (Easy Guide)**

**Similar Readings**

**How to Calculate Coefficient of Variance in Excel (3 Methods)****Calculate Mean Variance and Standard Deviation in Excel****How to Calculate Sample Variance in Excel (2 Effective Approaches)****Calculate Variance Percentage in Excel (3 Easy Methods)**

## Steps to Do Two-Way Variance Analysis in Excel

Suppose you have some data on student attendance in a school. There are two shifts in that school. You have found some clue that there is some effect of the day temperature on the presence of students but it also varies in shifts. You want to Data Analysis of the ready data to find a relation between the attendance and shift and temperature. I am showing the steps below.

### Step 1: Go to Data Analysis Tool

- First, go to the
**Data**tab. - Then, select the
**Data Analysis**option.

### Step 2: Select Anova (Two-Factor with/without Replication)

- Now, in the
**Data Analysis**window, select the “**Anova: Two-Factor With Replication**” option. - And press
**OK.**

### Last Step: Set Input Range to Get Result

- Now, a new
**window**will appear. - Here, select the
**data range**in the**Input Range**box. - Then input 4 in the
**Rows per sample**box as you have 4 rows per shift. - Finally, press
**OK.**

- As a result, you will see a new worksheet created.
- And,
**two way****ANOVA**result is shown in this worksheet.

**Two Way ANOVA Results Explanation:**

Here, the first tables, there are showing the summary of shifts. In brief:

- The average presence in
**Morning**shift while**temp < 30C**is**65.5**but in the**Day**shift is**83.75** - But when the temp is >36C, the average presence in the
**Morning**shift is**82%**but in the**Day**shift is**70%**. - Variance is very high at 91 in the
**Morning**shift when temp**<30C.** - You will get a complete overview of data in the summary

And in the **ANOVA** part, you can summarise the interactions and the individual effects. In brief:

- The
**P-value**of**shifts**is**0.6**which is**not****statistically significant**at the**alpha level**of 0.05. - The
**P-value**of**Temperatures**is**0.036**which is statistically significant so you can say that there is an effect of temperatures on the presence in the school. But the value is**close to**the alpha value of 0.05 so the effect is**less significant**. - But the
**P-value of interactions**is**6.29 e-05**which is much less than the alpha value so it is very much**significant statistically**and you can say that the combined effect of shift and temp is very high in the presence in the school.

**Read More: ****How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)**

## What to Do When Data Analysis Tool Is Not Showing?

The **Data Analysis** option will not show if the Data **Analysis Toolpak** **Add-in** is not enabled. Here I will show to enable the Data Analysis Toolpak in Excel.

- First, go to the
**Options**from the**File**tab.

- Then, go to the
**Add-ins**option. - Here, select
**Excel Add-ins**in the**Manage**drop-down menu. - And press
**Go**.

- Then, a new window will appear.
- Here,
**mark**the option “**Analysis ToolPak**” And press**Ok**.

- Now, go to the
**Analyze**menu in the**Data**tab. Here you will find the**Data Analysis**option.

## Conclusion

In this article, you have found how to do **variance analysis** in Excel. I have shown both **one-way ANOVA **and **two-way ANOVA** to do the **variance analysis** in Excel. You can download the workbook for free and practice them yourself. I hope you found this article helpful. You can visit our website** ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.