ANOVA is one of the frequently used inferential statistical method to check the effect of an experiment or test. Like other statistical tools, excel also have the capability to perform ANOVA test. In this article, we’ll discuss that how to use ANOVA with excel. Moreover, 4 steps for one way and two way ANOVA with excel will be explained with screenshots. Let’s start.
One way and two-way ANOVA both can be performed by using Data Analysis tool in excel which is placed in Data tab. If data analysis tab is not found in the data tab, follow the below five steps to add it in your data tab.
Table of Contents
How to add Data Analysis option in data tab
Click on the “File” in left side of excel.
2. Select “options” and then “Add-ins”
3. Next step is to select “Excel Add-ins” from the drop down and click on Go.
4. One pop up window will open where “Analysis ToolPak” needs to be selected.
5. You are done. Now check the Data tab in excel, “Data Analysis option is visible.
ANOVA with excel
When number of variable is one in a data set which needs to be analyzed, use ANOVA Single Factor option. If it number of variables are more than one, use two way or two factor anova.
For the example, we have sales data for three months, Month 1, Month 2 & Month 3. Now ANOVA analysis to be done to check the variance or analysis variance among three months. Though only month sales factor is used as one variable, single factor option should be used. To perform one way ANOVA, below data set is used.
4 Steps for Single Factor ANOVA with excel
1. Go to Data tab and select “Data Analysis”
2. Select “Anova: Single Factor” from Pop-up window of “Data Analysis”.
3. Another pop-up window of Anova: Single Factor will open (shown below). In the input range, select the data set, here it is B2:D20. If you have column names, check the box of “Labels in the first row. By default, alpha is set at 0.05 which is basically p-value. Next step is to select the output range where the output table will be pasted in excel. It may be same sheet or new sheet in excel. Now click ok.
4. Similar table will be generated by excel as ANOVA output table which shown below. Let’s understand the output or how to read it.
There are two parts in ANOVA output, Summary table and ANOVA table.
Summary table:
Groups– It defines the names of the group exist in the input data
Count– It shows the count of the observations in each group. For all the months it is showing that 18 observations are present in the data.
Sum– Sum column shows the sum of values in each group
Average– Average column shows the average of values from each group. Month 1 average is 68, month 2 average is 33 and month 3 is 74.
Variance– It shows the variance of values from each group. Variance of month 1, 2 and 3 are 195, 120 and 252.
ANOVA table:
Source of Variation– Shows variation between the groups and within the groups for the input data. Two different rows are being showed in the table for two types of summary.
SS– Sum of squares for individual source
Df– It shows degree of freedom for between the groups and within the groups. As per the output as df,
MS– It shows the mean sum of squares. To calculate mean sum square, formula needs to use as SS/df
F– It is calculated as MS (between)/ F-value (within)
P-value– It is most important output parameter for ANOVA to check weather null hypothesis is rejected or alternate hypothesis is accepted. If p value is <0.05, null hypothesis is rejected, and it is statistically significant. In other words, if p value is <0.05, that means of the groups are not same.
F crit– F critical value is important to check the significance of the test
In the output, if F value is greater than F critical value, null hypothesis can be rejected. Only F value or F critical value comparison is not the right measurement to reject null hypothesis. P value also plays a critical role to do the same.
4 Steps for Two Factor with Replication ANOVAwith excel
As discussed earlier, one way or two-way ANOVA is based on the number of variables. If the variables are more than one, two factor ANOVA should be used. Steps mentioned below to perform two-way ANOVA. Two Way ANOVA also has two different types, with replication and without replication. If sample size is different, without replication option to be used else
In the data set, we have two age groups where sales for all the three months are mentioned. Follow the below steps to perform two factor with replication Anova in excel.
1. First step is to go to the data table and click on Data Analysis option. A pop-up window will be opened.
2. From the list, you need to select two factor with replication option and click on ok.
3. Select the data range and number of samples for each group. Keep alpha at 0.05 and also select the output cell where output will be pasted.
4. Click on ok to create the output table. Similar table will appear in the output position as shown below.
We have already explained before that how to read the Anova output tables in terms of each output parameter. Only difference here is, two summary tables have been created as we had two age groups. Moreover, one total summary table will give you the overall summary for the entire data set. Unlike summary table, there is only one Anova summary table to interpret the test result. The meaning and significance of SS. Df, MS, F, p value and F critical value already discussed in the one-way Anova. Only interpretation or statistical significance will be discussed for two-way Anova.
P value for both samples and interaction are more than the p value significance benchmark (0.05). On the other hand, p value of columns is <0.05 and statistically significant. F value and F critical values are also communicating the same significance as F > F crit.
Here sample represents different age group and column represents month sales. So, there is no significance of age group on the sales. Only specific month has its impact on sales. Also, interaction is not significant (p >0.05) or no interaction, between two variables.
All the best to create your first ANOVA report in excel.
